Estimate database growth

[janpetrucci]

Hello,

I am creating a new SQL database on SQL Server 2000.

I have an idea of the number of records I will be loading on a weekly basis.

Does anyone have any information on how I can estimate the size the database will reach in six months?

Thanks in advance for any information provided.

JP

 

[vongrunt]

There are ways to calculate/estimate table/index size based on structure and estimated number of records.
Size of database = at least sum of all table sizes, estimated of course

Is that what you want?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

One way
run dbcc showcontig ('TableName') with tableresults for all your tables
Multiply the (AverageRecordSize field) * (rows per week)
(Sum it all up * 26) would be your 6 month size


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[janpetrucci]

Thank you both for your helpful posts.

Yes, I do want an estimated size of all the database.
JP

 

[vongrunt]

Good estimates are hard to calculate. While used math is simple, entire procedure is tedious at best. You must take into account table type (heap or clustered), nonclustered indexes and some other things - and then repeat procedure for every table. Here is link with more details.

What is sometimes simpler: create database, monitor table/database size for 2-3 weeks with sp_spaceused/DBCC or taskpad view in EM. Then simply interpolate measured value(s) up to 26 weeks in future.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

As VonGrunt said that math is fairly basic.

Take the average number of bytes for each record, times the number of records per week, times 26 weeks.
Repeat for each index on the table and sum up the data. That will give you a rough number for that table.

Now repeat for each table in your database.

If you have a larger row size and tons of records, you may want to include the page size in your math as this will account for the waisted space in the page.

Take the average number of bytes per record and divide that into the number of bytes per page (8192 is default). Round that number down to the next lowest integer. This will tell you how many records you can store in one page. Divide the number of records by the number of records per page to get the number of pages. Then multiply that number by 8k to get the amount of space that will be used.

It will look something like this.
Average Record Size of 115 bytes.
Average Number of Records per day of 300,000.
We can fit 71.23478... records per page so we round that down to 71 records per page.
300,000 / 71 = 4225.35 (Rounded to 4226 pages)
4226 * 7 (days) * 26 (weeks) = 769,132 pages
769,132 * 8k = 6,153,056k (6,008.8 Megs)

If you do the more basic math you get 5,988.1 Megs for the table, so there is a small difference. As time goes on this difference would get larger.

Then repeat this for each index you need to create.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[janpetrucci]

Thanks Again.

I will give it a try...could use the practice in math as well!