我如何确定我的SQL Server数据库的大小？

您可能需要从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表中检索文件名。