May 20, 2010 at 3:35 am
As an 'involuntary dba' I've always wondered why 2 log backups are required before I can successfully reclaim log space. I can't find any reason via google and the msdn just says:
"If the reason is LOG_BACKUP, it may take two backups to actually free the space."
May 21, 2010 at 1:49 am
check this link for more info
the following info is relevant
A log backup is required to move the head of the log forward (full or bulk-logged recovery models only).
Note:
Log backups do not prevent truncation.
When the log backup is completed, the head of the log is moved forward, and some log space might become reusable.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 21, 2010 at 2:08 am
Thank Perry. I have read that. But it still doesn't explain why 2 x backups are required, if anything that article insinuates that 1 backup should be enough! "When the log backup is completed, the head of the log is moved forward, and some log space might become reusable."
May 21, 2010 at 5:27 am
I actually thought about it... have seen this on numerous occasions... the first log backup can actually be as big as the log file itself, the second log backup will be very small... Only after this will we be able to shrink..
I may not actually be able to answer this in a convincing manner but I would give it a try... Microsoft in the description of reuse of transaction log space says "If the reason is LOG_BACKUP, it may take two backups to actually free the space". So, only after the space has been freed can you successfully reclaim the space...
May 21, 2010 at 6:30 am
I think it's because in the time you backup your trn log the first time it writes at the end of your transaction log because the file is stil filed up. so shrinking the file will give you only a little space at the end of the file back because it shrinks only from end to last log entry. then you back up again. this time the new transactions can be writen at the begin of your now mostly empty file and the shrink can free much more space..
or are i wrong?
May 21, 2010 at 7:32 am
Alex Webber (5/21/2010)
Thank Perry. I have read that. But it still doesn't explain why 2 x backups are required, if anything that article insinuates that 1 backup should be enough! "When the log backup is completed, the head of the log is moved forward, and some log space might become reusable."
Hi Alex
the way i understand it the first log backup moves the log head forward, the second cycles back to the beginning of the file assuming no open transactions are occupying the end of the log. Hence the statement
If the reason is LOG_BACKUP, it may take two backups to actually free the space.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 21, 2010 at 7:35 am
Thanks Chaps. The moving the head forward to flush then back to use up the preallocated, but now unused, space makes sense. 🙂
May 21, 2010 at 8:52 am
It really depends upon which VLF is the active one. If the active VLF is somewhere in the middle of the file, performing a log backup just moves to the next VLF. To shrink, you have to move the active VLF to the beginning of the log file.
Step 1: perform LOG backup to free up space
Step 2: shrink
Step 3: if did not shrink to expected size, perform another log backup (now it rolls to beginning of file)
Step 4: shrink
Once you have done this, you need to review Kimberly Tripp's articles on this subject, as well as review the article I link to in my signature.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply