Clearing the Transaction Log

Question

Post reply

Clearing the Transaction Log

Paul Randal

One Orange Chip

Points: 29544
More actions
May 31, 2010 at 9:15 pm

#233158

Comments posted to this topic are about the item Clearing the Transaction Log
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Ha! Easy for anyone who reads your excellent blog 🙂

Great question.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

Today i learned new topic about VLF.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 4

Today i learned about the Transaction Logs and virtual log files

Tom Garth SSCertifiable Points: 6193 More actions · Answer 5

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,

Tom Garth
Vertical Solutions[/url]

"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

webrunner SSC-Dedicated Points: 31862 More actions · Answer 6

Ouch! Got that one wrong.

Great question.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31862 More actions · Answer 7

Tom Garth (6/1/2010)
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,
[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]

Speaking of which, you deserve 2 points for your tag line! 🙂

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Most excellent 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

Rich Mechaber SSChampion Points: 11130 More actions · Answer 9

Paul, thanks to a firmly worded and clearly written article of yours I'd read a while ago http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx, 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.

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 10

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 SSChampion Points: 13482 More actions · Answer 11

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 12

Got it wrong. :angry:

Thanks for a good explanation. 🙂

WayneS SSC Guru Points: 95460 More actions · Answer 13

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 14

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