How to set timeout in SQL Agent Job or in stored proc

[Thanh Nguyen]

Hi Experts,



I have a sql agent job that executes a stored proc. The job normally takes 5
minutes to finish. Last week, it took more than 8 hr to finish. Is there a
way to set a timeout in sql agent for this specific job, or anyway to set a
timeout inside the stored proc itself?



Thanh Nguyen

[Jonathan Kehayias]

There is no way to do that inside SQL Agent that I know of. What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for a set period
of time. When that time is exceeded, it will terminate the osql or sqlcmd
call which will kill the connection SQL.

Rather than do this, I have a job that runs in the mornings to tell me if
any jobs have been running longer than 30 minutes and what spid they are
currently running on. This way I can evaluate if I need to jump online
before leaving for the office to stop the job in SQL Agent. You can find the
code for it online:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon

Hope it helps!

--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Thanh Nguyen
Hi Experts,
I have a sql agent job that executes a stored proc. The job normally takes 5
minutes to finish. Last week, it took more than 8 hr to finish. Is there a
way to set a timeout in sql agent for this specific job, or anyway to set a
timeout inside the stored proc itself?
Thanh Nguyen

[Tibor Karaszi]

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of.

I agree. I looked yesterday in the registry through all Agent options
but didn't find anyone which seems to control this.

Post by Jonathan Kehayias
What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for a set period
of time.

Hmm, why not just use a Agent job but with a CmdExec step (instead of
TSQL) and kick off sqlcmd or osql using the -t option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of. What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for a set period
of time. When that time is exceeded, it will terminate the osql or sqlcmd
call which will kill the connection SQL.
Rather than do this, I have a job that runs in the mornings to tell me if
any jobs have been running longer than 30 minutes and what spid they are
currently running on. This way I can evaluate if I need to jump online
before leaving for the office to stop the job in SQL Agent. You can find the
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon
Hope it helps!
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Thanh Nguyen
Hi Experts,
I have a sql agent job that executes a stored proc. The job
normally takes 5
minutes to finish. Last week, it took more than 8 hr to finish. Is there a
way to set a timeout in sql agent for this specific job, or anyway to set a
timeout inside the stored proc itself?
Thanh Nguyen

[Jonathan Kehayias]

Post by Tibor Karaszi
Hmm, why not just use a Agent job but with a CmdExec step (instead of
TSQL) and kick off sqlcmd or osql using the -t option?

I thought about that, but you still don't get the ability to set a time out
on the Task. The windows task scheduler on the other hand can specify that a
task can only run for a set amount of time, on the Settings Tab of the task.
It is generally defaulted to 72 hrs.
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Tibor Karaszi

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of.

I agree. I looked yesterday in the registry through all Agent options
but didn't find anyone which seems to control this.

Post by Jonathan Kehayias
What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for a set period
of time.

Hmm, why not just use a Agent job but with a CmdExec step (instead of
TSQL) and kick off sqlcmd or osql using the -t option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of. What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for a set period
of time. When that time is exceeded, it will terminate the osql or sqlcmd
call which will kill the connection SQL.
Rather than do this, I have a job that runs in the mornings to tell me if
any jobs have been running longer than 30 minutes and what spid they are
currently running on. This way I can evaluate if I need to jump online
before leaving for the office to stop the job in SQL Agent. You can find the
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon
Hope it helps!
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Thanh Nguyen
Hi Experts,
I have a sql agent job that executes a stored proc. The job
normally takes 5
minutes to finish. Last week, it took more than 8 hr to finish. Is there a
way to set a timeout in sql agent for this specific job, or anyway to set a
timeout inside the stored proc itself?
Thanh Nguyen

[Tibor Karaszi]

Yes..., there is a difference, being the SQLCMD which is per query,
where the task scheduler would be for the whole task execution...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Jonathan Kehayias

Post by Tibor Karaszi
Hmm, why not just use a Agent job but with a CmdExec step (instead of
TSQL) and kick off sqlcmd or osql using the -t option?

I thought about that, but you still don't get the ability to set a time out
on the Task. The windows task scheduler on the other hand can
specify that a
task can only run for a set amount of time, on the Settings Tab of the task.
It is generally defaulted to 72 hrs.
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Tibor Karaszi

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of.

I agree. I looked yesterday in the registry through all Agent
options
but didn't find anyone which seems to control this.

Post by Jonathan Kehayias
What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for
a
set period
of time.

Hmm, why not just use a Agent job but with a CmdExec step (instead of
TSQL) and kick off sqlcmd or osql using the -t option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of. What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for
a
set period
of time. When that time is exceeded, it will terminate the osql
or
sqlcmd
call which will kill the connection SQL.
Rather than do this, I have a job that runs in the mornings to
tell
me if
any jobs have been running longer than 30 minutes and what spid
they
are
currently running on. This way I can evaluate if I need to jump online
before leaving for the office to stop the job in SQL Agent. You
can
find the
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon
Hope it helps!
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Thanh Nguyen
Hi Experts,
I have a sql agent job that executes a stored proc. The job normally takes 5
minutes to finish. Last week, it took more than 8 hr to finish.
Is
there a
way to set a timeout in sql agent for this specific job, or
anyway
to set a
timeout inside the stored proc itself?
Thanh Nguyen

[Thanh Nguyen]

Jonathan, Tibor:

Thanks very much for your valuable comments. I'll go ahead implement my job
using windows task w/ sqlcmd

Thanks again.

Thanh Nguyen

Yes..., there is a difference, being the SQLCMD which is per query, where
the task scheduler would be for the whole task execution...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Jonathan Kehayias

Post by Tibor Karaszi
Hmm, why not just use a Agent job but with a CmdExec step (instead of
TSQL) and kick off sqlcmd or osql using the -t option?

I thought about that, but you still don't get the ability to set a time out
on the Task. The windows task scheduler on the other hand can specify that a
task can only run for a set amount of time, on the Settings Tab of the task.
It is generally defaulted to 72 hrs.
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Tibor Karaszi

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of.

I agree. I looked yesterday in the registry through all Agent options
but didn't find anyone which seems to control this.

Post by Jonathan Kehayias
What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for a set period
of time.

Hmm, why not just use a Agent job but with a CmdExec step (instead of
TSQL) and kick off sqlcmd or osql using the -t option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of. What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for a set period
of time. When that time is exceeded, it will terminate the osql or sqlcmd
call which will kill the connection SQL.
Rather than do this, I have a job that runs in the mornings to tell me if
any jobs have been running longer than 30 minutes and what spid they are
currently running on. This way I can evaluate if I need to jump online
before leaving for the office to stop the job in SQL Agent. You can find the
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon
Hope it helps!
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Thanh Nguyen
Hi Experts,
I have a sql agent job that executes a stored proc. The job normally takes 5
minutes to finish. Last week, it took more than 8 hr to finish. Is there a
way to set a timeout in sql agent for this specific job, or anyway to set a
timeout inside the stored proc itself?
Thanh Nguyen

[phani p]

You can use maintenance plans as execute tsql. In that you can set time out period for your command or sp.

Hope this will help

Thanks
Phani

Post by Thanh Nguyen
Hi Experts,
I have a sql agent job that executes a stored proc. The job normally takes 5
minutes to finish. Last week, it took more than 8 hr to finish. Is there a
way to set a timeout in sql agent for this specific job, or anyway to set a
timeout inside the stored proc itself?
Thanh Nguyen

Post by Jonathan Kehayias
There is no way to do that inside SQL Agent that I know of. What you can do
is use the Task Schedule in Windows, to run the code from the job with osql
or sqlcmd, and then specify in the task that it can only run for a set period
of time. When that time is exceeded, it will terminate the osql or sqlcmd
call which will kill the connection SQL.
Rather than do this, I have a job that runs in the mornings to tell me if
any jobs have been running longer than 30 minutes and what spid they are
currently running on. This way I can evaluate if I need to jump online
before leaving for the office to stop the job in SQL Agent. You can find the
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon
Hope it helps!
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Tibor Karaszi
I agree. I looked yesterday in the registry through all Agent options
but didn't find anyone which seems to control this.
Hmm, why not just use a Agent job but with a CmdExec step (instead of
TSQL) and kick off sqlcmd or osql using the -t option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Jonathan Kehayias
I thought about that, but you still don't get the ability to set a time out
on the Task. The windows task scheduler on the other hand can specify that a
task can only run for a set amount of time, on the Settings Tab of the task.
It is generally defaulted to 72 hrs.
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/

Post by Tibor Karaszi
Yes..., there is a difference, being the SQLCMD which is per query,
where the task scheduler would be for the whole task execution...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Thanks very much for your valuable comments. I will go ahead implement my job
using windows task w/ sqlcmd
Thanks again.
Thanh Nguyen
Submitted via EggHeadCafe
Microsoft ASP.NET For Beginners
http://www.eggheadcafe.com/training-topic-area/ASP-NET/7/ASP.aspx