August 12, 2007 at 11:07 pm
I am in the process of reindexing our databases. We have log files which are allocated with lot of space which is not used at all. Please note that we have regular log backups sequence every hour in full recovery mode. Will it be good to reduce the space allocated to log files to 10MB or so? If so which command followed by which options will the best and what will be the outcome? I do not want to change the log backup sequence. Plz advise.
August 12, 2007 at 11:34 pm
You can reduce the log files if you want.
What I did at my current gig is to reduce the log file size to the DB size. Then after a few days, the logs will have jumped back up to what they actually need to be for the db to function properly. You then have the option to make them even bigger so that the files don't get fragmented or leave them alone. I'd suggest the former.
August 12, 2007 at 11:52 pm
iIm sorry the answer isnt clear??? Lets say have a log file which is 80MB which I dont need to have so much space with regular backup sequence as well as I can see that only 0% is used all the time. Now if i want to reduce the log file to 10MB what impact will it have on performance? Also include the best command and options to be used without affecting the backup sequence.
August 13, 2007 at 12:29 am
DBCC SHRINKFILE (2, 10)
Performance : File fragmentation could be one of them, but at 80MB I would not worry about it, could slow down and even kill a big transaction if the server needs to go from 10 MB to 50MB in a few seconds, but then again with such small logs I wouldn't worry about it.
Sequence?
Shrink the file as you see fit, then leave it alone forever... unless you know it's way too small or 10 times way too big for your needs.
August 13, 2007 at 7:29 am
Ninja said: Shrink the file as you see fit, then leave it alone forever... unless you know it's way too small or 10 times way too big for your needs.
However, do continue to monitor it, because the only constant is change.
August 13, 2007 at 8:37 am
I said that because the file will grow back to whatever size it needs to be at for the DB to work properly. Once that's done I like to exten the size to the size I think the DB will need in the next X years. That way you can plan for disk usage and you avoid file fragmentation. You also avoid cancelled or slow queries because the servers needs to wait for the file system to grow that log file.
Is that more clear now?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply