Help understanding why log file keeps growing

  • Hi,

    We have a database log file that keeps growing.  The database uses the simple recovery model, so my understanding would be that the log file should automatically truncate, so the log file shouldn't keep growing like this.  I can't manually shrink this log file, as it is currently using 100% of this file.  There are several other DBs on this instance, and none of their log files are using all of the space like this particular DB's log file does. We aren't currently backing up these DBs (we are taking nightly backups/snapshots of the server itself rather than the DBs, not sure I like this approach, but I have limited control over it), but with a simple recovery model, I wouldn't have thought backing up would be needed to clear the transaction log.

    Any guidance on understanding why this is happening and what we can do to shrink the log to a reasonable size would be greatly appreciated!

  • Log files don't automatically truncate.

    The reason you can't shrink it might be because there is a stuck transaction, maybe someone has some code with a begin transaction but no commit transaction.

  • It's a third party application, so unfortunately, we don't have control over the underlying T-SQL that's executing.  Is it a fair bet that if the transaction log is progressively growing within a simple recovery model and always using about 100% of that file, that there is some sort of large transaction that is causing this?  I would assume that once the transaction completes, the log file should have lots of free space that could be removed as part of a shrink.

  • Check log_reuse_wait_desc in sysdatabases. What it says?

     

    --Vadim R.

  • Jonathan AC Roberts wrote:

    Log files don't automatically truncate.

     

    Might just be a difference in lingo (MS does switch terms here an there) here but they DO automatically "truncate".  They just don't automatically shrink.  Is that what you meant?

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

  • Jeff Moden wrote:

    Jonathan AC Roberts wrote:

    Log files don't automatically truncate.

      Might just be a difference in lingo here but they DO automatically "truncate".  They just don't automatically shrink.  Is that what you meant?

    Yes Jeff, that's what I meant. But I didn't realise they did truncate, I thought they were just marked as no longer in use so allow reuse of the space.

  • Jonathan AC Roberts wrote:

    Jeff Moden wrote:

    Jonathan AC Roberts wrote:

    Log files don't automatically truncate.

      Might just be a difference in lingo here but they DO automatically "truncate".  They just don't automatically shrink.  Is that what you meant?

    Yes Jeff, that's what I meant. But I didn't realise they did truncate, I thought they were just marked as no longer in use so allow reuse of the space.

    Hmmm... put that way, I might be the one confused by terms.

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

  • Heh... I was pretty sure that I remembered the terminology correctly but your reply made me doubt it.  Then I went to find where I read the term and had a heck of a time finding it.  I must've dropped by "Google Qual Card" somewhere.

    Anyway, I did find it and truncation actually means the process that you replied with.  Here's the quote and the link for that follows.

    Transaction log truncation

    Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the alotted space. Several factors can delay log truncation, so monitoring log size matters. Some operations can be minimally logged to reduce their impact on transaction log size.

    Log truncation deletes inactive virtual log files (VLFs) from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space allocated to physical log files.

    https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017

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

  • That's exactly what Jeff is saying, it's really an issue about poor word usage. Microsoft's use of "truncate" for the transaction logs means marking the inactive portions of the log as being able to be reused. And using the term truncate has caused so much confusion.  So the log can automatically truncate but there is no automatic shrink of the physical size of the log.

    The Transaction Log (SQL Server)

     

    Sue

     

  • Sorry...posted at the same time. But all of it goes to show how stupid it was to use the term truncate.

  • Yes, I think I read the question it too quickly and subconsciously thought the OP meant shrink. I can see now truncate is an official MS term for it but I had never used that term for log file truncation before. It's not something that crops up in every day conversation, unlike truncating tables.

  • Sue_H wrote:

    Sorry...posted at the same time. But all of it goes to show how stupid it was to use the term truncate.

    Don't be sorry! 😀  Confirmation of the meaning of the term and seeing that someone else thinks it's stupid that they picked the term to mean such a thing is always welcome!

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

  • Jonathan AC Roberts wrote:

    Yes, I think I read the question it too quickly and subconsciously thought the OP meant shrink. I can see now truncate is an official MS term for it but I had never used that term for log file truncation before. It's not something that crops up in every day conversation, unlike truncating tables.

    That's definitely a part of why I think using the term TRUNCATE in association with removing VLF entries is absolutely stupid.  Same word, totally different meanings.

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

  • I think my initial confusion was the OP said "log file should automatically truncate" It's not the log file that truncates but the transaction log that truncates which is just a virtual file.

Viewing 14 posts - 1 through 13 (of 13 total)

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