http://www.sqlservercentral.com/blogs/steve_jones/2009/08/20/managing-your-database/

Printed 2014/10/30 12:36PM

Managing Your Database

By Steve Jones, 2009/08/20

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

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.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.