Wednesday, April 8, 2009

[Backups] Determine your database growth via backup history

One thing about backups in SQL Server is that the history is kept forever, unless you clean it up using a maintenance plan, or one of the not-very-well-documented SPs.

But we can have it work for us.
Case in point - database growth estimations. This is a basic view that will show what your database growth has been like for the past 60 days. Turn it into a chart with reporting services, and you can see what's growing, at what rate, and what you need to be concerned with.

Yes, this is pretty basic code, but I hadn't seen anybody do this before.
And obviously, if you're cleaning up your backup history this won't necessarily do much.

CREATE VIEW [dbo].[backup_history]
-- catalog_family_number, --not sure what this does; unclear in BOL
backup_size/1000000 AS backup_size,
CONVERT(CHAR(12),backup_start_date,101) AS backup_date--,
-- in case you want to look at a particular type of backup
-- CASE [type]
-- WHEN 'D' then 'Database'
-- WHEN 'I' then 'Differential database'
-- WHEN 'L' then 'Log'
-- WHEN 'F' then 'File or filegroup'
-- WHEN 'G' then 'Differential file'
-- WHEN 'P' then 'Partial'
-- WHEN 'Q' then 'Differential partial'
-- END AS Backup_Type,
-- [NAME],
-- [description]
FROM msdb.dbo.backupset
WHERE [TYPE] IN ('D','F') --full backups, though tlogs could be interesting
AND server_name = @@SERVERNAME
AND database_name NOT IN ('msdb', 'MASTER', 'model')
AND backup_start_date > GETDATE()-60
--ORDER BY SERVER_name, DATABASE_name, backup_start_date, catalog_family_number

No comments: