none
Active Transaction state of log_Reuse_Wait_Dec

Answers

  • Now you might be in bigger trouble than before. Killing a transaction does not end it immediately, it goes through a rollback. If there is a lot of undo to do, it may take much much longer than it would take to finish the transaction regularly after removing the source of slowness (blocking lock?).

    Even worse, rollback is also logged, meaning your log will grow further without clearing, and may threaten to fill your disk. Once your log disk is filled, every change in database stops. It effectively becomes "read-only" database. In order to prevent that, you might add another log file on the disk with enough space, for log to spill there and hopefully finish the rollback without stopping your database and service.

    Use sp_helpfile to examine where is the log file and check the disk space. Use DBCC LOGINFO to see how many active VLF's there are (Status=2). During rollback take log backups more often than usual, for example every few minutes, not to end-up with one big log backup that also takes a long time. By each log backup you give log a chance to clear some active VLF's but that will happen only when your transaction rollback is finished.

    Read my blog, it has a lot of info on that subject.

    Install sp_WhoIsActive procedure and use it to see blocks, waits, and status of your rollback.

    Good luck! Post how it went.


    • Edited by Vedran Kesegic Friday, November 29, 2013 10:16 PM
    • Marked as answer by '''HuuM''' Sunday, December 01, 2013 12:42 AM
    Friday, November 29, 2013 10:12 PM

All replies

  • try running dbcc opentran for that database.
    Then sp_who2.
    Friday, November 29, 2013 5:01 PM
  • More information on this

    Transaction information for database 'abc'.

    Oldest active transaction:
        SPID (server process ID): 168
        UID (user ID) : -1
        Name          : INSERT
        LSN           : (3763883:6092:2)
        Start time    : Nov 29 2013  6:27:45:190AM
        SID           : 0x01050000000000051500000072f8f8bb7b42368b94581cd33c080000

    Replicated Transaction Information:
            Oldest distributed LSN     : (3790857:21702:722)
            Oldest non-distributed LSN : (3763883:6092:2)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    k

    Friday, November 29, 2013 5:15 PM
  • More information on this

    Transaction information for database 'abc'.

    Oldest active transaction:
        SPID (server process ID): 168
        UID (user ID) : -1
        Name          : INSERT
        LSN           : (3763883:6092:2)
        Start time    : Nov 29 2013  6:27:45:190AM
        SID           : 0x01050000000000051500000072f8f8bb7b42368b94581cd33c080000

    Replicated Transaction Information:
            Oldest distributed LSN     : (3790857:21702:722)
            Oldest non-distributed LSN : (3763883:6092:2)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    k

    Use DBCC Inputbuffer(SPID) to get query behind this SPID.Now also check

    select * from sys.sysprocesses where blocked <> 0

    to see any blocking transaction.Let this operation complete and then you can take Trn log backup to truncate logs.May be you can shrink logs if you require space.

    Is this transaction running for long time ,see what is blocking this process SPID from sp_who2


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, November 29, 2013 5:21 PM
  • this returned 0 results

    k

    Friday, November 29, 2013 5:23 PM
  • this returned 0 results

    k

    DBCC INPUTBUFFER(168) returned 0 result.If this is an active transaction it wont be possible.Whats is output of below query

    select * from sys.sysprocesses where spid=168--this query has SQL_handle col put that value in below DMV to see th query

    select * from sys.dm_exec_sql_text(sql_handle)

    Please post result here

    run dbcc opentran(db_name) to check if command has completed


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, November 29, 2013 5:32 PM
  • The log_reuse_wait_desc column tells you what is preventing log truncation. This was the reason when SQL Server last attempted to truncate the log, not the "reason as of now."  If the database is in FULL recovery model, run a LOG backup and immediately query sys.databases to see what the new value is. If you still see ACTIVE_TRANSACTION, follow the advice of Shanky_621 to find out what that active transaction is and see if it is a critical one. Look for the application name, hostname of the machine running it and how long has it been running for. Your decision on whether or not to kill it will depend on how critical the transaction is.

    Edwin Sarmiento SQL Server MVP Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course

    Friday, November 29, 2013 5:34 PM
  • No transaction is not completed,

    so as per your DMV query here is the result


    k

    Friday, November 29, 2013 8:24 PM
  • Remaining part of result set


    k

    Friday, November 29, 2013 8:26 PM
  • i am trying to kill the transaction but i am getting this

    SPID 168: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.


    k

    Friday, November 29, 2013 8:29 PM
  • Now you might be in bigger trouble than before. Killing a transaction does not end it immediately, it goes through a rollback. If there is a lot of undo to do, it may take much much longer than it would take to finish the transaction regularly after removing the source of slowness (blocking lock?).

    Even worse, rollback is also logged, meaning your log will grow further without clearing, and may threaten to fill your disk. Once your log disk is filled, every change in database stops. It effectively becomes "read-only" database. In order to prevent that, you might add another log file on the disk with enough space, for log to spill there and hopefully finish the rollback without stopping your database and service.

    Use sp_helpfile to examine where is the log file and check the disk space. Use DBCC LOGINFO to see how many active VLF's there are (Status=2). During rollback take log backups more often than usual, for example every few minutes, not to end-up with one big log backup that also takes a long time. By each log backup you give log a chance to clear some active VLF's but that will happen only when your transaction rollback is finished.

    Read my blog, it has a lot of info on that subject.

    Install sp_WhoIsActive procedure and use it to see blocks, waits, and status of your rollback.

    Good luck! Post how it went.


    • Edited by Vedran Kesegic Friday, November 29, 2013 10:16 PM
    • Marked as answer by '''HuuM''' Sunday, December 01, 2013 12:42 AM
    Friday, November 29, 2013 10:12 PM
  • i am trying to kill the transaction but i am getting this

    SPID 168: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.


    k

    First of all you should not have killed the transaction specially the insert one.You have to rerun it because table any how has to be updated.You should have waited.

    How much data was being inserted ,how big was transaction .See if there is any process blocking the rollback.See if log files are not out of space.I would like you to have patience and le it rollback completely


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, November 30, 2013 5:38 AM
  • To me it looks line you have replication configured and the log reader process has stopped, for some reason.

    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, December 01, 2013 2:58 PM