Can't Kill SPID "Transaction Rollback in Progress"

  • I have an uncommitted statement in perptual rollback mode in my database. When I try to kill the SPID associated with this statement, I get the "transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." error. This uncommitted statement is causing users to not be able to view the DB's table, view, and procedure trees. How do I stop this SPID?

  • guerillaunit (8/31/2012)


    I have an uncommitted statement in perptual rollback mode in my database. When I try to kill the SPID associated with this statement, I get the "transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." error. This uncommitted statement is causing users to not be able to view the DB's table, view, and procedure trees. How do I stop this SPID?

    I've never had a process I've killed report back an actual estimated time.

    If it's rolling back, you'll just have to wait for it to finish.

    If it is hung up while trying to roll back, then you'll need to restart the service (or possibly the server) to clear the process. This can happen for example if you use xp_cmdshell to run an executable that prompts for some interactive input. The process will appear to hang at that point as it is waiting for input, and when you kill it the rollback will hang as well as it is waiting for a response from that executable which is still waiting for input.

  • guerillaunit (8/31/2012)


    I have an uncommitted statement in perptual rollback mode in my database. When I try to kill the SPID associated with this statement, I get the "transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." error. This uncommitted statement is causing users to not be able to view the DB's table, view, and procedure trees. How do I stop this SPID?

    what's the reason for rollback? do you used kill spid? or occurs fails on server/database?


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • The rollback has been going on for a day now so I assume it is stuck. Any watchouts that need to be done when restarting the server? Also, how long does it usually take?

  • guerillaunit (8/31/2012)


    The rollback has been going on for a day now so I assume it is stuck. Any watchouts that need to be done when restarting the server? Also, how long does it usually take?

    Whenever I have seen this condition, I have never seen it complete on its own.

    I suggest restarting SQL Server; its should not be necessary to reboot the server.

    Depending the actual state of the rollback, it may take SQL Server longer than usual to recover the database.

  • Thanks Michael. I was hoping to avoid the restart, but it looks like there are no alternatives. I've never restarted a server before. Any things to watch out for other than to avoid doing it when the DBs are in high demand?

  • guerillaunit (8/31/2012)


    Thanks Michael. I was hoping to avoid the restart, but it looks like there are no alternatives. I've never restarted a server before. Any things to watch out for other than to avoid doing it when the DBs are in high demand?

    If the transaction is actually rolling back and not hung up, then you won't gain anything from restarting the server. When it comes back up, it will just continue rolling back the transaction.

    I've had processes take as long to roll back as it had been running before being killed.

    Can you tell us more about what the process is, how long it's been running, and why it is rolling back?

  • sestell1 (8/31/2012)


    guerillaunit (8/31/2012)


    Thanks Michael. I was hoping to avoid the restart, but it looks like there are no alternatives. I've never restarted a server before. Any things to watch out for other than to avoid doing it when the DBs are in high demand?

    If the transaction is actually rolling back and not hung up, then you won't gain anything from restarting the server. When it comes back up, it will just continue rolling back the transaction.

    I've had processes take as long to roll back as it had been running before being killed.

    Can you tell us more about what the process is, how long it's been running, and why it is rolling back?

    The problem reported is a well known problem that Microsoft has failed to fix across multiple versions. I have seen it on SQL 7.0, 2000, 2005, 2008, and 2008 R2.

    Maybe they will get around to it someday, but they don't seem to be in a hurry.

    http://connect.microsoft.com/SQLServer/feedback/details/433703/killed-rollback

  • that's right! sestell1!!!

    I ask you, again: what's the reason for rollback? do you used kill spid? or occurs fails on server/database?

    depending of reasons, isn't appropriated restart server/sql!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • It was a DB backup operation that took much longer than usual (1 hr vs. 5 + hrs.). I stopped the process and it has been in "Kill/Rollback" mode for half a day

  • guerillaunit (8/31/2012)


    It was a DB backup operation that took much longer than usual (1 hr vs. 5 + hrs.). I stopped the process and it has been in "Kill/Rollback" mode for half a day

    Ah, was the backup using compression?

    If so, there is a bug that can cause a backup process to hang like that if it fails to complete successfully. The only way to clear it is to restart. I think MS released a hot-fix, but it didn't happen enough for me to risk applying an untested hot-fix.

    EDIT - Here's a link to the hotfix:

    http://support.microsoft.com/kb/970133

  • The restart fixed it. Gracias

Viewing 12 posts - 1 through 11 (of 11 total)

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