January 9, 2013 at 6:41 am
Hi fellow SQL server users,
I have a SQL server agent job with several job steps. This job is scheduled and runs almost every night.
Sometimes there is one step that is taking to much time to execute.
I know of the possibillity to set the properties for a step what to do after failure or success but in this case the step is still executing. And i want to stop it automatically after a specific period of time, and let the job continue with the next step.
Is there somebody who can help me.
Thanks in advance.
Martijn
January 9, 2013 at 10:05 am
This should work...
Create a new job that has "checking" code in it that will monitor the running job. From the first job step of your existing job (or via a scheduled job would work too), fire off this new "job checking job" that can monitor things.
DECLARE @status tinyint, @Elapsed int, @Threshold int, @JobToCheck sysname
SET @JobToCheck = 'MyJobToCheck'
SET @Threshold = 60 * 1 /* Represented in seconds */
/* Assign varibles */
SELECT TOP 1 @status = ISNULL(last_executed_step_id, 0)
FROM msdb.dbo.sysjobactivity
WHERE job_id = (SELECT job_id from msdb.dbo.sysjobs WHERE name = @JobToCheck)
AND (last_executed_step_id = 1 OR last_executed_step_id IS NULL) -->> This is NULL when first step of job is currently executing
ORDER BY run_requested_date DESC
WHILE (@Status = 0)
BEGIN
/* Check job status, and elapsed time */
SELECT TOP 1 @status = ISNULL(last_executed_step_id, 0),
@Elapsed = DATEDIFF(second, start_execution_date, GETDATE())
FROM msdb.dbo.sysjobactivity
WHERE job_id = (SELECT job_id from msdb.dbo.sysjobs WHERE name = @JobToCheck)
ORDER BY run_requested_date DESC
IF @Elapsed > @Threshold
BEGIN
/* Stop, then restart job for the step you want */
EXECUTE msdb.dbo.sp_stop_job @job_name = @JobToCheck
EXECUTE msdb.dbo.sp_start_job @JobToCheck, @step_name = 'Step you want to restart the job at'
BREAK;
END
ELSE
BEGIN
/* Check every minute or so */
WAITFOR DELAY '00:01:00'
END
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 10, 2013 at 12:02 am
Hi MyDoggieJessie,
Thanks for your reply.
I will try this in the next few days.
Martijn
January 10, 2013 at 12:10 am
Hey No problem, it worked for me in my testing so best of luck with it!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply