Post reply

Ways to determine what increased the log file.

  • January 21, 2018 at 9:34 pm

    #325505

    Hi All,
    Need some assistance on how i could find which query or process increased the size of transaction log file on production database.
    Regards,
    Adil

  • January 22, 2018 at 1:14 am

    #1976678

    Any inputs on the same would be highly appreciated

  • January 22, 2018 at 2:16 am

    #1976683

    Not much you can do after the fact.  Look at the time that the log last grew (it will usually be the last modify time of the physical file).  Where there any jobs or processes running at that time?  Index maintenance jobs are often the culprit, especially when growth occurs over the weekend.

    John

  • January 22, 2018 at 2:37 am

    #1976687

    John Mitchell-245523 - Monday, January 22, 2018 2:16 AM

    Not much you can do after the fact.  Look at the time that the log last grew (it will usually be the last modify time of the physical file).  Where there any jobs or processes running at that time?  Index maintenance jobs are often the culprit, especially when growth occurs over the weekend.

    John

    Thanks for your reply , Yeah the Index Optimize job was running at around the time when this incident happened; however we are not sure if this particular job is the main culprit behind it.

  • January 22, 2018 at 2:48 am

    #1976688

    Does the Index Optimize job keep a record of what indexes it's rebuilt?  If it does, there's your smoking gun.  If it doesn't, content yourself with having found the most likely cause of the growth, and move on.

    John

  • January 22, 2018 at 2:58 am

    #1976691

    John Mitchell-245523 - Monday, January 22, 2018 2:48 AM

    Does the Index Optimize job keep a record of what indexes it's rebuilt?  If it does, there's your smoking gun.  If it doesn't, content yourself with having found the most likely cause of the growth, and move on.

    John

    Ok Sir,

    The Job doesn't keeps any record of the rebuilt indexes; since it was an Holiday Sunday, we missed out the details of the Indexes rebuilt as with usual days ; it can be find out by executing a simple monitoring query.

    Anyways, thanks for the assistance, will look forward for more inputs on this space so as to how this situation can be tackled more effeciently

    Regards,
    Adil

  • January 22, 2018 at 6:35 am

    #1976710

    adilahmed1989 - Monday, January 22, 2018 2:37 AM

    John Mitchell-245523 - Monday, January 22, 2018 2:16 AM

    Not much you can do after the fact.  Look at the time that the log last grew (it will usually be the last modify time of the physical file).  Where there any jobs or processes running at that time?  Index maintenance jobs are often the culprit, especially when growth occurs over the weekend.

    John

    Thanks for your reply , Yeah the Index Optimize job was running at around the time when this incident happened; however we are not sure if this particular job is the main culprit behind it.

    Been through this before.  Typical Index Maintenance routines can and usually will take a HUGE toll on the log file.  Index REBUILDs are part of it but so is REORGANIZE.

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

  • January 26, 2018 at 12:05 pm

    #1977579

    Try this article by Jason Brimhall. I have an automated job based on Jason's code but it's mutilated beyond recognition. I cannot share it here as it infringes his rights.

    Log Growing Pains

Viewing 8 posts - 1 through 7 (of 7 total)

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