Menu
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);
633
You can use the KILL SPID command to kill a particular user session. You can only KILL the user processes. Once we kill a session, it undergoes through the rollback process, and it might take time and resources as well to perform a rollback.
Just use a RETURN (it will work both inside and outside a stored procedure). In a script, you can't do a RETURN with a value like you can in a stored procedure, but you can do a RETURN. dangerous to assume as it will continue after then next GO. GO is a script terminator or delimiter; it's not SQL code.
When the query is submitted, the Abort menu and tool button are enabled so that the query can be aborted if necessary. Select Tools > Abort.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
