January 18, 2008 at 4:16 am
Is there any way to set a Timeout on a specific JOB
i have a job that does incremental uploads to a datawarehouse. The job normally takes approx 20 minutes to execute. Sometimes the job fails to execute, i get a failure Notification but the job continues executing even though it has failed.
My problem is that i have incremental loads scheduled every hour but if one fails the subsequent loads will not begin as the problem one just Hangs.
Is there any way to stop a jobs after a time period eg if it has not finished in 1 hour stop the job.
All i can seem to find is how to set timeouts on SQL Server Agent and not on individual Jobs.
I thought you could do this on previous versions of SQL Server especially in relation to Backup's when they ran for a certain period
Any help greatly appreciated.
Thanks in Advance
March 22, 2010 at 8:53 am
Hi,
acutally i'm loking for a solution for a similar problem. Did you solve yours?
Regards
Gregor
March 23, 2010 at 4:32 am
you can try below link but i am not quite sure that it can give you required help
http://blogs.solidq.com/EN/rdyess/Lists/Posts/Post.aspx?ID=16
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e5c0af6e-763e-460b-818b-0e9df6572f12
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 23, 2010 at 6:58 am
Thanks for your reply.
Unfortuntaly the link you sent is broken or poits to an closed area.
Regards
Gregor
March 23, 2010 at 7:05 am
try now the above links
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 24, 2010 at 7:03 am
We've implemented a custom reindex job for our clients and had issues where, if the indexes were really, really bad, it would run into production hours. Here's a snippet of the proc that the job calls. @QuitTime is a datetime parameter that gets passed in and only the hour portion is used so the job doesn't need to keep changing with the date. @starttime is pulled from getdate() when the proc starts.
IF @QuitTime = ''
SELECT @QuitTime = NULL
IF @QuitTime IS NOT NULL
BEGIN
SELECT @Starttime = DATEPART(hh, @Now)
SELECT @Quittime1 = DATEPART(hh, @Quittime)
IF @Quittime1 < @Starttime
BEGIN
--If quit time is before start time we want to quit the next day
SELECT @nextday = GETDATE() + 1
SELECT @month = DATENAME(Month, @nextday) -- Do this to get only date values
SELECT @day = DATENAME(Day, @nextday)
SELECT @year = DATENAME(Year, @nextday)
SELECT @nextday1 = @month + '' + @day + ',' + @year
SELECT @nextday = CAST(@nextday1 AS DATETIME)
SELECT @nextday = DATEADD(hh, @Quittime1, @nextday) -- Add quittime from here
SELECT @Quittime = @nextday
END
ELSE
BEGIN
SELECT @month = DATENAME(Month, @Now)
SELECT @day = DATENAME(Day, @Now)
SELECT @year = DATENAME(Year, @Now)
SELECT @today1 = @month + '' + @day + ',' + @year
SELECT @Now = CAST(@today1 AS DATETIME)
SELECT @Now = DATEADD(hh, @Quittime1, @Now)
SELECT @Quittime = @Now
END
END
ELSE
IF @QuitTime IS NULL -- Assign default value if no value is passed for quittime
BEGIN
SET @QuitTime = DATEADD(day, 2, GETDATE())
END
/**
Other code here
**/
OPEN objectsname
FETCH NEXT FROM objectsname INTO @tablename, @tableid, @indid, @indexname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Now = GETDATE()
IF @Now < @Quittime
BEGIN
--Work code here
END
ELSE
BREAK
FETCH NEXT FROM objectsname INTO @tablename, @tableid, @indid, @indexname
END
CLOSE objectsname
DEALLOCATE objectsname
There may be an easier way to do this for you so just use this as a starting point.
March 24, 2010 at 9:31 am
@Bhuvnesh: Thanks for your effort. Now the links work.
@all: The problem was solved by realising a second job that monitors the runtime of the productiv job. If the runtime exeeds the maximum runtime, the job is cancled. That just works fine for us.
Thanks for all replys.
Regards
Gregor
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply