T-log and T-Log Backup

Question

Post reply

T-log and T-Log Backup

Kaushik Majumder

Ten Centuries

Points: 1009
More actions
August 19, 2010 at 1:51 pm

#222763

Hi,
Some confusion regarding Transaction log and backup.. would greatly appreciate any guidance on this.
1. During transaction log backup, is it the inactive portion of the log (LSN< MINLSN) that is actually archived?
2. If so, does the inactive VLFs (after their content being written to the backup destination) gets truncated so that active log can wrap around?
3. Say, at present, T1 my oldest running transaction, the BEGIN TRAN entry in the T-Log for T1 is my , MINLSN (say L000001 in virtual log VLF10002). T2 starts after L000001 and commits.
If the T-Log is backed up now, what will happen to transaction T2. As per my understanding, though committed, these transactions are in the active part of the T-Log, as because MINLSN is in virtual log VLF10002, only VLFs prior to the same (ie VLF10001 and earlier) will be inactive and be backed up.
Please advise.
Regards

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

These links may help you.

http://www.sqlskills.com/BLOGS/PAUL/post/How-do-checkpoints-work.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-More-on-the-circular-nature-of-the-log.aspx

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 2

Kaushik Majumder

Ten Centuries

Points: 1009

August 19, 2010 at 2:06 pm

#1209356

Thanks Ron,

Will go through and update.

Regards.

pankushmehta Ten Centuries Points: 1217 More actions · Answer 3

1. During transaction log backup, is it the inactive portion of the log (LSN< MINLSN) that is actually archived?

This is correct.

2. If so, does the inactive VLFs (after their content being written to the backup destination) gets truncated so that active log can wrap around?

the inactive VLFs are not truncated. they get marked, so that they can be re-used again.

3. Say, at present, T1 my oldest running transaction, the BEGIN TRAN entry in the T-Log for T1 is my , MINLSN (say L000001 in virtual log VLF10002). T2 starts after L000001 and commits.

The transactions will be backed up in the log. if this log is final file to be restored, it will use the information from the T1 for rollback and T2 for the rollforward, if needed.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

pankushmehta (8/19/2010)
1. During transaction log backup, is it the inactive portion of the log (LSN< MINLSN) that is actually archived?
This is correct.

The active portions of the log are also backed up. Not just the inactive portions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 5

Thanks so much Pankush.

I am still not very clear with the last scenario, transaction steps, once recorded in the transaction log, will surely be considered while Analysis/roll forward/Roll Backward phase of the database recovery.... but I was more keen to know what the storage engine will do while backing up, does it look up the MINLSN and blindly goes back to see the inactive potion of log that is not yet backed up, or it looks inside the active portion as well to find out any committed transactions as well?

Regards