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

10 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
  3. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites! Packers And Movers Mumbai

    ReplyDelete
  4. Spolu offers OnePlus, Mi, Vivo, Lenovo & iPhone all types od mobile mobiles repair service at your doorstep. All our phone repairs come with 6 months warranty, same day service. We'll send you a text with a link to download the app.
    You can download the mobile app here-
    Doorstep Mobile Repair Service in Noida

    ReplyDelete
  5. Orel State University which is also known as Government State Educational Institution of Higher Education is identified as a prime higher education Institution in the metropolis of Orel. Orel State University was founded in 1931 as an industrial-Pedagogical Institute. In 1932 Orel Pedagogical University became Orel State University. Orel State University is unique in many aspects.
    To get Admission and more information-
    Orel State Medical University

    ReplyDelete
  6. Orel State University is unique in many ways. It is the only classical university Orel, a member of the Association of classical Russian universities. It is the only institution in the region, which is training on a wide range of areas and specialties of higher and postgraduate education, training, re-training (more than 300).
    To Get more information and Admission-
    Orel State Medical University

    ReplyDelete
  7. Orel State University is unique in many aspects. Orel State University is the only classical university who is the member of the Association of classical universities of the Russian Federation and the only educational institution in the region which provide training on a wide range of areas and specialties of higher and postgraduate education, training, retraining.

    Orel State Medical University

    ReplyDelete
  8. Packers And Movers Bangalore Local Household Shifting Service, Get Free Best Price Quotes Local Packers and Movers in Bangalore List, Compare Charges, Save Money And Time at
    Local Packers And Movers Bangalore

    ReplyDelete
  9. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!.@ Packers and Movers Ahmedabad

    ReplyDelete