Our company recently experienced a problem with our SQL Server database where we were having high load and some database monitoring scripts killed a number of connections.
When this happened a few of the transaction in process failed to finish rolling back.
They repeatedly said the rollback was 0% complete when executing kill on the pid.
After searching online we discovered people suggesting to restart SQL Server. This was not desirable as this was a production db. We were also concerned that restartinng could cause corruption to our database forcing us to restore from backups.
We eventually restarted the server and everything started up fine without the transactions that where rolling back.
My question is:
Is it possible to prevent this from happening in the first place? If not is there any way to know it is safe to restart?
Best Answer
First to answer your final question, can you present this from happening again, no not really.
What happened is that when the processes were killed, the SQL Server tells the client that the process has been killed. Under some circumstances the SQL Server will hang while trying to tell the client that the process has been killed. Normally this isn't a problem, but there are a few times when it will cause this problem.
If you leave the processes running nothing will happen, other than the SPID is sitting there in process. The rollback has actually completed. The only way to clear the SPID is to restart the SQL instance. There is 0 chance of corruption by restarting your SQL Instance when this has happened.