Have you ever needed to make absolutely sure two jobs dont run at the same time in, just schedule to run at different times i hear you say. well in some cases its not that easy. Sometimes you have jobs that run every 20 minutes and others of a similar fashion, there is a chance one job could extend longer for what ever reason, most of the time its unforseen, as in the case i had recently.
So i came up with a solution. A simple Stored Procedure i wrote can, it will live in your master db, why the master i hear you ask. well in order to prevent collasions between databases it is best to live in the master.
1st write a list of the job names you want to prevent from running concurrently, and then follow the instructions here
2nd place a code snippet only once, in the first step of each job, so it runs prior to anything in the job
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Andre Pageot -- Create date: 29 july 09 -- Description: Check for concurrent running jobs -- returns the number of concurrent jobs running -- this procedure resides in the master DB to avoid a collation conflicts -- USEAGE:- -- ammend the job names for the jobs you consider are no allowed to run concurrently -- when writing job step 1 of job name 'Job 1' you must place the following code snippet at the begining of step, this is only required once for each job -- wait for a window to open before we begin -- WHILE (2 > 1) -- BEGIN -- DECLARE @Running bit -- EXEC master.dbo.usp_LCEDependantJobRunning @CurrentExecutingJobName = -- 'Job 1' -- ,@Result = @Running output -- -- IF @Running = 0 BREAK -- if your job runs periodically say every 20 minutes you can exit the job -- in the case more than 1 job is running it is likely there is already a job in teh queue so to speak -- IF @Running > 1 RETURN -- --hold up a second -- WAITFOR DELAY '00:00:01' -- END -- ============================================= ALTER PROCEDURE [dbo].[usp_LCEDependantJobRunning] @Result int output, @CurrentExecutingJobName nvarchar(500) AS BEGIN SET NOCOUNT ON; DECLARE @JobNames table([name] nvarchar(500)) DECLARE @JobIDs Table(Job_ID uniqueidentifier) DECLARE @name SYSNAME SELECT @name = SUSER_SNAME() DECLARE @xp_results TABLE (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, -- BOOL request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, -- BOOL current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) --job_state --Value Description --1 Executing. --2 Waiting for thread. --3 Between retries. --4 Idle. --5 Suspended. --7 Performing completion actions. INSERT INTO @JobNames ([name]) values ('LCE 1 PPC Filters (every 20 mins)') INSERT INTO @JobNames ([name]) values ('LCE 2 PPC Stats, Budget update (every 20 mins)') INSERT INTO @JobNames ([name]) values ('LCE 3 PPC Finalise (midnight)') INSERT INTO @JobNames ([name]) values ('LCE Bot Session DELETE (hourly)') INSERT INTO @JobIDs SELECT [job_id] From msdb.dbo.sysjobs WHERE [name] in (select [name] from @JobNames) AND [name] <> @CurrentExecutingJobName INSERT INTO @xp_results exec master.dbo.xp_sqlagent_enum_jobs 1 , @name IF (SELECT count(*) FROM @JobIDs) > 0 BEGIN -- SELECT -- (SELECT [Name] From msdb.dbo.sysjobs WHERE [Job_ID] = R.Job_ID) AS [Name], -- Job_ID, -- Job_state -- FROM @xp_results R WHERE Job_State <> 4 AND Job_ID = @JobID SELECT @Result = count(Job_ID) FROM @xp_results WHERE Job_State <> 4 AND Job_ID in (SELECT job_id FROM @JobIDs) END ELSE BEGIN -- SELECT -- (SELECT [Name] From msdb.dbo.sysjobs WHERE [Job_ID] = R.Job_ID) AS [Name], -- Job_ID, -- Job_state -- FROM @xp_results R WHERE Job_State <> 4 SELECT @Result = Count(Job_ID) FROM @xp_results WHERE Job_State <> 4 END -- Return the result of the function RETURN @Result END GO |