Clearing the Transaction Log

  • 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

  • Ha! Easy for anyone who reads your excellent blog 🙂

    Great question.

  • 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.

  • Today i learned new topic about VLF.

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

  • Today i learned about the Transaction Logs and virtual log files

  • 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
  • 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

  • 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

  • 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

  • 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.

  • 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.
  • Thanks for the great question! I am learning so much more from these questions and their explanations.

  • Got it wrong. :angry:

    Thanks for a good explanation. 🙂

  • 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

  • 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

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

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