Trending Database Growth from Backups
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