Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to stop a jobstep and go to the next in a scheduled job. Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 6:41 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Post #1404744
Posted Wednesday, January 9, 2013 10:05 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
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"
Post #1404883
Posted Thursday, January 10, 2013 12:02 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Post #1405225
Posted Thursday, January 10, 2013 12:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
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"
Post #1405229
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse