Query to calculate database growth

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

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.

StackExchange Database Administrators Q#112038, answer score: 6