September 25, 2014 at 9:24 pm
Hi all ,
I would like to set time out in my Query (Transact SQL). I want to set for example 10 mins maximum for a query to run in SQL job / maintenance plan .
because previously there is 1 query that keep running for 8 hours and cause HIGH CPU load which is BAD .
Any advice is highly appreciated
Cheers
September 25, 2014 at 9:48 pm
If you have looked you will find that the Execute T-SQL statement task has a "Execution time out" setting.
With a SQL Agent job step you are out of luck with the T-SQL step. You could try using a PowerShell step or CmdExec step that calls SQLCMD and set the time out through the query execution command, but that gets a bit dirty to me.
A more appropriate response to this would be tune the problem query. A monitoring step would also be setup alerts for long running transactions[/url] and take action as needed. I make a statement in the discussion of this article regarding simply creating a procedure to run on a scheduled basis that checks and sends email.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 25, 2014 at 10:58 pm
Shawn Melton (9/25/2014)
If you have looked you will find that the Execute T-SQL statement task has a "Execution time out" setting.With a SQL Agent job step you are out of luck with the T-SQL step. You could try using a PowerShell step or CmdExec step that calls SQLCMD and set the time out through the query execution command, but that gets a bit dirty to me.
A more appropriate response to this would be tune the problem query. A monitoring step would also be setup alerts for long running transactions[/url] and take action as needed. I make a statement in the discussion of this article regarding simply creating a procedure to run on a scheduled basis that checks and sends email.
wowww..Great RESPONSE!!!
it works ..using Execute T-SQL statement task
many thanksss:)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply