Shrinking Transaction Log File "the right way"

  • Thanks for the explanation Gail! That was what I was looking for.

  • Ahh... the old 'It's on the Interweb - It must be true' - Doh! I didn't get to read the article but, I don't think I needed to. Clearly the comments from those who I use as my research yard-sticks bear out my opinion of internet columnists Like google - Look hard enough and you'll find something somewhere to support your point of view - - even if it is wrong.

    Keep up the good standard and guide those who are obviously in error.

    CodeOn:-P

  • danielfountain (10/16/2012)


    Apart from the other stuff that has been said i would also suggest that from what i have seen the number one cause of the log file becoming big is that someone has left SQL on default settings and they dont know what there doing.

    Dan

    This is exactly what was happening to the dbs that I am now managing. I even got into an argument with the prior dba about shrinking the logs during my interview. I said don't do it unless absolutely necessary, it's a bad practice. Size them right, get the right amount of storage provisioned and then grow them right and you should be fine. They argued that the shrinks it were necessary because the logs were growing to big for the drive and getting "all filled up". Turns out that the dbs were all in FULL, with no T-Log backups and T-logs shrinks every 6 hours! And the growth settings were awful. These dbs are mostly bulk loading DW type databases that should have been in SIMPLE to begin with. Not sure how this department got by for so long with those guys.

    Kimberly Tripp's articles on Tlogs and VLFs should be required reading.

    Side note: I just assumed that all articles on here would be peer reviewed prior to publishing. Is that not true?

    MWise

  • MWise (10/17/2012)


    Side note: I just assumed that all articles on here would be peer reviewed prior to publishing. Is that not true?

    Steve edits them, don't know what his process is. Authors who want a proper tech review prior to submitting the article should arrange that themselves

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/18/2012)


    Steve edits them, don't know what his process is.

    I'm guessing he didn't edit that one, it was way off base πŸ˜‰

    The last time someone was that wrong, Adolf Hitler marched up the "Avenue des Champs-Γ‰lysΓ©es" and shouted "Hi hunny i'm home" πŸ˜€

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • Perry Whittle (10/18/2012)


    GilaMonster (10/18/2012)


    Steve edits them, don't know what his process is.

    I'm guessing he didn't edit that one, it was way off base πŸ˜‰

    No need to guess.

    Editor: Our apologies. Today's article was inadvertently published without a complete edit. We have removed this content as it contains a number of technical errors and misleading information.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gila, thank you for your constrictive criticism. Unfortunately, the rest of the comments made by other participants were just useless buzz, worse than they claimed my article to be. Nice, old, lame teenage-kind of attitude. Keep it this way! But elsewhere from now one, please.

    So, Gila (the rest, please, ignore), now after so much bashing, yet inspired by the only constructive comment of yours, I admit I might be under the influence of, as you referred to it β€œa prevalent and very irritating myth”. Would very much appreciate if you "demythologize" it. What is this, if not broken LSN? (please, see attached file)

  • What am I supposed to be looking at? Lots of VLF data, but I see nothing that tells me anything useful.

    As I already said, if you check BoL, truncateonly is an option that is ONLY valid for shrinking data files, it is completely ignored when shrinking a log. Hence, since it is not valid for shrinking log files, there is no grounds at all for assuming it does anything special when shrinking a log file as the option is ignored.

    It is trivially easy to show that it does not break the log chain. Take a DB in full recovery. Take a log backup. Shrink the log. Take another log backup. If the log chain was broken, the second log backup would fail. It does not.

    As for the rest of the criticism, it's true, even though it may be blunt. There are so many technical errors in the article that it is harmful to anyone reading. I strongly suggest you consider getting a peer review/tech edit if you intend to re-submit it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • OK, thank you, Gila.

  • distas (10/25/2012)


    Unfortunately, the rest of the comments made by other participants were just useless buzz, worse than they claimed my article to be. Nice, old, lame teenage-kind of attitude. Keep it this way! But elsewhere from now one, please.

    ????

    Err, read the doc and for the 1000th time DBCC SHRINKFILE on a t-log with TRUNCATEONLY is not a valid command!!!!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • distas (10/25/2012)


    Gila, thank you for your constrictive criticism. Unfortunately, the rest of the comments made by other participants were just useless buzz, worse than they claimed my article to be.

    I disagree - your article was much worse than our comments.

    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 11 posts - 16 through 26 (of 26 total)

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