T-SQL Tuesday #11 – Misconceptions in SQL Server – Systems Architect says “Databases don’t grow”

TSQL2sDay150x150My 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.

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.  I put all the data into Excel and came up with this graph:

So, if data doesn’t grow, what’s going on here?

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.

About these ads
This entry was posted in SQLServerPedia Syndication, Uncategorized and tagged , , . Bookmark the permalink.

4 Responses to T-SQL Tuesday #11 – Misconceptions in SQL Server – Systems Architect says “Databases don’t grow”

  1. So what did he think happened when you added more data to a database? Maybe SQL Server manipulates the space/time continuum to store all rows of data in the same space as the other rows??

  2. thomasrushton says:

    I suspect he was expecting the database devices to be sized once and that that would be sufficient for the run-time of the project…

  3. Pingback: T-SQL Tuesday #11 Round up, Misconceptions in SQL Server | Sankar Reddy, SQL Server Developer/DBA

  4. Pingback: 2010 in review – according to WordPress | The Lone DBA

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