As we start to look at trending the database growth and backup strategies, I’m starting to gather a couple of different metrics. One I’ve blogged about before, which is monitoring the changes to the database based on Paul Randall function (read my post here). This lets me track weather it’s best to do nightly full backups or maybe look at saving some space and time with putting in some differentials.
 
The other, is to look through MSDB and track the history of backups, durations and size help indicate how much the database is growing. I’ve been using my new PowerShell functions to auto populate Excel and simply add a pivot table and chart to get some handy information.
 

/*
Trending Database Growth From Backups
*/
SELECT  server_name AS 'Server' ,
database_name AS 'Database' ,
CONVERT (DATE, backup_start_date) AS 'Date' ,
DATEDIFF(SECOND,backup_start_date,backup_finish_date) AS 'Duration',
backup_size  / 1024 / 1024 AS 'Backup Size MB',
compressed_backup_size / 1024 / 1024 AS 'Compressed Backup Size MB'
FROM    msdb.dbo.backupset AS BS
WHERE bs.type = 'D' -- D = database L = Log
AND CONVERT(DATE, bs.backup_finish_date) > DATEADD(Month, -1, getdate()) -- Last Months data
ORDER BY bs.database_name, bs.backup_start_date

 

 

Advertisements