How can you cancel a SQL Server execution process programmatically
Solution 1
use KILL
with the process id:
KILL 53;
Just be aware that you can't kill your own spid, you need to create another connection and then kill the spid from the
if you try to kill your own SPID you will get the following error
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Solution 2
Kill @Spid
note that this is a last effort. you should just close the connection on the client to kill the process.
Solution 3
sp_who2
List of process displays
Find you SPID
Kill SPID
If error message you can't kill your own process
See where the process rund in your management studio and stop it.
Or close current connection and reopen and try to kill now.
Tawani
Updated on July 03, 2022Comments
-
Tawani about 2 years
Let's say you have execute the following (long running) process from your code:
int processID = DB.Execute(SQL); //some long running sql statement
Is there a way to programatically call SQL Server to cancel the process if it is taking too long (kind of like hitting the "Stop" button in QueryAnalyzer)?
//cancel the process if it is taking too long DB.Execute("sp_CancelProcess @ProcessID=" + processID);
-
SQLMenace almost 15 yearsJust be aware that you can't kill your own spid, you need to create another connection and then kill the spid from the
-
Mike over 14 yearsThe BeginExecuteNonQuery() suggestion is good, but EndExecuteNonQuery does not cancel the command - it blocks until the command completes.