July 21, 2006 at 7:10 am
Good morning. I just read that shrinking a database fragments the indexes. However, what do you do when the transaction log keeps growing, despite transaction log backups and it is taking up too much disk space. Thanks.
Chris
July 21, 2006 at 7:46 am
Hi Chris,
There is a distinction between shrinking a database and shrinking a transaction log.
As you noted, shrinking a database fragments the indexes. This however does not apply to shrinking the log.
Now, regarding your transaction log size. Backing up the transaction log does not free up any disk space. Backing up the log truncates the inactive portion of the log. But if your log grew to 10 GB in between log backups then it will stay at 10 GB in size after the backup.
Now, you're saying that the log is taking up too much disk space. Question is, how much?
If you backup your log regularly then the size of the log shouldn't grow too much so If you're not doing so already, I'd suggest you backup the log every 15 minutes or so (less if necessary). This has the benefit of keeping your overall log size down and reduces the amount of data you might lose in the event of a disaster.
Look at the native size of the log backup files (the ones you're taking every 15 minutes). This will tell you how big the log gets. Using those figures you can get an idea for how big the log is going to get. So if your biggest log backup file is 1 GB in size, that's the bigest your log is going to be.
Using that figure you can shrink your log file to an appropriate size. Allow for situations where the log might grow a little more than expected and use dbcc shrinkfile to shrink it down.
Hope that helps,
July 21, 2006 at 9:19 am
Thank you for the wonderful explanation, Karl.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply