Monday, January 6, 2014

T-SQL: Which table taking most space

This query will list all tables of the selected database, sorted by the table which is taking up most data space in MB from the database.  I used it on a database which had a huge MDF file and found out that the Audit table had grown too much.

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 
TotalSpaceMB desc
 --OBJECT_NAME(i.object_id) 


Source: http://stackoverflow.com/questions/3927231/how-can-you-tell-what-tables-are-taking-up-the-most-space-in-a-sql-server-2005-d

2 comments:

  1. A good blog. Thanks for sharing the information. It is very useful for my future. keep sharing
    facebook baixar
    baixar facebook
    baixar facebook gratis
    facebook movel baixar

    ReplyDelete

  2. الرائد من افضل شركات الخدمات المنزلية في المملكة وخدماتها تغطى كل المنطقة العربية للمزيد قم بزيارة
    شركة تنظيف خزانات بمكة شركة تنظيف خزانات بمكة
    افضل شركة تنظيف منازل بالمدينة المنورة افضل شركة تنظيف منازل بالمدينة المنورة
    افضل شركة تنظيف بمكة بالبخار افضل شركة تنظيف بمكة بالبخار
    شركة نقل عفش من جدة الى الاردن شركة نقل عفش من جدة الى الاردن
    شركة تنظيف مجالس بمكة شركة تنظيف مجالس بمكة

    ReplyDelete