My first attempt at a SQL Tuesday post. These are a collection of posts on the second tuesday of the month that are thematically linked. In this case, Sankar Reddy is hosting, and his choice of topic is “Misconceptions in SQL Server”.
“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, 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. I put all the data into Excel and came up with this graph:
That’s a slightly alarming growth rate, particularly in the last half of the graph. This was found to be due to a debugging / logging configuration change made by our supplier without providing us with information about what he was doing. We turned it off, and the size of the database dropped back down to somewhere around the 45GB mark. Growth since then has been around the 5% per week mark.
He got the point.