Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 months.

I can use the sp_spaceused statement to calculate the actual size but how do I calculate everything else?

6 Answers
6

The other answers are technically correct, but not real-world correct. Here's what you need to ask the business:

What time horizon am I aiming for? In your case, you're looking for a 12-month number.

During that time, will we be archiving data, or keeping all data? In some businesses, you're allowed to (or required to) only keep a certain amount of data, like the last 12 months. In that case, you'll need to figure out the data growth (which the subsequent questions will answer) but then back down to the last rolling 12 months. You can't just say, "Right now that amount of data is 100GB," because if your data volume is growing, then the last 12 months is growing too. The time amount might be constant, but the data is not.

Will we be adding additional users? For example, the business might be growing into new territories or acquiring new customers. If they double the user base, then in some cases, the data will start doubling as well.

Do we expect the business volume to grow? If you're tracking sales on a web site, for example, and you start running Super Bowl or World Cup ads, your data volume can hit the hockey stick growth curve.

Will we be adding additional functionality in the app? If the app suddenly starts storing images, this will dramatically affect database size.

Will we be adding data from another source, or logging new data? If you start capturing web site clicks, or in a data warehouse, adding additional sources, then data volume will grow.

Will developers or DBAs be performance tuning indexes? If you're going to let people create indexes, you can easily double (or triple, or quadruple) the size of your data depending on how overzealous they get.

And as long as you're asking these questions, you should also ask if performance is expected to stay the same, degrade, or get better. I like mapping out the projected growth on a line chart, and then comparing hardware and staff training investments over that same timeline.

You can't accurately project future growth without a history of previous growth. You can however cheat and get a rough trend using backup history, as detailed by Erin Stellato in Trending Database Growth From Backups.

I am using above query and it is giving me result like SSISDB 11059.5 10233.6 9322.9 8338.8 7675.6 7075.1 6383.7 5592.6 4862.1 (for 0,-1,-2,-3... etc) What does this value mean? Does it mean that my row size in MB is 11059 and it will be increase by 10233 mb next month? I am confuse with the output.. can you please assist me
–
ZerotoinfinityMay 27 at 12:28

There is other method involving mathematical calculations and this would give accurate results. As already pointed backups would be best to refer to data growth since you said you need to calculate and predict size of database below Microsoft links would help you

I think Brent Ozar's post is spot on. I've been in a massively bloating DB project and had exactly the same issue you do here, and it just isn't that simple.

Since it's better to at least do something - even if not really that accurate -, I'd set up the required tables and a job (or whichever other method you want, anything to just query the sizes and store it somewhere reliably) to track the rows and space used for DB and all its tables on a weekly basis and use that to project the most likely growth curve. Using the backup history is also a great idea. But regardless of the method, you need time for getting even remotely reliable data.

Other than that, it really depends on your situation. It may be the use% of your DB is now only a fraction of what it will be in the next 6 months, for example when your software gains more ground, making it impossible to predict the explosive growth that's coming. It may be there are yearly massive data transfers that will double the size of the DB, but you will only find out about that mass after the fact.

But as said, if growth is a concern, then you absolutely should do something to keep track of it. Last thing you want is to find yourself 6 months from now with a DB twice as massive as its original lifetime projection, having to explain to your customer how or why that happened, not to even mention having to start guessing how much more it will grow in the next 6 months. There are also some very obvious benefits of knowing where the new data has gone and what's the relative growth of each table in a given amount of time, as it can provide valuable information on different trends, potential software issues etc. all for relatively small effort.