Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clearing the Transaction Log


Clearing the Transaction Log

Author
Message
Steve Cullen
Steve Cullen
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 1226
Great question. This is what the QOD is all about. A great explanation of the answer as well.

Converting oxygen into carbon dioxide, since 1955.


UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Thanks for the great question! I am learning so much more from these questions and their explanations.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
Got it wrong. Angry

Thanks for a good explanation. :-)
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
I got lucky... I got it right. Partly from discounting some, and the rest by what didn't make sense.
I also agree... excellent explaination of the answer.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
This is a great question, thank you Paul. It would have been very difficult to answer it correctly, but fortunately for me, incorrect options were clearly incorrect, and therefore it was not too difficult to not answer the question incorrectly:

B. VLFs are physically deleted? No, VLF is, well, VLF, a fragment
C. VLFs are overwritten with zeroes? No, in Windows nothing is overwritten with zeroes as there is no need to do that. Even formatting the drive does not actually overwrite data with zeroes.

With above out of the way, All of the above and Answers A and C have to go too.

This leaves only A and D to seriously consider, and while this is true that the only way to shrink the file is to first have it inactive VLFs (Status = 0 when running dbcc loginfo()) bunched in its tail, the log file shrinks when dbcc shrinkfile is called.

Thus, answer A is the only answer which is not incorrect.

Oleg
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Oleg Netchaev (6/2/2010)
C. VLFs are overwritten with zeroes? No, in Windows nothing is overwritten with zeroes as there is no need to do that.

Hey Oleg,

Log files are initialised with zeroes (there's some complexity around that - see Paul's blog and Jonathan Kehayias' entries:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/13/does-the-tempdb-log-file-get-zero-initialized-at-startup.aspx

Windows uses zero-initialised memory pages too (see Demand Zero Pages).

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
Paul White NZ (6/2/2010)
Oleg Netchaev (6/2/2010)
C. VLFs are overwritten with zeroes? No, in Windows nothing is overwritten with zeroes as there is no need to do that.

Hey Oleg,

Log files are initialised with zeroes (there's some complexity around that - see Paul's blog and Jonathan Kehayias' entries:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/13/does-the-tempdb-log-file-get-zero-initialized-at-startup.aspx

Windows uses zero-initialised memory pages too (see Demand Zero Pages).

Paul

Thank you Paul, it is good to know. Usually, Windows does not need to write any zeroes (when you delete files or format drive, just the records from allocation table are deleted and this is good enough), but initializing a log file with zeroes does make a lot of sense.

Since the question was about clearing existing log file, I think that my assumption about not overwriting existing inactive fragments with zeroes is still OK. If not then I guess I got lucky while dismissing options which I perceived as incorrect. In any case, I got my 2 points out of the question posted by Paul Randall, and to get those is never a trivial task.

Oleg
ziangij
ziangij
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2910 Visits: 374
thanks Paul :-) of all the QOTD, i find your questions the most difficult...:-)
Tush
Tush
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 423
I "Lost" 2 points Angry but "gained" a very good knowledge :-)

Thanks & Regards
-Tushar :-)
MCP,MCDBA(SQL 2000),MCTS(SQL 2005,2008),MCITP(SQL 2005,2008)

"Most Good Experience Comes From Judgement,
Most Judgement Comes From Bad Experience"
_____________________________________________
jrandolph
jrandolph
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 53
Another good question which made reading the relevant tech pages to answer it interesting and well worth the time.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search