基本:我的MS SQL Server数据库磁盘上的大小是多less?
更多:我可以快速查看数据的位置吗? 即哪些表,日志等
您可能需要从sp_spaceused命令开始。
例如:
sp_spaceused 返回有关数据库总大小的信息
sp_spaceused'MyTable' 返回有关MyTable大小的信息
阅读所有可获取信息的文档。 您还可以使用sp_msforeachtable命令一次对所有表运行sp_spaceused。
编辑:请注意,该命令有时会返回多个数据集,每个数据集包含不同的统计块。
最简单的方法(不input!):在Management Studio的SQL 2005/8中,右键单击数据库,selectReports,Standard Reports,Disk Usage(也可以按顶部表格,表格和分区)。
该脚本遍历当前数据库中的所有表,并显示每个数据,索引和未使用空间占用多less空间:
http://sqlserverpedia.com/wiki/Calculate_Current_Table_Sizes
您可以在sys.database_files
看到物理文件。 这有通往文件和大小的path(在块IIRC)。
sp_spaceused
将显示单个对象占用多less空间。
运行开始\程序\ Microsoft SQL Server \企业pipe理器。 打开数据库表,在属性%databasename%你可以看到位置数据filrs&Transaction文件。
运行这个来获得每个表的大小:
/****************************************************************************** ** File: “GetTableSpaceUsage.sql” ** Name: Get Table Space Useage for a specific schema ** Auth: Robert C. Cain ** Date: 01/27/2008 ** ** Desc: Calls the sp_spaceused proc for each table in a schema and returns ** the Table Name, Number of Rows, and space used for each table. ** ** Called by: ** n/a – As needed ** ** Input Parameters: ** In the code check the value of @schemaname, if you need it for a ** schema other than dbo be sure to change it. ** ** Output Parameters: ** NA *******************************************************************************/ /*—————————————————————————*/ /* Drop the temp table if it's there from a previous run */ /*—————————————————————————*/ if object_id(N'tempdb..[#TableSizes]') is not null drop table #TableSizes ; go /*—————————————————————————*/ /* Create the temp table */ /*—————————————————————————*/ create table #TableSizes ( [Table Name] nvarchar(128) /* Name of the table */ , [Number of Rows] char(11) /* Number of rows existing in the table. */ , [Reserved Space] varchar(18) /* Reserved space for table. */ , [Data Space] varchar(18) /* Amount of space used by data in table. */ , [Index Size] varchar(18) /* Amount of space used by indexes in table. */ , [Unused Space] varchar(18) /* Amount of space reserved but not used. */ ) ; go /*—————————————————————————*/ /* Load the temp table */ /*—————————————————————————*/ declare @schemaname varchar(256) ; -- Make sure to set next line to the Schema name you want! set @schemaname = 'dbo' ; -- Create a cursor to cycle through the names of each table in the schema declare curSchemaTable cursor for select sys.schemas.name + '.' + sys.objects.name from sys.objects , sys.schemas where object_id > 100 and sys.schemas.name = @schemaname /* For a specific table uncomment next line and supply name */ --and sys.objects.name = 'specific-table-name-here' and type_desc = 'USER_TABLE' and sys.objects.schema_id = sys.schemas.schema_id ; open curSchemaTable ; declare @name varchar(256) ; /* This holds the name of the current table*/ -- Now loop thru the cursor, calling the sp_spaceused for each table fetch curSchemaTable into @name ; while ( @@FETCH_STATUS = 0 ) begin insert into #TableSizes exec sp_spaceused @objname = @name ; fetch curSchemaTable into @name ; end /* Important to both close and deallocate! */ close curSchemaTable ; deallocate curSchemaTable ; /*—————————————————————————*/ /* Feed the results back */ /*—————————————————————————*/ select [Table Name] , [Number of Rows] , [Reserved Space] , [Data Space] , [Index Size] , [Unused Space] from [#TableSizes] order by [Table Name] ; /*—————————————————————————*/ /* Remove the temp table */ /*—————————————————————————*/ drop table #TableSizes ;
取自 罗伯特·凯恩的博客
此代码适用于Microsoft SQL 2005+
这是一个查询/视图,获取所有这些信息,更多,没有任何“邪恶的”游标或循环。 😉
/* vwTableInfo - Table Information View This view display space and storage information for every table in a SQL Server 2005 database. Columns are: Schema Name Owner may be different from Schema) Columns count of the max number of columns ever used) HasClusIdx 1 if table has a clustered index, 0 otherwise RowCount IndexKB space used by the table's indexes DataKB space used by the table's data 16-March-2008, [email protected] 31-January-2009, Edited for better formatting */ --CREATE VIEW vwTableInfo -- AS SELECT SCHEMA_NAME(tbl.schema_id) as [Schema] , tbl.Name , Coalesce((Select pr.name From sys.database_principals pr Where pr.principal_id = tbl.principal_id) , SCHEMA_NAME(tbl.schema_id)) as [Owner] , tbl.max_column_id_used as [Columns] , CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx] , Coalesce( (Select sum (spart.rows) from sys.partitions spart Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount] , Coalesce( (Select Cast(v.low/1024.0 as float) * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.indexes as i JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id JOIN sys.allocation_units as a ON a.container_id = p.partition_id Where i.object_id = tbl.object_id ) , 0.0) AS [IndexKB] , Coalesce( (Select Cast(v.low/1024.0 as float) * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.indexes as i JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id JOIN sys.allocation_units as a ON a.container_id = p.partition_id Where i.object_id = tbl.object_id) , 0.0) AS [DataKB] , tbl.create_date, tbl.modify_date FROM sys.tables AS tbl INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2) INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')
请享用。
有几个如何从GUI中做到这一点的描述。
真正的DBA知道:graphics用户界面(GUI)适用于Chump。
的sp_helpdb
返回所有文件名,位置,磁盘空间和types的logging集。
您也可以从每个数据库的sysfiles表中检索文件名。