June 21, 2010 at 2:23 am
Dear Readers,
According to your experience what we should set the size of following fields while creating a new database.
MDF Initial Size = ?
LDF Initial Size = ?
MDF
—-
Enable Autogrowth = ?
File Growth = ?
Maximum File Size = ?
LDF
—-
Enable Autogrowth = ?
File Growth = ?
Maximum File Size = ?
Thanks,
Adnan
June 21, 2010 at 3:07 am
It really depends on what you are planning to do with this database and how the storage is.
My typical settings are something like this.
MDF Initial Size = 100
LDF Initial Size = 100
MDF
—-
Enable Autogrowth = yes
File Growth = 100 Mb
Maximum File Size = it depends
LDF
—-
Enable Autogrowth = yes
File Growth = 50 Mb
Maximum File Size = it depends
Maximum File Size depends on how you expect the DB to grow, the recovery model, how you're planning to work with backups and lots of other things.
-- Gianluca Sartori
June 21, 2010 at 4:08 am
It's depend on the size of your database, nature of your database i.e. transactions, Recovery model of your database, frequency of your T-log backup etc. As a thumb rule if the database is OLTP you can consider LDF as 25% of MDF.
Also as mentioned above file growth should be mentioned into MB compare to percentage for performance reason. Also if you are keeping the MDF and LDF on local storage than keep MDF & LDF on different drive.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 21, 2010 at 6:17 am
adnan8t2 (6/21/2010)
Dear Readers,According to your experience what we should set the size of following fields while creating a new database.
MDF Initial Size = depends on system estimates
LDF Initial Size = depends on system estimates
MDF
—-
Enable Autogrowth = Yes
File Growth = 100mb
Maximum File Size = Depends on the systems estimates and the drive the file is on
LDF
—-
Enable Autogrowth = Yes
File Growth = 50mb
Maximum File Size = Depends on the system estimates and the drive the file is on
Thanks,
Adnan
I just replaced the question marks. Unfortunately, most of this is really dependent on what we're setting up. Except for database & log growth, I almost never standardize these things.
"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
June 21, 2010 at 6:24 am
Thanks to all of you guys for such a quick & informative reply.
Regards,
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply