如何判断 SQL Server 2005 数据库中哪些表占用的空间最多? [英] How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?

问题描述

如何判断 SQL Server 2005 数据库中哪些表占用的空间最多?

How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?

我确定有一些系统存储过程可以显示此信息.

I am sure there is some System Stored Procedure that shows this information.

我有一个从 1tb 增长到 23tb 的​​ TEST 数据库.我们目前正在数据库中进行大量客户端转换测试，这需要多次运行相同的转换存储过程.它执行 DELETE，我确信它会增加事务日志.但这让我想到要问这个问题.

I have a TEST database that grew from 1tb to 23tb. We are currently doing a lot of client conversion testing in the database, which entails running the same conversion Stored Procedure multiple times. It does DELETEs which I am sure is increasing the Transaction Log. But this got me thinking to ask this question.

最大的问题是 dbo.Download 表，它创建了实际上不需要的大量存储空间，在截断它之前我有 3GB，然后是 52MB ;)

the big problem is the dbo.Download table, it creates massive storage that is actually not needed, I had 3GB before truncating it, then 52MB ;)

推荐答案

试试这个脚本 - 它将列出数据库中所有表的行数和数据行使用的空间(以及使用的总空间):

Try this script - it will list the number of rows and the space used by data rows (and the total space used) for all tables in your database:

SELECT 
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
 OBJECT_NAME(i.object_id) 

这篇关于如何判断 SQL Server 2005 数据库中哪些表占用的空间最多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！