最近開始研究 Database Maintenance 的方向
很多時候都想對公司的Table了解多一些
看看那個Database Table 佔的空間比較大
如果大家想看看在Database上不同的Table佔了多小空間..
可以在選擇了Database後執行以下的SQL Query
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)
之後他便會出列出 以下的資料…其實都很有用的
IndexName = 在Table上有的 Index的名稱
RowCounts = 在這個Table上有多 行
最主要都是看 Total Space用了多小
之後的網誌會和大家分享怎樣可以 release返一些沒有使用的空間
Hope you find it useful