 |
|
|
|
Next: growing size of system tables in distribution db
|
| Author |
Message |
External
Since: Nov 05, 2008
Posts: 4
|
(Msg. 1) Posted: Wed Nov 05, 2008 12:57 pm
Post subject: How to set timeout in SQL Agent Job or in stored proc
Archived from groups: microsoft>public>sqlserver>server (more info?)
|
|
|
|
|
| Back to top |
|
 | |
External
Since: Aug 13, 2008
Posts: 11
|
(Msg. 2) Posted: Wed Nov 05, 2008 6:14 pm
Post subject: RE: How to set timeout in SQL Agent Job or in stored proc [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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=LongRu...ngJobMo
Hope it helps!
--
Jonathan Kehayias
SQL Server MVP, MCITP
http://jmkehayias.blogspot.com/
http://www.sqlclr.net/
"Thanh Nguyen" wrote:
> 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
>
>
>
> |
|
| Back to top |
|
| |
External
Since: Jan 29, 2004
Posts: 1559
|
(Msg. 3) Posted: Thu Nov 06, 2008 1:25 am
Post subject: Re: How to set timeout in SQL Agent Job or in stored proc [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
> 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.
> 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
"Jonathan Kehayias" wrote
in message
> 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=LongRu...ngJobMo
>
> Hope it helps!
>
> --
> Jonathan Kehayias
> SQL Server MVP, MCITP
> http://jmkehayias.blogspot.com/
> http://www.sqlclr.net/
>
> "Thanh Nguyen" wrote:
>
>> 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
>>
>>
>>
>> >> Stay informed about: How to set timeout in SQL Agent Job or in stored proc |
|
| Back to top |
|
| |
External
Since: Aug 13, 2008
Posts: 11
|
(Msg. 4) Posted: Thu Nov 06, 2008 4:37 am
Post subject: Re: How to set timeout in SQL Agent Job or in stored proc [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
> 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/
"Tibor Karaszi" wrote:
> > 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.
>
>
> > 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
>
>
> "Jonathan Kehayias" wrote
> in message
> > 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=LongRu...ngJobMo
> >
> > Hope it helps!
> >
> > --
> > Jonathan Kehayias
> > SQL Server MVP, MCITP
> > http://jmkehayias.blogspot.com/
> > http://www.sqlclr.net/
> >
> > "Thanh Nguyen" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
>
>
> >> Stay informed about: How to set timeout in SQL Agent Job or in stored proc |
|
| Back to top |
|
| |
External
Since: Jan 29, 2004
Posts: 1559
|
(Msg. 5) Posted: Thu Nov 06, 2008 8:25 am
Post subject: Re: How to set timeout in SQL Agent Job or in stored proc [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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
"Jonathan Kehayias" wrote
in message
>> 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/
>
>
> "Tibor Karaszi" wrote:
>
>> > 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.
>>
>>
>> > 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
>>
>>
>> "Jonathan Kehayias"
>> wrote
>> in message
>>
>> > 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=LongRu...ngJobMo
>> >
>> > Hope it helps!
>> >
>> > --
>> > Jonathan Kehayias
>> > SQL Server MVP, MCITP
>> > http://jmkehayias.blogspot.com/
>> > http://www.sqlclr.net/
>> >
>> > "Thanh Nguyen" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>> >>
>>
>>
>> >> Stay informed about: How to set timeout in SQL Agent Job or in stored proc |
|
| Back to top |
|
| |
External
Since: Nov 05, 2008
Posts: 4
|
(Msg. 6) Posted: Thu Nov 06, 2008 12:18 pm
Post subject: Re: How to set timeout in SQL Agent Job or in stored proc [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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
"Tibor Karaszi" wrote in
message
> 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
>
>
> "Jonathan Kehayias" wrote in
> message
>>> 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/
>>
>>
>> "Tibor Karaszi" wrote:
>>
>>> > 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.
>>>
>>>
>>> > 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
>>>
>>>
>>> "Jonathan Kehayias" wrote
>>> in message
>>> > 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=LongRu...ngJobMo
>>> >
>>> > Hope it helps!
>>> >
>>> > --
>>> > Jonathan Kehayias
>>> > SQL Server MVP, MCITP
>>> > http://jmkehayias.blogspot.com/
>>> > http://www.sqlclr.net/
>>> >
>>> > "Thanh Nguyen" wrote:
>>> >
>>> >> 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
>>> >>
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
> >> Stay informed about: How to set timeout in SQL Agent Job or in stored proc |
|
| Back to top |
|
| |
External
Since: Jan 05, 2011
Posts: 1
|
(Msg. 7) Posted: Wed Jan 05, 2011 11:25 am
Post subject: Re: There is no way to do that inside SQL Agent that I know of. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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
> On Wednesday, November 05, 2008 1:57 PM Thanh Nguyen wrote:
> 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
>> On Wednesday, November 05, 2008 9:14 PM JonathanKehayia wrote:
>> 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=LongRu...ngJobMo
>>
>> Hope it helps!
>>
>> --
>> Jonathan Kehayias
>> SQL Server MVP, MCITP
>> http://jmkehayias.blogspot.com/
>> http://www.sqlclr.net/
>>
>> "Thanh Nguyen" wrote:
>>> On Thursday, November 06, 2008 12:53 AM Tibor Karaszi wrote:
>>> 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
>>>
>>>
>>> "Jonathan Kehayias" wrote
>>> in message
>>>> On Thursday, November 06, 2008 7:37 AM JonathanKehayia wrote:
>>>> 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/
>>>>
>>>>
>>>> "Tibor Karaszi" wrote:
>>>>> On Thursday, November 06, 2008 8:13 AM Tibor Karaszi wrote:
>>>>> 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
>>>>>
>>>>>
>>>>> "Jonathan Kehayias" wrote
>>>>> in message
>>>>>> On Thursday, November 06, 2008 1:18 PM Thanh Nguyen wrote:
>>>>>> Jonathan, Tibor:
>>>>>>
>>>>>> 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 >> Stay informed about: How to set timeout in SQL Agent Job or in stored proc |
|
| Back to top |
|
| |
| Related Topics: | SQL server transaction timeout and stored procedures - Hi, is there a default transactions timeout when running stored procedures? Basically I need to run stored procedures that could involve 100,000s of inserts\updates and I want to be able to let the procedure run until these are completed. Is this..
help with stored proc - Hi, I am writing a simple stored proc: creat PROCEDURE Purge @timet numeric(3) -- User Specifies how much data to perserve in months AS SET NOCOUNT ON IF OBJECT_ID('tmp_AU1') IS NOT NULL DROP TABLE tmp_AU1 GO IF OBJECT_ID('tmp_AU2') IS NOT NULL DROP....
Unicode parameter in stored proc - If my stored procedure calles the following exec dbo.storedproc N'test' and the body of the stored proc for instance is as follows create procedure dbo.storedproc @param varchar(50) as select * from <Table> where <col> = @param and <co...
Copy Stored proc from one DB to other - Hi All, I want to copy my stored proc from one DB to other. I am wondering how can I do it???????Can I use DTS to do that?I couldn't find option in to do it in DTS??? Thanks!
Slow stored proc recompile? - I have a main SQL7 stored proc that creates a temporary table and then calls a number of stored procedures that each insert into the table. I'm experiencing slow performance and a SQL trace shows that an SP recompile is happening when the INSERT is.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
