Database Growth Myths

teamwork

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.

Wh…?

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,
CASE [type]
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'
ELSE '???'
END AS Backup_Type,
backup_start_date, recovery_model, backup_size, compressed_backup_size
FROM msdb.dbo.backupset

For older data, I found a directory where some more ancient backups had been stored.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s