We are trying to find a way that we can get our DBAs to agree to that will allow us to KILL long running queries from our application code.
In the dev environment life is relatively easy - you can execute a KILL against the spid of your session as we have dbo rights.
However in UAT/production of course our application servers run under a database role which has far more limited permissions, amongst which is the lack of permission to execute a KILL.
I cannot believe we are the only people to hit this problem and desire a solution. Our N-tier application is written in .NET. We cannot use the "Cancel" feature of ADO.Net, because we have multiple application servers servicing the clients using a (highly customised) version of the Microsoft Data Access App Block. Ado.Net Cancel requires "holding on" to your SqlCommand object reference - something which we don't do as it is abstracted. Even if we were to put some nasty in like a static cache of active SqlCommand objects of course in a load balanced server scenario the request to cancel from the client may not even come back to the same server.
So that left us with an idea to store the spid information in the database with a unique query id which is returned to the client - the idea being that if the client issues a cancel request it passes that query id, we open a new session, look up the spid from our running query table for that query id and if the session still is running then "KILL" it.
As stated above that last bit is the sticking point. In an ideal world we would have some extended stored proc which the DBAs own that we are able to call passing the spid, that is a wrapper around KILL. However is it even possible to write and permission a sproc to do what we want (i.e. be called by an account with a different role that does not have that KILL permission)?
The only other alternative that has been mooted is to have a separate "service" application, owned by the DBAs which runs with sufficient dbo priveleges and exposes via a web service for instance the ability to kill a spid on a database. However that is obviously dramatically increasing the complexity of the overall solution and there are other issues it opens up internally over security, ownership, resourcing, hosting etc etc.
Desperately hoping someone out there has some suggestions...
In the dev environment life is relatively easy - you can execute a KILL against the spid of your session as we have dbo rights.
However in UAT/production of course our application servers run under a database role which has far more limited permissions, amongst which is the lack of permission to execute a KILL.
I cannot believe we are the only people to hit this problem and desire a solution. Our N-tier application is written in .NET. We cannot use the "Cancel" feature of ADO.Net, because we have multiple application servers servicing the clients using a (highly customised) version of the Microsoft Data Access App Block. Ado.Net Cancel requires "holding on" to your SqlCommand object reference - something which we don't do as it is abstracted. Even if we were to put some nasty in like a static cache of active SqlCommand objects of course in a load balanced server scenario the request to cancel from the client may not even come back to the same server.
So that left us with an idea to store the spid information in the database with a unique query id which is returned to the client - the idea being that if the client issues a cancel request it passes that query id, we open a new session, look up the spid from our running query table for that query id and if the session still is running then "KILL" it.
As stated above that last bit is the sticking point. In an ideal world we would have some extended stored proc which the DBAs own that we are able to call passing the spid, that is a wrapper around KILL. However is it even possible to write and permission a sproc to do what we want (i.e. be called by an account with a different role that does not have that KILL permission)?
The only other alternative that has been mooted is to have a separate "service" application, owned by the DBAs which runs with sufficient dbo priveleges and exposes via a web service for instance the ability to kill a spid on a database. However that is obviously dramatically increasing the complexity of the overall solution and there are other issues it opens up internally over security, ownership, resourcing, hosting etc etc.
Desperately hoping someone out there has some suggestions...