SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Managing Your Database

It seems that I see posts like this one more often than I'd like. Or at least some variation on autogrowth issues or sizing problems. Here's another one where someone apparently doesn't realize that you want to keep fre space in your database files. There's actually a simple solution, but not one that avoids work.

Track your database size.

Actually I'd recommend tracking your backup file sizes (data and log) since what you are looking for is a rough growth rate, not an exact measurement. Databases can change size quickly, so trying to worry about an exact size doesn't make sense to me. Usually by the time I get a daily report of database sizes, the size might have substantially changed, especially for systems that import data or log things like daily activity. So comparing the last two database sizes (or the sum of the last xx hours of log backups) will give me an idea of database growth over time.

I can also generate a standard alerts from this data. I used to average out a week's worth of backups and use that to predict growth. If I had growth yesterday that was more than 20% larger than what I expected, I raise an alert to go check things. That often indicates that some load or something else happened, and it allows me to proactively adjust my space requirements. Other alerts I've used

  • If free space drops below 20%, I start to look at resizing the data files
  • If the backup is smaller than the previous one by more than 2%, go check why. This has alerted me to issues in the past.

So can you estimate database size out of the blue?

Not without more information. You have to have an idea of what the load will be on your database. How much data will get entered on a weekly basis. If you don't know that, then you're just placing a guess. In which case I'd be prepared to add space if you estimate turns out to be wildly off. If you have a SAN, then you can more easily do this, even shrink your space if needed. If you are using DASD (direct attached storage), then I might have a few extra drives available that I could slip in there if needed.

In any case, this often comes down to a monitoring solution. It can be as simple as an Outlook reminder to record the sizes in Excel (not recommended), or a simple query that runs every day (use a job) and stores the data in a table. If you want to tackle the latter, here's what I recommend:

  1. Create a small administrative database
  2. Create a table that has a database name, a date, a type, and a size
  3. Query backupset in msdb to get the data from the backup. Filter for all backups in the last 24 hours
  4. Insert this data into your table
  5. Schedule this to occur on a daily basis.

I'll leave it to you to write the actual code, but if you have questions, please feel free to ask in our forums. Once this is done, you can easily query this table over time and see what your growth is. That will allow you to better set your database and log sizes.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by simon.murin on 23 August 2009

The way does not work for compressed backups in SQL 2008.

Leave a Comment

Please register or log in to leave a comment.