up vote 0 down vote favorite
share [g+] share [fb]

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?

link|improve this question
2  
I think this may be better suited for serverfault.com – jon3laze Mar 17 '11 at 1:48
feedback

migrated from stackoverflow.com Mar 17 '11 at 8:20

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

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.

link|improve this answer
So in our case one or more of these deadlocked rollbacks was hanging onto a lock on one of our tables. This caused our system to grind to a hault and is no way acceptable. Do you know of any way to see that the transaction is not doing anything? – dennisjtaylor Mar 18 '11 at 1:27
That sounds like a different issue. Every time I've seen this happen the hung rollback released all its locks. Are you sure that the rollback wasn't just taking a long time to process? How long did the command run before you killed it? – mrdenny♦ Mar 18 '11 at 19:18
The rollback had been running for about 3x the time it was running before the rollback. But there was no way from our perspective to see if it was done. – dennisjtaylor Mar 19 '11 at 0:20
It could be that the query is rolling back, but that answer just isn't sitting well with me. Check the system waits the next time it happens and see what the spid shows that it is waiting on. That might be able to provide some insight. Is this a very large statement or transaction, or something very small? Can you provide some sudu code? – mrdenny♦ Mar 19 '11 at 3:28
Thanks for the suggestion about the system waits. That should be usefull if this was to happen again. The transaction was not not a large transaction. – dennisjtaylor Mar 23 '11 at 19:15
feedback
up vote -1 down vote

All I can say is to do the basic stuffs. First of all know the importance of the script that currently running at the server. Complicated scripts that would take too much memory consumption and might do dataloss 'MUST' do backup procedure first before running the script. Let's say a thousand line script is scheduled to run unattended, techy guys who developed the script must be advised to include an automated differential backup on their scripts to make sure that if their script crashes then at least a reliable backup was secured first.

If the script don't have an automated backup procedure, well a technical guy must be responsible to do the backup manually at a certain scheduled time. The only thing that needs to be considered is the to know when the script would start running on the server so that the dba would also know when to start the manual backup process. One reason why dba's exist.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.