November 9, 2011 at 8:18 am
I am wondering if someone could please confirm my understanding about logs files in full recovery mode, please.
My understanding is that when I take a SQL Full backup my log files are cleared out (NOT SHRUNK) and new trans log data is saved. At the end of each trans backup a marker is added to the File to signify the trans backup. This file "wraps around" if there is free space. If the file becomes full then it expands.
Thanks
Taggs
November 9, 2011 at 8:22 am
Taggs (11/9/2011)
My understanding is that when I take a SQL Full backup my log files are cleared out (NOT SHRUNK) and new trans log data is saved.
No. Full backups don't do anything to the log
At the end of each trans backup a marker is added to the File to signify the trans backup.
Err, no. Log backups truncate (mark as reusable) portions of the log file.
This may help: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Edit: Removed something that could be misconstrued.
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
November 9, 2011 at 9:02 am
Hmm, I'm a little confused. Although the file still has the data (which I thought it had cleared) that portion is now reusable?
The article says the following
If a log backup now runs, the third and fourth VLFs (C and D) would be marked inactive and would be available for SQL to reuse them.
Surely this is the same/simlar effect as empting the file out (without shrinking)??
Also the document states that the file does wrap around (also indicated in the diagram.
After a number of data modifications, the log has wrapped around and SQL has now reused the first VLF and is about to start reusing the second.
I'm really sorry if I'm being thick!
Taggs
🙂
November 9, 2011 at 9:21 am
Taggs (11/9/2011)
Hmm, I'm a little confused. Although the file still has the data (which I thought it had cleared) that portion is now reusable?
Yup. Nothing's actually cleared when a log truncation occurs, nothing gets overwritten at the time. All that happens is that portions of the log are marked as 'can be overwritten'
The article says the following
Also the document states that the file does wrap around (also indicated in the diagram.
I never stated that it didn't.
What I said was that log backups don't just put a marker in the log to signal a log backup. They actually truncate the log. It's not the full backup that makes the log reusable, it's the log backups.
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
November 9, 2011 at 9:36 am
Many thanks. It all makes sense now.
It's not the full backup that makes the log reusable, it's the log backups.
Thank you for imparting your wisdom
Taggs
🙂
November 9, 2011 at 9:40 am
Hi, Gail.
That's an excellent article. Surely a result of years of experience.
Can I add a little related question to this topic?
Article (11/9/2011)
(...) Log backups are the one that most people know can prevent log reuse. In full and bulk-logged recovery models, VLFs cannot be marked as inactive until all the log records in that VLF have been backed up. (...)
If a transaction is rolled back, will the VLF used by that transaction be set to inactive even without performing a log backup? Or it must be kept active until the log backup is made?
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 9, 2011 at 9:50 am
codebyo (11/9/2011)
If a transaction is rolled back, will the VLF used by that transaction be set to inactive even without performing a log backup? Or it must be kept active until the log backup is made?
In full and bulk-logged recovery the only thing that marks a VLF as inactive is a log backup.
Imagine, if a transaction roll back did mark a VLF inactive, what would happen to all the other transaction log records in that VLF. They wouldn't eb backed up, would be lost on a log ship secondary. Not great for DR.
Also, imagine if a transaction rolled back and the roll forward portion had already been written to the data file and the rollback marked the VLF as inactive, what would happen if the server crashed right after the rollback. On restart, crash recovery wouldn't be able to replay the rollback. That's a recipe for a suspect DB.
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
November 9, 2011 at 12:04 pm
GilaMonster (11/9/2011)
In full and bulk-logged recovery the only thing that marks a VLF as inactive is a log backup.Imagine, if a transaction roll back did mark a VLF inactive, what would happen to all the other transaction log records in that VLF. They wouldn't eb backed up, would be lost on a log ship secondary. Not great for DR.
Also, imagine if a transaction rolled back and the roll forward portion had already been written to the data file and the rollback marked the VLF as inactive, what would happen if the server crashed right after the rollback. On restart, crash recovery wouldn't be able to replay the rollback. That's a recipe for a suspect DB.
I was failing to see that the sequence of events in a database is important.
Actually I totally forgot about all that after reading about VLFs.
Thank you for your detailed explanation.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply