SQL Server with Mr. Denny

Mar 8 2010   11:00AM GMT

How do I deal with a rollback which is stuck at 0% remaining?



Posted by: Denny Cherry
Tags:
SQL Server

Every once and a while you have to kill a SPID in SQL Server.  And on a rare occasion the SPID will rollback, but won’t actually rollback and go away.  While this is annoying there isn’t actually anything bad going on.  The SQL Server is running just fine, however you won’t be able to kill this SPID without restarting the SQL instance.

Typically when I’ve seen this the client application has been disconnected from the SQL Server.  From what I understand is happening is:

  1. The SPID is killed
  2. The SQL Server rolls back the transaction
  3. The client is informed of the rollback
  4. The client acknowledges that the rollback is complete
  5. SQL terminates the SPID

Every time that I’ve seen this on my servers the client has already disconnected, do to a reboot, network drop, client crash, etc which stops the SQL Server from telling the client that the rollback is complete.  This breaks something within step 3 and 4 leaving the process sitting there.

The upside to this problem is that the rollback is complete and the transaction has been completely rolled back and closed so it isn’t holding any locks.  The downside is that you’ll need restart the SQL Instance in order get rid of the process.  Killing the process won’t do anything for you as it will only tell you that there are 0 seconds remaining and that the rollback is at 0%.

If you have one of these processes show up on you and you have to leave it for a day or two until you can restart the instance there shouldn’t be any harm in this as the process is idle. It is using up a small amount of memory, but once the rollback has completed it isn’t using any CPU or memory.  Upon restart of the instance it won’t add any time to the instance restart as the transaction has been rolled back so it’ll come back online quickly.

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: