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:
- The SPID is killed
- The SQL Server rolls back the transaction
- The client is informed of the rollback
- The client acknowledges that the rollback is complete
- 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
