How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:

> Is there a way to identify a specific query - such as by begin time or use
r
> account - and then be able to kill the query programaticaly. We have a w
eb
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table).
I
> would like a way a user can kill their query.
> Thanks,|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done b
y
issuing queries/commands to the SQL via a Java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
[vbcol=seagreen]
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
>|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a Java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:

> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.[vbcol=seagreen]
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
>
user[vbcol=seagreen]
web[vbcol=seagreen]
table). I[vbcol=seagreen]