How can you cancel a SQL Server execution process programmatically

Question

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);

Please pay more attention to your tag choices - tags are how you connect your question to the appropriate audience. — Joel Coehoorn, Jul 07 '09 at 14:04

score 8 · Answer 1 · edited Sep 24 '12 at 13:22

8

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.

edited Sep 24 '12 at 13:22

Seki

11,135
7
46
70

answered Jul 07 '09 at 14:08

SQLMenace

132,095
25
206
225

score 4 · Answer 2 · answered Jul 07 '09 at 14:03

4

Kill @Spid

note that this is a last effort. you should just close the connection on the client to kill the process.

answered Jul 07 '09 at 14:03

Mladen Prajdic

15,457
2
43
51

Just be aware that you can't kill your own spid, you need to create another connection and then kill the spid from the – SQLMenace Jul 07 '09 at 14:09

score 2 · Answer 3 · edited May 03 '17 at 05:01

2

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.

edited May 03 '17 at 05:01

Ajay2707

5,690
6
40
58

answered May 03 '17 at 00:20

user7954693

71
1
1

score 0 · Answer 4 · answered Jul 07 '09 at 14:34

You have to run your query asynchronously, as follows:

    SqlConnection _anotherConnection;
    SqlCommand _anotherCommand;
    IAsyncResult _anotherCommandStarted;
    _anotherCommand = _anotherConnection.CreateCommand();
    _anotherCommand.CommandText = string.Format("SET DEADLOCK_PRIORITY HIGH; BEGIN TRANSACTION; {0};", hookCommand);
    _anotherCommand.CommandType = CommandType.Text;
    _anotherCommand.ExecuteNonQuery();
    _anotherCommand.CommandText = "UPDATE Data.Hook1 SET i=1-i";
    _anotherCommandStarted = _anotherCommand.BeginExecuteNonQuery();

To cancel the command, run this:

    _anotherCommand.EndExecuteNonQuery(_anotherCommandStarted);

The BeginExecuteNonQuery() suggestion is good, but EndExecuteNonQuery does not cancel the command - it blocks until the command completes. — Mike, Nov 13 '09 at 16:33

How can you cancel a SQL Server execution process programmatically

4 Answers4

Linked