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

Published On: 2010-03-08By:

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

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via