Databases usually grow. You can size the database files so that you start from day one with the database files as large as you ever expect them to be, but that is rarely 100% accurate. Things change and databases usually grow. I was reading an article over at The Lone DBA blog and thought it would be helpful to pass along this post.
“Databases don’t grow” – so said a systems / technical architect at a company I was working at recently.
My rebuttal was to query the msdb database for historical information about the size of the backups using the following query:
SELECT Server_Name, Database_Name,
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Transaction Log'
WHEN 'I' THEN 'Differential Database'
WHEN 'F' THEN 'File or Filegroup'
WHEN 'G' THEN 'Differential File'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
END AS Backup_Type,
backup_start_date, recovery_model, backup_size, compressed_backup_size
For older data, I found a directory where some more ancient backups had been stored.