[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

Stop runaway query in SQLServer

Hi,

a classical runaway query question: if you (or better yet your code) discovers that a query is taking too long, it would be nice to abandon and even stop  this query.

On the GUI side, it is entirely possible. But giving the user a chance to start a bad query with a quick click can lead to overload.

So in addition, I would like to be able to stop the backend processing. SQLServer is the database in this case.

Any idea? Thank you.
0
kerzner
Asked:
kerzner
  • 3
  • 3
1 Solution
 
frankyteeCommented:
where is the user executing the query? from query analzyer or the front end (Access, vb etc)? if front end then set the timeout property of the connection to say 5 minutes etc so the query will stop.
0
 
kerznerAuthor Commented:
from java code using jdbc in weblogic container :)

here too, though, it is possible to set time out - only it is now knows in advance if the user wants to cancel
0
 
frankyteeCommented:
i think you'll need two connections to SQL from your front end.
say #1 for the connection that will run the kill command, and #2 that runs the long sql query.
use the global sql variable @@spid to find the process id of the 2nd connection.
and assign to your local variable before you run the long running sql query.
then from your 1st connection, the sql command to kill the 2nd:
EXEC ('kill ' + @@spid)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kerznerAuthor Commented:
going to try that!
0
 
frankyteeCommented:
kerzner, how did it go?
0
 
kerznerAuthor Commented:
it all makes sense; but I did not try it.

I think the questions is answered though.

Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now