0

I've tried to kill a query on ms sql server management studio. Now it has the status killed/rollback.

When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I delete this query without restarting the sql server?

I will really appreciate all your answers.

  • 2
  • I have seen this blog. But you must restart the sql server. I wonder if there's a command to hard kill the query so that you don't have to restart the server. – Ezra P. Jun 24 '16 at 12:07
  • 1
    I've seen rollback take a long time, is it a large commitment of data? how long was it altering before it went into rollback? It can take longer to rollback than it took to run in the first place – Andrew Deighton Jun 24 '16 at 12:15
  • Actually, I execute a powershell script.It doesn't do anything with the database yet. the powershell script went into a loop and now it keeps running. So if I can make a hard kill, it will doesn't do anything harm. And it is almost for hours that it stuck at 0%. – Ezra P. Jun 24 '16 at 12:25
  • Are you sure your script did not restart query right after kill? Are you running on real server or on laptop or something? – Ivan Starostin Jun 24 '16 at 12:57
2

Even if not applicable here, please be wary of runaway queries initiated from an OPENQUERY command. These run on remote servers, and continue to do so no matter how many times the user tries to cancel from their SSMS console....these pass-thru queries will continue to run on the target server. And if, God forbid, there's a recursive loop in there, it'll keep on running until the kill command is issued from the remote server end

| improve this answer | |
1

You have to be very careful. Assuming your query is not restarting and you have a rollback for that SPID.
If you restart SQL Server it won't help, because transaction still would have to be rolled back.
The problem with that is following: When you run a transaction in multi-CPU environment with not restricted degree of parallelism it most probably will generate parallel execution plan.
Would say your transaction run for 10 minutes on 8 CPUs.
When you Killed it, ROLLBACK will be processed ONLY by ONE CPU. That means it might take up to 8 times longer to recover.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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