|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Thursday, January 24, 2013 3:52 AM
Points: 8,
Visits: 42
|
|
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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:19 PM
Points: 2,037,
Visits: 3,761
|
|
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; They'll drag you down to their level and beat you with experience"
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Thursday, January 24, 2013 3:52 AM
Points: 8,
Visits: 42
|
|
Hi MyDoggieJessie,
Thanks for your reply. I will try this in the next few days.
Martijn
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:19 PM
Points: 2,037,
Visits: 3,761
|
|
Hey No problem, it worked for me in my testing so best of luck with it!
______________________________________________________________________________ "Never argue with an idiot; They'll drag you down to their level and beat you with experience"
|
|
|
|