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
February 4, 2011 at 8:38 am
The information found on the following blog might be interesting in regards to this VLF question:
http://ochoco.blogspot.com/2009/03/shrinking-transaction-log-files-in-sql.html
Thanks for the QoTD,
Michael
February 4, 2011 at 8:41 am
I picked the correct answer, but possibly for the wrong reason. Maybe someone could comment?
Consider an unused database with no active transactions. If I either manually issue a CHECKPOINT or stop/restart the server (forcing a CHECKPOINT), but there are no transactions to clear, why would a VLF be cleared?
Thanks,
Rich
February 4, 2011 at 9:15 am
rmechaber (2/4/2011)
Consider an unused database with no active transactions. If I either manually issue a CHECKPOINT or stop/restart the server (forcing a CHECKPOINT), but there are no transactions to clear, why would a VLF be cleared?
It wouldn't, but that is not what the explination is saying.
0 or More VLF files will be cleared.
As in sometimes VLF files are not cleared after a checkpoint.
Thier are several things that would casue 0 VLF files to be cleared.
February 4, 2011 at 2:17 pm
Thanks for the question Steve!
February 4, 2011 at 8:30 pm
GilaMonster (2/4/2011)
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:
Yes, Steve can't spell. I completely blame Firefox for the lack of a squiggly in the text box.
February 5, 2011 at 9:41 am
Steve Jones - SSC Editor (2/4/2011)
GilaMonster (2/4/2011)
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:
I completely blame Firefox for the lack of a squiggly in the text box.
Don't they have an add-on for that? 😀 😛
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 8, 2011 at 4:15 am
One more good question with good topic VLF 🙂
Simple logic, if this statement is True we never encounter LOG FULL in simple recovery. But we do not as it depnd on open transaction.
Cheers!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 8, 2011 at 12:07 pm
Great fundamental question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply