mark (5/12/2009)
Hi - I know this topic is a little dead, because it was posted in February, However I am going through this same problem now as we just completed a 2008 upgrade.We backup our database nightly and take transaction log backups every half hour. One db we have is about 5 gigs and the log file is usually about 2 - 2.5 gigs. If a large transaction occurs though, like in loading a new table or something of that nature, the log will grow an equivalent size. As this is not the normal size of the log file, it makes sense to shrink it to conserve disk space. I know some folks would love to throw more disk space at the problem, but another philosophy is to mange the files. If you're shrinking your log file every day that's a problem, but if this is an occasional thing, that makes sense to me. Also, I don't think that your log file should exceed your database size, which will inevitably happen if you leave it alone.
It seems ridiculous that you should need to change your recovery model in order to complete this task. This is not the way that it worked in 2005 and it seems a shame that you should have to jump through hoops to do this simple task.
I would love to hear if anyone comes up with a more reasonable work around.
m
Why would you need to manage the files? This should be a dedicated LUN for the log file only - with enough space to handle all of the transactions your system runs. Repeated shrinking and growing of the transaction log will cause performance issues.
If this is not a dedicated LUN - and you want to be able to 'manage' the file, then what happens when you run your transaction that needs additional space available in the log and there is no space on the disk to grow? I can tell you, your system will come to a screeching halt and no further transactions will be possible until you clear the transaction log and/or add space.
It really does not make any sense at all to be shrinking the log file.
Now, if there is an extraordinary event that you do not anticipate happening again (ever...), then yes it is okay to shrink. Again, if this happens on a regular basis you are much better off keeping the transaction log the max size it needs (actually, make it a bit larger).
Please review the article in my signature on managing transaction logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs