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.

Ezra P.
  • 1
  • 1
  • 1
  • 1

2 Answers2

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

Deepraj Kunnath
  • 51
  • 5
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.

Slava Murygin
  • 474
  • 3
  • 11