DB Size for Online Database.

Question

DB Size for Online Database.

IN_Sandeep

SSCertifiable

Points: 6259
More actions
December 6, 2007 at 3:25 am

#184530

Hi All,
I wants to Create a Database for a Online System.
Its Related to Manage Customer Details.
Here my 1st question is :
What should be the initial Database Size for a any online system?
lets assume If the database contains min 10 Tables. 1,000 rows of each,
then,
2nd qst: What should be my Database Size & what should be my log file size and max growth size?
Cheers!
Sandy.
--

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

That is going to depend on many things that you really stand very little chance of describing here.

From your description, I do not think you are very far along in your design, so don't worry about the initial size. Take the defaults and allow automatic growth. Once you have a design, you should be able to look at the size of your records (and their indexes) and make an estimate.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

I have to agree. The beauty of the auto-grow is that you don't have to sweat details like that sometimes. The only suggestion I'd make is to set the auto-grow to go by actual size increments, not percentages. You'll find that that if you leave the default in place and let it grow by percentage, the growths become slower and slower and more and more painful for the system as the size of the database increases.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

Hi Michael & Grant ,

Thanks for your quick response.

As per Grant, the growth should be Sql Server wise but wats about the log file size?

I hope the same regarding growth.

but Is there any guidelines need to follow before develop the SQL DB..

If yes, Please guide me...

If DB is small,

Is it necessary to follow the File Partition Concept?

Cheers!

Sandy.

--

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Partitioning is a whole different discussion, that I'm frankly not qualified to take part in.

As to the size of the log... I treat it the same as the data. I let the initial size go at the default for most databases. I set a growth size that seems appropriate to the db and the server. Depending on the server, I put a file size limit on the file so it can only grow so large. I find this especially important with the log because they can sometimes grow out of control.

I do try to split up the file storage into as many file groups as seems appropriate (minimum, one for data, one for index, one for blobs and one for logs) and, where possible distribute them across drives (we use a SAN, so although we create different "drives" for the storage, we don't have a lot of control over what gets stored where).

Except for partitioning, does that answer the question?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

Here's the thing. Grant has good advice, but I'd calculate the row sizes, guess at how many rows will get inserted in a year, then double that, add 50% for your bad guess, and set the database size there.

Auto grow is nice, but you don't want it happening when your system is busy. It's an emergency thing. Track the database size and grow it manually when needed.

Partitioning into filegroups is ok, though I'd avoid this unless you need to (administrative hassle) and unless you have separate IO paths.

For the log, you have to guess. Logs tend to be smaller than dbs, especially if you back them up often, but you have to watch them. Same thing, you don't want it auto-growing if needed.

If I had 10GB database, I'd probably set up a 1GB log, track the sizes, and decide. If I ever got large, I'd just set it to 2GB or 4GB. Disk is cheap and a larger log doesn't slow your system.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

Steve's much more accurate than I am. You have to remember, I spend most of my time working in development, not production. That makes me a lazy b*****d.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 7

Hi Steve & Grant,

Really feel great to know this Answer,

I really Appreciate both of you.

Now I am Clear on my question.

Thanks again for the Same.

Cheers!

Sandy.

--