Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2005-11-16 : 03:57:33
|
| What's the best way to decide a good size for the transaction log for a OLTP database in Simple mode? Just a % of the db size? By monitoring traffic?I've got it to auto grow by 10% when needed. But I have a shrinkfile running nightly. If I just monitor it and see what the largest is that it autogrows to while in use, my plan is to just shrink it to that every night. Is that approach good enough and will it give me the best performance?It's a 6gig db.I'm not using autoshrink |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-16 : 15:53:36
|
| "Just a % of the db size?"I think that's a bad plan. Creating large extensions to MDF files kills SQL server IME. A fixed (but "generous") extension size is better IMHO"I have a shrinkfile running nightly"Also a bad plan IMHO as each day the file will be re-extended - so will fragment the file and consume CPU resources.How big does the TLog file get if you don't shrink it? 120% ~ 130% of the MDF file would be "normal" in my bookKristen |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-11-17 : 03:48:03
|
| are you saying it's ok to have a ldf file that is larger than the mdf file? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-17 : 04:02:08
|
| it's ok to have a large or small tlog file as long as you are able to manage itwhat's not ok is having to shrink a file that will eventually grow back again. why not allow it to grow to a certain size and maintain it at that using log backups?--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-17 : 05:42:15
|
"are you saying it's ok to have a ldf file that is larger than the mdf file?"Yes, provided the filesize is "stable" - not continuously growing, and didn't suddenly leap to a much bigger size (e.g. because of a one-off massive deletion)We allocate a best-estimate initial size to the MDF and LDF, and then take the DB offline, and defrag the physical file, then put it back online. We do this again after any significant growth in the file (which obviously includes shrinking it and it then growing again ).Kristen |
|
|
|
|
|
|
log maintain