Cancel MS SQL Query

I wanted to please ask how can I cancel a query in MS SQL if the query takes more than 3 minutes to run.  I need to place this logic in the query itself that is used.  I am using MS SQL 2005.  Thanks!
LVL 1
jjrr007Asked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
this should give you the session id of anything that has been executing for longer than 60 seconds.

select session_id
from
(
select objectname = object_name(rr.objectid, rr.dbid), * from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) rr
where session_id > 50 and datediff(s, start_time, getdate()) > 60
) a
where objectname = 'yourstoredprocname'
0
 
Daniel WilsonCommented:
AFAIK, you can't put that logic in the query itself.

You CAN put it into the application that's call the SQL Server w/ the query.  That would be the CommandTimeout property (in seconds) in either ADO or ADO.Net.

There's also a setting at the server level ... but for most application's that's WAY too broad.
0
 
jjrr007Author Commented:
How about adding the logic to the MS Sql server job?The query is executed by the job.  

I am not using the query in ado/ado.net.  Thanks.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
chapmandewCommented:
Why are you concerned about the timeout?
0
 
Daniel WilsonCommented:
I can't find a timeout option in the job setup ...
0
 
jjrr007Author Commented:
How about calling the job using a bat file?  The bat file could have the time out option.  How would I do that?  
0
 
chapmandewCommented:
Why are you concerned about the timeout?
0
 
Daniel WilsonCommented:
jjrr, the question chapmandew is asking is very valid.  If we know WHY, we may be able to suggest a better solution.

If you do want to do it from the command line, check out the sqlcmd utility ... and the -t option: http://msdn.microsoft.com/en-us/library/ms162773.aspx
0
 
jjrr007Author Commented:
I agree.  Chapmandew's question is a good one.  I ask because at times a query that I have written can take more than a few minutes to run.  The query can take longer for a wide variety of circumstances.  For example, the server may have too much traffic or there could be some tables that are updating etc.  

When the query takes more than a few minutes to run, I have noticed that the query (that has been optimized) can negatively impact the server.  Since I am running the query every 25 minutes, I want to the query to stop running when it is taking more than 3 minutes to run.  What do you suggest?  Thanks!
0
 
Daniel WilsonCommented:
I can't think of an option better than using SqlCmd to execute it from the command-line and using the -t option.
0
 
chapmandewCommented:
In that case, you might be better off querying system tables to see if you have any active queries that have been open for a certain amt of time...and cancel it.  Not a good idea, but an option.
0
 
jjrr007Author Commented:
Thanks.  Maybe I am mistaken, but I don't think this will cancel the query.  From what I know this will only stop the process if it is not executed. In other words, the T option is used to monitor the time until it is executed. Is that right?
0
 
chapmandewCommented:
are you referring to my post or Daniel's?
0
 
jjrr007Author Commented:
chapmandew, Thanks for your time.  In my last post, I was eferring to Daniel's post regarding the t option.  I apologize for not looking more closely at your post.  

 How would I write a query that you mentioned in your post "22933218"? There is only one stored procedure that is doing this so I could just look for that.  
0
 
jjrr007Author Commented:
Thanks.  This looks good Chapnew.  How do I tell the server to cancel the query?
0
 
jjrr007Author Commented:
I appreciate everyone's time.  I really just need to know how to cancel the query.  I haven't received an answer about cancelling a query..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.