Txn log file is full. Error 9002

  • bobrooney.81

    SSC Enthusiast

    Points: 199

    Hi All,

    Here is the scenario on of the test servers. The transaction log is full. The database is in AG. Its SQL Server 2016 enterprise edition instance.

    SQL Server Alert System: 'Error 9002 Log File Full' occurred on \\Server1

    followed steps

    =============

    > The ldf file growth was limited to 100GB. There is lot of disk space available on drive though.

    > DBCC SQLPERF(LOGSPACE); -- shows 100% usage

    > Checked the recovery model and log reuse description from DMVs. it was Full and waiting ACTIVE transaction.

    > dbcc loginfo --shows all VLFS are active i.e. status =2

    > Taken consecutive 3 log backup and tried to check if there VLF status will change. nothing changed.

    > I tried to increased the log file size using TSQL to 110GB. SQL still throws me an error message repeatedly

    Msg 9002, Level 17, State 4, Line 3

    The transaction log for database 'db1' is full due to 'ACTIVE_TRANSACTION'.

    > I see multiple block chains as well.

    Did anyone come across this situation? How to solve it without KILLing any sessions or restarting SQL Server ?

    -Bob

  • John Mitchell-245523

    SSC Guru

    Points: 148669

    Not much you can do, I'm afraid.  You either wait for the transaction to finish (in which case you may need to add more log space) or abort it (in which case it may take just as long or longer to roll back as it would have taken to finish).  Make sure you identify the offending query and tune it so that the same thing doesn't happen next time it runs.  What is the query doing - is it blocked by something else, waiting for user input, or just a complicated query?

    John

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    run a quick dbcc opentran against that database - then figure out what that spid is doing

    it might not be as bad as john says. it could be a tiny transaction that is uncommitted and needs killing

    have you issued a checkpoint command ?

    this makes me shudder, (don't do this unless under adult supervision) - I've had to occasionally swap the DB to simple mode, checkpoint it,  shrink the log (purely for if we have to restore it somewhere else) then put it back in full mode then kick off a full backup

    MVDBA

  • Lynn Pettis

    SSC Guru

    Points: 442283

    MVDBA (Mike Vessey) wrote:

    run a quick dbcc opentran against that database - then figure out what that spid is doing

    it might not be as bad as john says. it could be a tiny transaction that is uncommitted and needs killing

    have you issued a checkpoint command ?

    this makes me shudder, (don't do this unless under adult supervision) - I've had to occasionally swap the DB to simple mode, checkpoint it,  shrink the log (purely for if we have to restore it somewhere else) then put it back in full mode then kick off a full backup

    To start a new log chain you can just do a differential backup instead of a full backup after putting the database back in the full recovery model.

     

  • Jeffrey Williams

    SSC Guru

    Points: 88335

    Because the database is part of an AG - make sure you check the send and redo queues.  To see that - view the availability group dashboard by right-clicking on the availability group and selecting show dashboard.  When that opens - add the send queue and redo queue.

    If either of these are backed up - then the transaction log cannot be cleared even with a transaction log backup.

    Also, verify the space on the secondaries for the transaction log has at least as much space available as the primary node.  If you have run out of space on one of the secondaries - then the redo queue cannot complete its operation causing the primary to either fill the drive or reach the max size defined (it seems you have the growth limited on the log).

    One final note - when a database is part of an AG then the status from DBCC LOGINFO will always be 2.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • bobrooney.81

    SSC Enthusiast

    Points: 199

    John Mitchell-245523 wrote:

    Not much you can do, I'm afraid.  You either wait for the transaction to finish (in which case you may need to add more log space) or abort it (in which case it may take just as long or longer to roll back as it would have taken to finish).  Make sure you identify the offending query and tune it so that the same thing doesn't happen next time it runs.  What is the query doing - is it blocked by something else, waiting for user input, or just a complicated query?

    John

    Hi John,

    I endup killing that particular spid since there is no space left, it can't move further.

    Even adding log space i.e. Alter database cmd failed since there is no space in the log file so had to kill that process and increase the log file.

     

  • Thomas Rushton

    SSC-Insane

    Points: 22645

    MVDBA (Mike Vessey) wrote:

    this makes me shudder, (don't do this unless under adult supervision) - I've had to occasionally swap the DB to simple mode, checkpoint it,  shrink the log (purely for if we have to restore it somewhere else) then put it back in full mode then kick off a full backup

    If you do go down that route (and, believe me, it's a last resort sort of thing), then you'll have to take the DB out of the AG first...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

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

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