Can sql server queries be really cancelled/killed?

Question

I would like to give a user the ability to cancel a running query. The query is really slow. (Query optimization is besides the point.) This is mainly out of my curiosity.

MSDN says:

If there is nothing to cancel, nothing occurs. However, if there is a command in process, and the attempt to cancel fails, no exception is generated.

• Cmd - SqlCommand
• DA - DataAdapter
• Conn - SqlConnection
• CurrentSearch - Thread
• LongQuery - Singleton

Here's what I have:

var t = new Thread(AbortThread);
t.Start();

void AbortThread()
{
    LongQuery.Current.Cmd.Cancel();
    LongQuery.Current.Cmd.Dispose();
    LongQuery.Current.DA.Dispose();
    LongQuery.Current.Conn.Close();
    LongQuery.Current.Conn.Dispose();
    LongQuery.Current.Cmd = null;
    LongQuery.Current.DA = null;
    LongQuery.Current.Conn = null;
    CurrentSearch.Abort();
    CurrentSearch.Join();
    CurrentSearch = null;
}

I noticed that CurrentSearch.Abort() was blocking, that's why I wrapped it in a thread, which probably means that the thread is still working.

Finally, is there anything else than this that I can do to cancel a query? Is it actually possible to cancel such a long query from .NET?

Answer 1

IF you really absolutely want to kill it for good use this approach:

• store away the session ID right before starting the long-running query by calling SELECT @@SPID AS 'SESSIONID' on the same connection

When you want to kill it:

• Open a new DB connection
• issue a KILL command for that session ID
  BEWARE as the MSDN documentation states you need the permission ALTER ANY CONNECTION to do this