ITG Systems Integration Inc.

Data Architects

Tech Blog - Where has my space gone

This SQL server query (you can easily turn it into a view) gives you details about the tables/indexes in your database. One of the most (not so) surprising, thing that I come across is the size of the index file. I have used this query to keep track of the growth of a particular database over a long period of time..

SELECT   T.Name AS Name,
         P.Rows AS Rows,
         SUM(A.Total_pages
               * 8) AS Reserved,
         SUM(A.Data_pages
               * 8) AS Data,
         SUM((A.Used_pages
                - A.Data_pages)
               * 8) AS Index_size,
         SUM((A.Total_pages
                - A.Used_pages)
               * 8) AS Unused
FROM     Sys.Tables T,
         Sys.Indexes I,
         Sys.Partitions P,
         Sys.Allocation_units A
WHERE    T.Object_id = I.Object_id
         AND I.Object_id = P.Object_id
         AND I.Index_id = P.Index_id
         AND P.Partition_id = A.Container_id
         AND T.Name NOT LIKE 'dt%'
         AND T.Is_ms_shipped = 0
         AND I.Object_id > 255
GROUP BY T.Name,P.Rows