SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Paul Randal / Clearing the Transaction Log / Latest Posts

RE: Clearing the Transaction Log

rahulddeshmukh — Thu, 10 Jun 2010 04:15:58 GMT

good Q on Transaction Log Physical Architecture and Virtual Log Files

RE: Clearing the Transaction Log

jrandolph — Sun, 06 Jun 2010 10:05:18 GMT

Another good question which made reading the relevant tech pages to answer it interesting and well worth the time.

RE: Clearing the Transaction Log

Tush — Thu, 03 Jun 2010 03:45:05 GMT

I "Lost" 2 points :angry: but "gained" a very good knowledge :-)

RE: Clearing the Transaction Log

ziangij — Wed, 02 Jun 2010 21:25:28 GMT

thanks Paul :-) of all the QOTD, i find your questions the most difficult...:-)

RE: Clearing the Transaction Log

Oleg Netchaev — Wed, 02 Jun 2010 16:06:58 GMT

[quote][b]Paul White NZ (6/2/2010)[/b][hr][quote][b]Oleg Netchaev (6/2/2010)[/b][hr]C. VLFs are overwritten with zeroes? No, in Windows nothing is overwritten with zeroes as there is no need to do that.[/quote]Hey Oleg,Log files are initialised with zeroes (there's some complexity around that - see Paul's blog and Jonathan Kehayias' entries:[url]http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/13/does-the-tempdb-log-file-get-zero-initialized-at-startup.aspx[/url]Windows uses zero-initialised memory pages too (see Demand Zero Pages).Paul[/quote]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 [b]existing[/b] 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

RE: Clearing the Transaction Log

Paul White — Wed, 02 Jun 2010 15:05:26 GMT

[quote][b]Oleg Netchaev (6/2/2010)[/b][hr]C. VLFs are overwritten with zeroes? No, in Windows nothing is overwritten with zeroes as there is no need to do that.[/quote]Hey Oleg,Log files are initialised with zeroes (there's some complexity around that - see Paul's blog and Jonathan Kehayias' entries:[url]http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/13/does-the-tempdb-log-file-get-zero-initialized-at-startup.aspx[/url]Windows uses zero-initialised memory pages too (see Demand Zero Pages).Paul

RE: Clearing the Transaction Log

Oleg Netchaev — Wed, 02 Jun 2010 14:11:48 GMT

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 fragmentC. 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, [b]All of the above[/b] and [b]Answers A and C[/b] have to go too.This leaves only [b]A[/b] and [b]D[/b] 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 [b]A[/b] is the only answer which is not incorrect.Oleg

RE: Clearing the Transaction Log

WayneS — Wed, 02 Jun 2010 11:47:25 GMT

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.

RE: Clearing the Transaction Log

Daniel Bowlin — Wed, 02 Jun 2010 11:18:26 GMT

Got it wrong. :angry:Thanks for a good explanation. :-)

RE: Clearing the Transaction Log

UMG Developer — Tue, 01 Jun 2010 20:23:23 GMT

Thanks for the great question! I am learning so much more from these questions and their explanations.

RE: Clearing the Transaction Log

Steve Cullen — Tue, 01 Jun 2010 12:38:39 GMT

Great question. This is what the QOD is all about. A great explanation of the answer as well.

RE: Clearing the Transaction Log

rmechaber — Tue, 01 Jun 2010 10:40:42 GMT

Paul, thanks to a firmly worded and clearly written article of yours I'd read a while ago [url=http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx]http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx[/url], I got this one right.I agree with the comment someone posted on that web page above: it would be helpful if BOL adopted more explicit language about this issue.Yours,Rich M.

RE: Clearing the Transaction Log

SQLRNNR — Tue, 01 Jun 2010 10:30:50 GMT

Most excellent question.

RE: Clearing the Transaction Log

webrunner — Tue, 01 Jun 2010 09:22:00 GMT

[quote][b]Tom Garth (6/1/2010)[/b][hr]I think I almost got it right - :-)This was a great question that made me do a little reading and learn something about a little understood part of SQL Server.I do believe that the other 16% who got it right, deserve more than just 2 points.Thanks,[i][font="Arial"]"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers[/font][/i][/quote]Speaking of which, you deserve 2 points for your tag line! :-)Thanks,webrunner

RE: Clearing the Transaction Log

webrunner — Tue, 01 Jun 2010 09:19:30 GMT

Ouch! Got that one wrong.Great question.Thanks,webrunner

RE: Clearing the Transaction Log

Tom Garth — Tue, 01 Jun 2010 08:56:35 GMT

I think I almost got it right - :-)This was a great question that made me do a little reading and learn something about a little understood part of SQL Server.I do believe that the other 16% who got it right, deserve more than just 2 points.Thanks,

RE: Clearing the Transaction Log

sharath.chalamgari — Tue, 01 Jun 2010 05:41:26 GMT

Today i learned about the Transaction Logs and virtual log files

RE: Clearing the Transaction Log

malleswarareddy_m — Tue, 01 Jun 2010 05:25:55 GMT

Today i learned new topic about VLF.

RE: Clearing the Transaction Log

Lynn Pettis — Tue, 01 Jun 2010 01:20:14 GMT

Not too difficult for someone who hasn't read his blog as well. I just pay close attention to others more versed in log files that also post here on SSC.

RE: Clearing the Transaction Log

Paul White — Tue, 01 Jun 2010 01:15:33 GMT

Ha! Easy for anyone who reads your excellent blog :-)Great question.

Clearing the Transaction Log

Paul Randal — Mon, 31 May 2010 21:15:17 GMT

Comments posted to this topic are about the item [B]Clearing the Transaction Log[/B]