Post reply

SQL Log File Too Big

  • February 16, 2022 at 12:00 am

    #3980794

    Comments posted to this topic are about the item SQL Log File Too Big

  • February 16, 2022 at 10:55 am

    #3984289

    Nice summary, Ginger, and good analogy about the grocery bag. Helps to bring home the point that shrinking the log file might be a really bad idea.

    Personally, I think the term "log file" is a really poor name for, well, the log file. It conjures up the idea of something that can be just chucked away anytime I need to make a bit of space. "Transaction integrity file" would be better, or maybe just "integrity file" for those in a hurry. Might make some people think twice.

    My ah! moment with understanding the why of logging was when I realized that:

    1. The database file (.mdf/.ndf) just contains the data, not what is being done to it.
    2. The log file (.ldf) contains what has recently happened to the data, or what I want to happen to the data, if I decide to commit the current transaction.
    3. Everything that happens to the data gets written to the log file first, before anything is updated in the database itself. That way, should a disaster occur, we have all the information necessary to return the database to an up-to-date and consistent state -- provided we haven't deleted it, of course.

    Once again, good article, and a good prelude for those who want to delve into the more detailed treatments.

    MarkD

    • This reply was modified 2 years, 7 months ago by  Mark Dalley.
    • This reply was modified 2 years, 7 months ago by  Mark Dalley. Reason: Improve layout

  • February 16, 2022 at 4:28 pm

    #3984429

    I know it was mentioned in the article but it needs to be stressed a bit.  One of the biggest reasons for massive log file sizes IS index maintenance and, within that category, the biggest culprit is the use of REORGANIZE, which doesn't actually work the way most people think it works and isn't actually the tame little log file kitty that everyone seems to advertise it as.

    REORGANIZE is an insidious monster that you should actually be very careful about using.  It's so misunderstood that people haven't realized that it's actually the reason why people think that Random GUIDs fragment a lot and the tests that supposed "prove" that fragmentation problem are actually incorrect.

    While we're at it, the "Best Practice" index maintenance of using REORGANIZE between 5/10 and 30% and only doing REBUILDs is not and was never meant to be a "Best Practice" and, because of the use of REORGANIZE, is actually a WORST practice in most cases.

    Here's a video on a lot of that.  It uses Random GUIDs to demonstrate the problems but applies to a whole lot of other indexes.

    https://www.youtube.com/watch?v=rvZwMNJxqVo

    And, yep... charts and graphs having to do with the related overuse of the log file is included in that.

    '

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 17, 2022 at 5:08 am

    #3984733

    This reply has been reported for inappropriate content.

    I always prefer to visit a very informative website,  this website is like that. Thank you for providing this information.

    Thanks,

    best smm panel indian

     

Viewing 4 posts - 1 through 3 (of 3 total)

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