It’s that time of the month again, no not time, T-SQL time! If you have not heard of T-SQL Tuesday before it was set up by Adam Machanic (Blog|Twitter) and you can read all about it here: T-SQL Tuesday. This months host is Allen White who asked the question “What T-SQL tricks do you use today to make your job easier?” I’m guessing that people will be going mad with DMV’s so I’m going to go down a slightly different route. I must admit I haven’t used it recently due to changing jobs which has meant a change of duties, but it’s just too good not to share.

At some point in your career you’ll be asked by somebody “so just how big is the database?” At this point you have a couple of choices, you can look at the properties of the database or you could even use the system stored procedure sp_spaceused. The problem I found with this sproc was it returned data in two result sets making storing the data more complicated than it actually needs to be, as you can see from the image below.

I decided to find out if there was an easier way to collate the data for all databases on an instance and indeed there was. The following script uses the internals of sp_spaceused which I have tweaked to return a single result set and is called from within the most documented of undocumented commands sp_msforeachdb the results are written to a temporary table. A good way to use this script is to schedule it within a job to write to a permanent table that can then be used to monitor database growth.

/*
Script written by Richard Douglas
HTTP://SQL.RichardDouglas.co.uk
Script will insert data about the sizes of all databases on the instance into a predetermined table.
This can be used as a snapshot or made into a job to chart the growth of databases over time.
Please note that the DATA_COMPRESSION functionality was introduced in SQL 2008.
If you're running on SQL Server 2005 comment out the use of DATA_COMPRESSION WHEN creating storage table. */ USE TempDB GO /* Create a table to store results. */ IF NOT EXISTS (SELECT 1 FROM sys.Objects WHERE Name like '#DatabaseGrowth%' AND OBJECT_SCHEMA_NAME(object_id) = 'dbo' ) BEGIN CREATE TABLE [dbo].[#DatabaseGrowth] ( [Database_Name] [char](128) NOT NULL ,[Database_Size_MB] DECIMAL(15,2) NOT NULL ,[Unallocated_Space_MB] DECIMAL(15,2) NOT NULL ,[Reserved_MB] DECIMAL(15,2) NOT NULL ,[Data_MB] BIGINT NOT NULL ,[Index_Size_MB] BIGINT NULL ,[Unused_MB] BIGINT NOT NULL ,[DateTimeStamp] DATETIME NOT NULL DEFAULT GETDATE() ) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE) END TRUNCATE TABLE [dbo].[#DatabaseGrowth] /* Insert the data into storage table */ INSERT INTO [dbo].[#DatabaseGrowth] ([Database_Name] ,[Database_Size_MB] ,[Unallocated_Space_MB] ,[Reserved_MB] ,[Data_MB] ,[Index_Size_MB] ,[Unused_MB]) EXEC sp_MSforeachdb 'DECLARE
    @pages    BIGINT            -- Working variable for size calc.
    ,@dbname SYSNAME
    ,@dbsize BIGINT
    ,@logsize BIGINT
    ,@reservedpages  BIGINT
    ,@usedpages  BIGINT
    ,@rowCount BIGINT
SET NOCOUNT ON
    SELECT
         @dbsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN Size ELSE 0 END))
        ,@logsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN Size ELSE 0 END))
        FROM [?].dbo.sysfiles
    SELECT
         @reservedpages = SUM(a.total_pages)
        ,@usedpages = SUM(a.used_pages)
        ,@pages = SUM(
                CASE
                    /* XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" */                    WHEN it.internal_type IN (202,204,211,212,213,214,215,216) THEN 0
                    WHEN a.type <> 1 THEN a.used_pages
                    WHEN p.index_id < 2 THEN a.data_pages
                    ELSE 0
                END
            )
    FROM [?].sys.partitions p
    JOIN [?].sys.allocation_units a ON p.partition_id = a.container_id
    LEFT JOIN [?].sys.internal_tables it on p.object_id = it.object_id
    /* unallocated space could not be negative */    SELECT
         database_name = ''?''
        ,database_size = (@dbsize + @logsize) * 8192 / 1048576
        ,''unallocated space'' = LTRIM(STR((CASE WHEN @dbsize >= @reservedpages THEN (CONVERT (dec (15,2),@dbsize) - CONVERT (DEC (15,2),@reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2))
        ,reserved = LTRIM(STR((@reservedpages * 8192 / 1024.)/1024,15,0))
        ,data = LTRIM(STR((@pages * 8192 / 1024.)/1024,15,0))
        ,index_size = LTRIM(STR(((@usedpages - @pages) * 8192 / 1024.)/1024,15,0))
        ,unused = LTRIM(STR(((@reservedpages - @usedpages) * 8192 / 1024.)/1024,15,0))'
/*Show data*/SELECT
 Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM [dbo].[#DatabaseGrowth]

Thanks for hosting Allen.

Follow Richard here:

Related posts:

1. T-SQL Tuesday #10: Unused Indexes (Indices)
2. T-SQL Tuesday 23 – Key Lookups