How can you cancel a SQL Server execution process programmatically

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

  • Column name sql isnull() clause where
  • Get a list of dates between two dates using a function
  • Is there a tool to generate a full database DDL for SQL Server? What about Postgres and MySQL?
  • VB - Get the strings inside IN statement
  • How to update a column in a SQL Server table with millions of rows when its column “createdDate” passes 90 days from now? Can we use trigger?
  • select 19/12 return 1,I need in decimal that is 1.58 (sqlserver 2005)
  • //cancel the process if it is taking too long
    DB.Execute("sp_CancelProcess @ProcessID=" + processID);
    

    4 Solutions collect form web for “How can you cancel a SQL Server execution process programmatically”

    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.
    

    Kill @Spid

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

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.