Kill long running transactions

  • So, I found a long running transaction that ran for 7 + hours. I killed the spid but it’s rolling back for more than 20 hours now. Is there any other way to Kill this? I’m not worried about data loss

  • You can bounce the server. Sometimes that works. Other times it stops the rollback which then has to start up again, from scratch.

    Look to see if there is blocking on the rollback.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Bouncing the server put the database in recovery state and can’t be accessible.

  • SQL_Surfer - Thursday, February 8, 2018 6:34 PM

    Bouncing the server put the database in recovery state and can’t be accessible.

    That's usually what happens when the rollback is starting back up again. You should see messages about it in the Error log, percentage completed, phases, that type of thing.

    Sue

  • agree a service 1bounce sometimes does work; when it does not the DB goes to recovery mode and you wait again, but usually this is no longer the 20 hours elapsed you mention.

  • If you kill active transaction, MSSQL does need rollback to guarantee the data consistence. I think it's lesson. Whenever we kill a session, first of all, we should be very carefully check the complete percentage in sys.dm_exec_requestes and evaluate the rollback time.. Sometimes, maybe it just needs 2 more minutes to finish, but 20 hours to rollback.

    GASQL.com - Focus on Database and Cloud

  • SQL_Surfer - Thursday, February 8, 2018 6:34 PM

    Bouncing the server put the database in recovery state and can’t be accessible.

    yep. It's rolling back.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Alexander Zhang - Thursday, February 8, 2018 11:06 PM

    Whenever we kill a session, first of all, we should be very carefully check the complete percentage in sys.dm_exec_requestes and evaluate the rollback time..

    The complete percentage is only for backups and operations like that. Normal queries don't get a complete percentage, and hence the time to complete can't be evaluated.

    percent_completerealPercentage of work completed for the following commands:

    ALTER INDEX REORGANIZE
    AUTO_SHRINK option with ALTER DATABASE
    BACKUP DATABASE
    DBCC CHECKDB
    DBCC CHECKFILEGROUP
    DBCC CHECKTABLE
    DBCC INDEXDEFRAG
    DBCC SHRINKDATABASE
    DBCC SHRINKFILE
    RECOVERY
    RESTORE DATABASE
    ROLLBACK
    TDE ENCRYPTION

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Friday, February 9, 2018 8:38 AM

    Alexander Zhang - Thursday, February 8, 2018 11:06 PM

    Whenever we kill a session, first of all, we should be very carefully check the complete percentage in sys.dm_exec_requestes and evaluate the rollback time..

    The complete percentage is only for backups and operations like that. Normal queries don't get a complete percentage, and hence the time to complete can't be evaluated.

    percent_completerealPercentage of work completed for the following commands:

    ALTER INDEX REORGANIZE
    AUTO_SHRINK option with ALTER DATABASE
    BACKUP DATABASE
    DBCC CHECKDB
    DBCC CHECKFILEGROUP
    DBCC CHECKTABLE
    DBCC INDEXDEFRAG
    DBCC SHRINKDATABASE
    DBCC SHRINKFILE
    RECOVERY
    RESTORE DATABASE
    ROLLBACK
    TDE ENCRYPTION

    Yes, you are correct. The complete_percent has its big limitation. And I agree it's very hard to evaluate the rollback time. My point is, we need to do it if possible. For example, if Insert/Update operations with CreateTime/InsertTime/LastUpdateTime info, we can check the row count with NOLOCK. I agree, it's probably not so helpful in many cases. But in some cases, it might prevent us from doing some bad decisions.

    GASQL.com - Focus on Database and Cloud

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

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