June 18, 2002 at 9:50 am
hi there, I have a very large database on sql 2k server, which is 15G by now, and might increase 20 to 30m daily. After migrating the data from older sql server, now it is stored on the new sql2k server, as data file d:\mssql\data\proddb.mdf 18G
log file e:\mssql\log\proddb.ldf 1G.
I was wondering if this data and log file allocation is optimal. should we use serveral smaller files instead of a huge data file?
advise and commments welcomed.
June 18, 2002 at 10:47 am
If you have more than one Drive or Raid array that it could be split across, then it would be worth splitting up the IO by placing it onto all available IO Channels(drives or arrays). Otherwise it wouldn't be much better than leaving it as one file. Sometimes two different Drives can be sharing the same IO channel, so check with your NT Admin if you are unsure.
June 18, 2002 at 4:10 pm
Also depends on how often you backup your server in regards to intial size. If the full backups are down weekly you should backup and empty the Transaction Log right before then do a Full backup so you have a clean file. In this case the 1 GB may be enough, not enough, or too much, just depends on how large it gets average, I usuaully aim 1.2 times the average weeks size as optimal.
As for the data file as danw states is right on, however keep in mind the grown and I would throw that if you know the file will need 20-30m daily that to limit new growth figure to give as much room as possible next growth to give a long period between growths. For instance I may say use a week so the autogrow will be set to 30*7 which is 210m or rounded to 300m (I personally choose fixed growth as oppossed to percentage based as SQL will have to calculate the next growth and the size will vary each time. With a fixed I know what to expect.
Also personally I have found taking fixed files, support tables, that never to rarely change do great in a seperate file and filegroup as you can defrag the physical drive and they will not be affected so much by other changes.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply