DBA Repository – Gather Database Size Info

In an earlier series of posts I demonstrated how to maintain a list of all the databases on all the SQL Server instances in an enterprise.  Once of the critical pieces of information about databases is their size, and more importantly, their growth over time.  In this installment of the DBA Repository project I’ll show how I’m gathering size information and so I can trend that growth over time for disk space planning.

Create the dbo.DatabaseSize Table

In order to keep historical size data I need to create a new table to hold the size information for each database on each instance for each time the data is gathered, it can’t be stored only in the dbo.Databases table so I created this dbo.DatabaseSize table:

CREATE TABLE [dbo].[DatabaseSize] (
      [DatabaseSizeId] [bigint] IDENTITY(1, 1) NOT NULL
    , [ServerName] [nvarchar](128) NOT NULL
    , [DatabaseName] [nvarchar](128) NOT NULL
    , [Polled] [datetime] NOT NULL
    , [DBSize(MB)] [decimal](15, 2) NOT NULL
    , [UnallocatedSpace(MB)] [decimal](15, 2) NOT NULL
    , [ReservedSpace(MB)] [decimal](15, 2) NOT NULL
    , [DataUsed(MB)] [decimal](15, 2) NOT NULL
    , [IndexUsed(MB)] [decimal](15, 2) NOT NULL
    , [Unused(MB)] [decimal](15, 2) NOT NULL
    , CONSTRAINT [i01_DatabaseSize] PRIMARY KEY NONCLUSTERED ([DatabaseSizeId] ASC)
    )
ALTER TABLE [dbo].[DatabaseSize] WITH CHECK
ADD CONSTRAINT [FK_DatabaseSize_Databases] FOREIGN KEY ([ServerName], [DatabaseName]) REFERENCES [dbo].[Databases] ([ServerName], [DatabaseName])
		ON UPDATE CASCADE
        ON DELETE CASCADE
ALTER TABLE [dbo].[DatabaseSize] CHECK CONSTRAINT [FK_DatabaseSize_Databases]
 
ALTER TABLE [dbo].[DatabaseSize] ADD CONSTRAINT [DF_DatabaseSize_DBSize(MB)] DEFAULT ((0)) FOR [DBSize(MB)]
ALTER TABLE [dbo].[DatabaseSize] ADD CONSTRAINT [DF_DatabaseSize_UnallocatedSpace(MB)] DEFAULT ((0)) FOR [UnallocatedSpace(MB)]
ALTER TABLE [dbo].[DatabaseSize] ADD CONSTRAINT [DF_DatabaseSize_ReservedSpace(MB)] DEFAULT ((0)) FOR [ReservedSpace(MB)]
ALTER TABLE [dbo].[DatabaseSize] ADD CONSTRAINT [DF_DatabaseSize_DataUsed(MB)] DEFAULT ((0)) FOR [DataUsed(MB)]
ALTER TABLE [dbo].[DatabaseSize] ADD CONSTRAINT [DF_DatabaseSize_IndexUsed(MB)] DEFAULT ((0)) FOR [IndexUsed(MB)]
ALTER TABLE [dbo].[DatabaseSize] ADD CONSTRAINT [DF_DatabaseSize_Unused(MB)] DEFAULT ((0)) FOR [Unused(MB)]

DBARepository – GatherDatbaseSizes SSIS Package

The SSIS package that gathers the database sizes is simple and only contains a few tasks that I summarize briefly.  Here ‘s the package:

DBA Repository - Gather Database Sizes

Finding the SQL Server instances and looping through each one is discussed in a previous post.

 

Load Database Size Data to Staging Execute SQL Task

Once connected to a SQL Server instance in the loop the Load Database Size Data to Staging task is executed.  The query will work on SQL 2000 and later instances.  This task completes two things:

  1. Create a staging table (dbo.cos_DBStatistics_DeleteMe) in master on the SQL Server instance it’s currently connected to
  2. Populate that staging table with database size information

Here’s the query:

IF OBJECT_ID('[master].dbo.cos_DBStatistics_DeleteMe') IS NOT NULL
    DROP TABLE [master].dbo.cos_DBStatistics_DeleteMe
CREATE TABLE [master].dbo.cos_DBStatistics_DeleteMe (
      ServerName sysname
    , DatabaseName sysname
    , Polled datetime
    , [DBSize(MB)] decimal(15, 2)
    , [UnallocatedSpace(MB)] decimal(15, 2)
    , [ReservedSpace(MB)] decimal(15, 2)
    , [DataUsed(MB)] decimal(15, 2)
    , [IndexUsed(MB)] decimal(15, 2)
    , [Unused(MB)] decimal(15, 2)
    )
DECLARE @Databases TABLE (DatabaseName sysname)
DECLARE @Database sysname
  , @ExecuteSQL varchar(8000)
  , @Pagesize varchar(18)
  , @ServerVersion varchar(18)
  , @SQL varchar(8000)
SET @ServerVersion = CAST(SERVERPROPERTY('ProductVersion') AS varchar(20))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.', @ServerVersion) - 1)
-- finding the server's bytes per page
SELECT  @Pagesize = d.low
FROM    [master].dbo.spt_values AS d
WHERE   d.number = 1
        AND d.[type] = 'E'
 
--  Gathering stats on SQL 2000 and earlier versions
IF CAST(@ServerVersion AS tinyint) <= 8
    BEGIN
        INSERT  @Databases (DatabaseName)
                SELECT  d.name
                FROM    [master].dbo.sysdatabases AS d
 
        SET @SQL = '
DECLARE @DBSize bigint
, @LogSize bigint
, @Pages bigint
, @ReservedPages bigint
, @ServerVersion varchar(18)
, @UsedPages bigint
 
-- loading the mdf and ldf files sizes
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
 
--  finding total pages used, this is all the data in the database
SELECT  @Pages = SUM(CONVERT(decimal(15), dpages))
FROM    dbo.sysindexes
WHERE   indid < 2
 
SELECT  @Pages = @Pages + ISNULL(SUM(CONVERT(decimal(15), used)), 0)
FROM    dbo.sysindexes
WHERE   indid = 255
 
-- finding used and reserved for calculations to find the pages used in the indexes
SELECT  @ReservedPages = SUM(CONVERT(decimal(15), reserved))
, @UsedPages = SUM(CONVERT(decimal(15), used))
FROM    dbo.sysindexes
WHERE   indid IN (0, 1, 255)
 
-- storing the output
INSERT INTO [master].dbo.cos_DBStatistics_DeleteMe
SELECT  ServerName = @@SERVERNAME
, DatabaseName = DB_NAME()
, Polled = GETDATE()
, [DB Size(MB)] = CAST(((@DBSize + @LogSize) * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
, [Unalloc. Space(MB)] = CASE WHEN @DBSize >= @ReservedPages
THEN CAST(((@DBSize - @ReservedPages) * ' + @Pagesize + ' / 1048567.) AS decimal(15, 2))
ELSE 0
END
, [Reserved(MB)] = CAST((@ReservedPages * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
, [Data Used(MB)] = CAST((@Pages * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
, [Index Used(MB)] = CAST(((@UsedPages - @Pages) * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
, [Unused(MB)] = CAST(((@ReservedPages - @UsedPages) * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
'
    END
-- Gathering stats on SQL 2005 and later versions
IF CAST(@ServerVersion AS tinyint) > 8
    BEGIN
        INSERT  @Databases (DatabaseName)
                SELECT  d.name
                FROM    [master].sys.databases AS d
 
        SET @SQL = '
DECLARE @DBSize bigint
, @LogSize bigint
, @Pages bigint
, @ReservedPages bigint
, @ServerVersion varchar(18)
, @UsedPages bigint
 
-- loading the mdf and ldf files sizes
SELECT  @DBSize = SUM(CONVERT(bigint, CASE WHEN type IN (0, 4) THEN size
ELSE 0
END))
, @LogSize = SUM(CONVERT(bigint, CASE WHEN type = 1 THEN size
ELSE 0
END))
FROM    sys.database_files
 
-- finding used and reserved for calculations to find the pages used in the indexes
SELECT  @ReservedPages = SUM(a.total_pages)
, @UsedPages = SUM(a.used_pages)
, @Pages = SUM(CASE WHEN it.internal_type IN (202, 204) 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
 
-- storing the output
INSERT INTO [master].dbo.cos_DBStatistics_DeleteMe
SELECT  ServerName = @@SERVERNAME
, DatabaseName = DB_NAME()
, Polled = GETDATE()
, [DB Size(MB)] = CAST(((@DBSize + @LogSize) * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
, [Unalloc. Space(MB)] = CASE WHEN @DBSize >= @ReservedPages
THEN CAST(((@DBSize - @ReservedPages) * ' + @Pagesize + ' / 1048567.) AS decimal(15, 2))
ELSE 0
END
, [Reserved(MB)] = CAST((@ReservedPages * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
, [Data Used(MB)] = CAST((@Pages * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
, [Index Used(MB)] = CAST(((@UsedPages - @Pages) * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
, [Unused(MB)] = CAST(((@ReservedPages - @UsedPages) * ' + @Pagesize + ' / 1048576.) AS decimal(15, 2))
'
    END
 
DECLARE dbs CURSOR FAST_FORWARD
    FOR SELECT  d.DatabaseName
        FROM    @Databases AS d
 
OPEN dbs
 
FETCH NEXT FROM dbs INTO @Database
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ExecuteSQL = 'USE [' + @Database + ']' + CHAR(13) + @SQL
--PRINT @ExecuteSQL
        EXECUTE (@ExecuteSQL)
        FETCH NEXT FROM dbs INTO @Database
    END
 
DEALLOCATE dbs

Gather Database Size Info Data Flow

The Gather Database Size Info data flow is a simple OLE DB Source to OLE DB Destination with the source being our populated dbo.cos_DBStatistics_DeleteMe staging table for the current SQL instance in the loop.  The destination is the dbo.DatabaseSize table in the DBARepository.

Cleanup Staging Objects Execute SQL Task

The Cleanup Staging Objects task simply drops the staging table from our source server instance so it’s not dangling out in master when it isn’t necessary.

Summary

Gathering database size is not too difficult.  The SSIS package perform the following steps:

  • Loop through all SQL Server instances we’re gathering data for
  • Build a staging table in master at each source server in the loop
  • Gather DB size information for each database on the source server and store it in the staging table
  • Transfer all data gathered from the staging table to the DBA Repository
  • Cleanup the staging table

I can run this SSIS package as often as I’d like, I run it daily.  Having the daily size allows me to build tools to trend the data over time.  I’ll demonstrate some table-valued UDFs to show how I calculate annualized rates of growth in a future article.

Hopefully, you’ll find that info useful in your endeavors.

6 Responses to DBA Repository – Gather Database Size Info

  1. DBA Dave says:

    Thanks, and BTW I’ll be making the DBARepository available as a download soon. It’ll have all the create scripts for the DB, SSIS packages and SSRS reports I’ve created to view all that data collected…

  2. Paul says:

    Great post Dave. Have you gotten around to making the DB, SSIS packages and SSRS reports?

  3. DBA Dave says:

    Hi Paul,
    I actually do have a full, installable version of the DBA Repository completed SSIS, SSRS and DB all ready to go. I’ve been delayed in finishing the posts and making it available for download, however, that tops my to-do list this weekend. Keep an eye out – it’ll be made available soon…

  4. Joel says:

    Very nice writeup. No download yet? Link to ‘Series of Posts’ on how to maintain a list of servers appears to be broken too… 🙁

  5. DBA Dave says:

    Hi Joel,
    Looks like the link to the previous article was broken after I updated my permalink structure. Thanks and all fixed. 🙂

    Also, you can download the DBA Repository here.

  6. dp says:

    You COMPLETELY saved me as I was unable to save temp tables to master. i was able to work with your code in getting sizes! WHOOT!

Leave a Reply

Your email address will not be published. Required fields are marked *