Sql-server – Query to calculate database growth

data collectionmanagement-data-warehousesql server

I have the following query that outputs some good information from MDW data collectors regarding database space usage. I am wondering with the following columns (db size, reserved space, unused space, unallocated space, collection date) how can i properly calculate database growth trend? I am trying to keep whether a db shrinks or grows into consideration so I cannot just take "max size" and go from that. Here is the full query so far…

DECLARE @ServerName VARCHAR(MAX);
DECLARE @SelectedDatabaseName VARCHAR(MAX);
DECLARE @snapshot_id INT;

SELECT TOP 1
        @snapshot_id = snapshot_id
FROM    ( SELECT DISTINCT TOP 100
                    d.snapshot_id
          FROM      snapshots.disk_usage d ,
                    core.snapshots ss
--WHERE ss.instance_name = @ServerName
--AND ss.snapshot_id = d.snapshot_id
ORDER BY            d.snapshot_id DESC
        ) AS q
ORDER BY snapshot_id ASC;

SELECT  database_name ,
        ss.instance_name ,
        CONVERT (DATETIME, SWITCHOFFSET(CAST (d.collection_time AS DATETIMEOFFSET),
                                        '+00:00')) AS collection_time ,
        d.snapshot_id ,
        ( ( CONVERT(DEC(15, 2), d.dbsize) + CONVERT(DEC(15, 2), d.logsize) )
          * 8192 / 1048576.0 ) AS 'database_size_mb' ,
        'reserved_mb' = ( d.reservedpages * 8192 / 1048576.0 ) ,
        'data_mb' = CONVERT(DEC(15, 2), d.pages) * 8192 / 1048576.0 ,
        'index_mb' = ( d.usedpages - d.pages ) * 8192 / 1048576.0 ,
        'unused_mb' = ( ( CONVERT(DEC(15, 2), d.reservedpages)
                          - CONVERT(DEC(15, 2), d.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'unallocated_space_mb' = ( CASE WHEN d.dbsize >= d.reservedpages
                                        THEN ( CONVERT (DEC(15, 2), d.dbsize)
                                               - CONVERT (DEC(15, 2), d.reservedpages) )
                                             * 8192 / 1048576.0
                                        ELSE 0
                                   END )
FROM    snapshots.disk_usage d ,
        core.snapshots ss
WHERE   --database_name =@SelectedDatabaseName
/*AND*/ d.snapshot_id >= @snapshot_id
--AND ss.instance_name = @ServerName
        AND d.snapshot_id = ss.snapshot_id
ORDER BY d.database_name ASC ,
        collection_time;

Hopefully at the end of it I will be able to see in a year from now how much every instance, database and server grows per year.

Best Answer

Method 1 : Comes with a cost

You can use a third party software which will do everything for you in terms of gathering the data and presenting the reports for database growth and predicts the same depending upon the gathered data as explained here

Method 2: Create table, run the stored proc using SQL agent job and scheduled accordingly to gather the data in the created table and then query it over a period of time to see what has been collected and predicts with all you're calculations handy:

You can use this as explained here

Method 3: Little more on Manual side but works good, is using the default trace. Use the default trace to capture the growth events on data and log for that database , depending upon how frequent or how long the default trace file stays there and does not get rolled over:

Method 4 My personal favorite and the one i have been using:

This excellent article from Chad Miller Database capacity planning which uses T-SQL and Powershell along with SSRS reporting to give you the desired database growth over a period of time.

Related Question