Estimating database size

[tezzyr]

Hi everyone,

I'm creating an application which uses an sqlexpress database to store details on company employees.
My database has 14 tables but only 3 of them will ever have any more records added to them (the other 11 tables will never be written to once they have been initially populated)

I need to estimate the size of the database based on the fact that these 3 tables will only have 2000 records in each table maximum.

I thought I could simply create the db and relationships, enter the data in the non changing 11 tables, make a note of the datafile size and then populate the 3 tables that will change with one record each so that i can find out how much one record will increase the filesize.
however, the datafile size remains the same, even if i add further records to the db.

how can i estimate the size of my fully populated database without actually populating it with 2000 dummy records in each table?

As you can probably tell, im a newbie

I need to know the filesize etc so that i can spec out a server to house the database.

Any help you can give will be very much appreciated,

Thanks.

 

[philhege]

SQL stores transactions in the log file (.ldf). These transactions are periodically committed to the data file (.mdf). You have to take both files into consideration, in addition to space used for indices.

The better way is to extrapolate based on the table schema. Search for "Estimating the Size of a Database" in Books Online for more info.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[SgtJarrow]

First, saying the 11 "lookup" tables will never be added to/deleted from, etc is like saying the sun will not rise tomorrow.....we all know that is false. I understand you don't "expect" them to change, but hopefully you can acknowledge the fact that things never stay static. Someone somewhere at some time will want to change a value. No big deal...just making you aware of the fact.

Second, as far as a server necessary to run a database such as you have described - 11 static tables and 3 tables with < 2000 records each is of no size whatsoever considering today's hardware. I would expect I could even run that database off my 1Gb thumb drive with little to no impact as to size. Not that I would, but I could.

HDD space costs almost nothing. You need to consider such things as number of concurrent connections, number of read/writes to the database and amount of information pull via sprocs and queries. Again, I expect this amount of data to be small, as your database is small.

I would concentrate on the number of users....propose processer and RAM appropriately and more than likely SCSI drives. I stress again that you could probably go down to the local electronics store, and buy anything off the shelf and it will run this database just fine... If you are investing in a server...HDD space will be the least of your costs anyway...

If you have any questions/comments about my suggestions, please let me know...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[mrdenny]

Considering that SQL Express doesn't support databases over 4 Gigs in size the database size isn't of much concern.

That said, this is how to figure out table size.
Take the average length of a row. Multiply that number times the number of records. This will give you the basic size of your table.

I think I wrote a FAQ that covers this. If I can find it I'll post it here.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com