Printed 2017/01/17 11:03PM

Table Space revised Again

By Jason Brimhall, 2011/11/17

Since I am in the Collation Sensitive mood, I am finally getting around to updating this script.  This is the Table Space script that I have had out there for a while.  In the last release of this script, a request (by Remi) was made to update it so it will work with CS.  In addition to that, a request was made to add a few columns.  I have done both.

The CS request was not too big of a deal – just took a minute to actually sit down and do it.  Then it was a matter of setting a test database to CS and confirming that the script continued to work.  A friend did the same legwork (thx Remi) and posted his update in a thread I had been planning on getting back to with the update.  Now it will just get a link to this, and then there can be a circular reference.

The second part of the request was for a change in calculations and possibly additional columns.  I just added columns and someday hope to get back to this script and parameterize the whole thing so that a variable set of columns can be returned – based on user input.  Oh the glory of those someday goals.

So, here is the updated Table Size script.

Code block   
DECLARE @dbsize DECIMAL(19,2)
        ,@logsize DECIMAL(19,2)
**  Summary data.
        SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
                , @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) * 8/1024
                FROM sys.database_files
        ;WITH FirstPass AS (
                SELECT OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024,
                        PageCnt = SUM(
                                WHEN (index_id < 2)
                                        THEN (used_page_count)
                                ELSE lob_used_page_count + row_overflow_used_page_count
                        )) * 8/1024,
                        RowCnt = SUM(
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                FROM sys.dm_db_partition_stats
                --Where OBJECTPROPERTY(object_id,'IsMSShipped') = 0
                GROUP BY OBJECT_ID
        ,InternalTables AS (
                SELECT ps.OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024
                FROM sys.dm_db_partition_stats  ps
                        INNER Join sys.internal_tables it
                                ON it.OBJECT_ID = ps.OBJECT_ID
                                And it.internal_type IN (202,204,211,212,213,214,215,216)
                WHERE it.parent_id = ps.OBJECT_ID
					--And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0
                GROUP BY ps.OBJECT_ID
        ,Summary AS (
                        ObjName = OBJECT_NAME (F.OBJECT_ID),
                        NumRows = MAX(F.RowCnt),
                        ReservedPageMB = SUM(IsNull(F.ReservedPage,0) + IsNull(i.ReservedPage,0)),
                        DataSizeMB = SUM(F.PageCnt),
                        IndexSizeMB = SUM(CASE WHEN (F.UsedPage + IsNull(i.UsedPage,0)) > F.PageCnt
                                                        THEN ((F.UsedPage + IsNull(i.UsedPage,0)) - F.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
                        UnusedSpace = SUM(CASE WHEN (F.ReservedPage + IsNull(i.ReservedPage,0)) > (F.UsedPage + IsNull(i.UsedPage,0))
                                THEN ((F.ReservedPage + IsNull(i.ReservedPage,0)) - (F.UsedPage + IsNull(i.UsedPage,0))) ELSE 0 END),
                        dbsizeMB = @dbsize,
                        LogSizeMB = @logsize
                FROM FirstPass F
                        LEFT Outer Join InternalTables i
                        ON i.OBJECT_ID = F.OBJECT_ID
                GROUP BY F.OBJECT_ID
        ),TotalUnused AS (
				SELECT SUM(UnusedSpace) AS UnusedSpace
					FROM Summary
        SELECT ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, S.UnusedSpace, dbsizeMB, LogSizeMB
						,dbsizeMB - TU.UnusedSpace AS TotalDataFreeSpace
                        ,PercentofDBPhysFile = ((IndexSizeMB + DataSizeMB) / @dbsize) * 100
                        ,PercentofDBUsedSpace = ((IndexSizeMB + DataSizeMB) / (@dbsize - TU.UnusedSpace)) * 100
        FROM Summary S
			CROSS APPLY TotalUnused TU
        ORDER BY PercentofDBUsedSpace DESC

If you recall, I did two versions of the table size script.  One followed the path of sp_spaceused and the other followed sp_MStablespace.  This script is the one that follows the sp_spaceused version.  I will post an update for the sp_MStablespace version shortly.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.