我继承了一个相当大的SQL Server数据库。考虑到它包含的数据,它似乎比我预期的要占用更多的空间。
是否有一种简单的方法来确定每个表占用的磁盘空间?
我继承了一个相当大的SQL Server数据库。考虑到它包含的数据,它似乎比我预期的要占用更多的空间。
是否有一种简单的方法来确定每个表占用的磁盘空间?
当前回答
也许表格有更多的分区文件,必须显示文件顺序
SELECT
T1.Name AS TableName,
T5.Name AS SchemaName,
T3.partition_number AS PartionNumber,
T3.Rows AS RowsCount,
SUM(T4.total_pages) * 8 AS TotalSpaceKB,
SUM(T4.used_pages) * 8 AS UsedSpaceKB,
(SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.objects T1 INNER JOIN
sys.indexes T2 ON T1.object_id = T2.object_id INNER JOIN
sys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id INNER JOIN
sys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOIN
sys.schemas T5 ON T1.schema_id = T5.schema_id
WHERE
T1.type='U'
GROUP BY
T1.Name, T5.Name, T3.Rows,T3.partition_number
ORDER BY
T1.Name,T3.partition_number;
其他回答
sp_spaceused可以获取表、索引视图或整个数据库使用的磁盘空间的信息。
例如:
USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO
这将报告ContactInfo表的磁盘使用情况信息。
要同时对所有表使用此选项:
USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO
您还可以从SQL Server的右键单击“标准报告”功能中获取磁盘使用情况。要获取此报告,请从对象资源管理器中的服务器对象导航,向下移动到数据库对象,然后右键单击任何数据库。从出现的菜单中,选择“报告”,然后选择“标准报告”,再选择“磁盘分区使用情况:[DatabaseName]”。
从使用OSQL的命令提示符:
OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt
也许表格有更多的分区文件,必须显示文件顺序
SELECT
T1.Name AS TableName,
T5.Name AS SchemaName,
T3.partition_number AS PartionNumber,
T3.Rows AS RowsCount,
SUM(T4.total_pages) * 8 AS TotalSpaceKB,
SUM(T4.used_pages) * 8 AS UsedSpaceKB,
(SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.objects T1 INNER JOIN
sys.indexes T2 ON T1.object_id = T2.object_id INNER JOIN
sys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id INNER JOIN
sys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOIN
sys.schemas T5 ON T1.schema_id = T5.schema_id
WHERE
T1.type='U'
GROUP BY
T1.Name, T5.Name, T3.Rows,T3.partition_number
ORDER BY
T1.Name,T3.partition_number;
这将为您提供每个表的大小和记录计数。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Get a list of tables and their sizes on disk
ALTER PROCEDURE [dbo].[sp_Table_Sizes]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
END
如果您只关心数据库中的空浪费空间,而不关心单个表,则可以考虑以下问题:
如果数据库经历了大量的数据插入和删除,可能与ETL情况类似,这将导致数据库中有太多未使用的空间,因为文件组会自动增长,但不会自动收缩。
您可以通过使用数据库的财产页面来查看是否是这种情况。您可以收缩(右键单击数据库>任务>收缩)并收回一些空间。但是,如果根本原因仍然存在,则数据库将增长(并花费额外的时间尝试增长,直到增长到足够的速度,所以不要这样做)