February 3, 2011 at 8:03 pm
Comments posted to this topic are about the item Clearning the transaction log
February 3, 2011 at 8:05 pm
Nice easy question.
It will be interesting to see how many manage to get it wrong.
Tom
February 3, 2011 at 8:58 pm
I got it wrong. But am surprised that when a database is set to simple recover model, it will either clear 0 or all vlf. Good to know this.
February 3, 2011 at 11:39 pm
Another great VLF question!
edit: there's a small typo in the question's title 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 3, 2011 at 11:56 pm
Excellent question. I didn't want to take a chance before taking the question. To be sure, I referred to this link which gave me an idea to answer correctly.
M&M
February 4, 2011 at 1:08 am
Tom.Thomson (2/3/2011)
It will be interesting to see how many manage to get it wrong.
And how many gripe about Steve's spelling :hehe:
Easy question, though I'm concerned explanation is far too simple. There's more involved in this than just size of a VLF. But then, I have been writing an article on tran log reuse for the last 3+ weeks.
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
February 4, 2011 at 1:16 am
Abi Chapagai (2/3/2011)
But am surprised that when a database is set to simple recover model, it will either clear 0 or all vlf. Good to know this.
It won't. It'll only clear VLFs that aren't needed for something - active transaction, DB backup, replication, etc
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
February 4, 2011 at 1:30 am
Good question, but the explanation is overly simplistic. There are other reasons for a checkpoint to not clear a VLF.
I chose the correct answer because I considered the scenario of a database with a long-running transaction. When the oldest active VLF is necessary for tha ttransaction, no checkpoint can ever clear a VLF until the transaction is finished.
And re: the spelling error in the title - I'll just go ahead and say that this is a deliberate word play on "clearing the transaction log" and "learning (about) the transaction log". 😀
February 4, 2011 at 1:47 am
Good question Steve.
Gethyn Elliswww.gethynellis.com
February 4, 2011 at 1:56 am
Hugo Kornelis (2/4/2011)
I chose the correct answer because I considered the scenario of a database with a long-running transaction. When the oldest active VLF is necessary for tha ttransaction, no checkpoint can ever clear a VLF until the transaction is finished.
Similar thinking here...I thought immediately that a VLF can't be cleared if it's in use, and what guarantees they won't be in use for transactions at the time the checkpoint occurs?
February 4, 2011 at 7:08 am
paul.knibbs (2/4/2011)
Hugo Kornelis (2/4/2011)
I chose the correct answer because I considered the scenario of a database with a long-running transaction. When the oldest active VLF is necessary for tha ttransaction, no checkpoint can ever clear a VLF until the transaction is finished.
Similar thinking here...I thought immediately that a VLF can't be cleared if it's in use, and what guarantees they won't be in use for transactions at the time the checkpoint occurs?
Common theme, pretty much my thinking as well.
February 4, 2011 at 7:48 am
Hugo Kornelis (2/4/2011)
There are other reasons for a checkpoint to not clear a VLF.
I also selected the right answer for the "wrong" reason. :w00t:
http://msdn.microsoft.com/en-us/library/ms345414.aspx
My new question today is does Tran Log Truncation or Check Points or Both get delayed by these situations?
Does the Tran Log always get truncated after a checkpoint occurs, or does Datbase Engine try to remove the 0 or more VLF after the checkpoint occurs?
😎
February 4, 2011 at 7:51 am
Great question, too simple of an explanation though..Paul's Blog is very helpful too...:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 4, 2011 at 7:58 am
Oops, hit Send too soon.
Removed.
February 4, 2011 at 8:02 am
SanDroid (2/4/2011)
My new question today is does Tran Log Truncation or Check Points or Both get delayed by these situations?Does the Tran Log always get truncated after a checkpoint occurs, or does Datbase Engine try to remove the 0 or more VLF after the checkpoint occurs?
😎
Checkpoints do not get delayed. They continue to run, writing dirty pages to disk to minimize the recovery period in case of a disaster. In case of the simple recovery model, they also continue to mark space before the minimum recovery LSN (MinLSN) in the log file for reuse - except that, as long as this long-running transaction is still active, MinLSN will be the same every time, so that specific part of the activities carried out during a checkpoint becomes a no-op.
The first checkpoint that occurs after the transaction finishes will find a new MinLSN and mark for reuse (not remove!) all the VLFs that are now no longer needed.
Reference: http://msdn.microsoft.com/en-us/library/ms189573.aspx
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply