Sql-server – How to prevent parallel running of SQL Agent jobs

jobssql serversql-server-2016

What would be good ways to design a group of jobs such that they can never run at the same time?

I am looking for a generic concept, like a semaphore, which makes it easier to ensure that some jobs are never running at the same time.

For example, I'm thinking of a "signal table". When a job starts, it sets the signal and at the end, it resets the signal. In addition, every job needs to wait until the signal is set. (How to implement a CPU friendly waiting?) However, when a job crashes, there must then be a solution to automatically reset the signal, otherwise jobs would never start.

We have 4 jobs which are doing various updates, imports, calculations. They all are running by scheduler, and in addition, some of them can also be started manually by super users in case a special run is needed. However, they should not run at the same time, because it often results then in locks or even deadlocks and the database does not properly respond to the users then.

EDIT: It's a data warehouse which is gathering and consolidating data from multiple external data sources. Every job is responsible for another data source, like this:

job #1 is running every 15 minutes
job #2 every 3 hours
job #3 and Job #4 run once per day or have extra runs when started by super users.

Best Answer

Aside from the comments, here's something you can use to accomplish what you're trying to do (since I'm not a fan of reinventing the wheel for locking systems).

You can use the system stored procedure msdb.dbo.sp_help_job to determine if your other jobs are running as the first step in each job. Then from there you can choose what logic you want it to take next when another job is running such as ending the job (if it's on a routine schedule) or implement a mock sleep using WAITFOR DELAY ('00:00:01') -- Sleeps for 1 second, which you can put inside a WHILE loop to recheck job status.

You can call the sp_help_job by passing in the job name:

EXEC msdb.dbo.sp_help_job @Job_name = 'TheJobName'

And you can use the columns current_execution_status and current_execution_step of the procedure's result set, to help you determine when it's safe to run the current job.

There's also some system job tables that might be helpful to you to query as well, which you can read up on in this StackOverflow answer.

Related Solutions

Sql-server – Can you Schedule a job inside another job

In more research and diving into the msdn site documentation I have found and am now using.

sp_add_schedule, sp_attach_schedule and sp_detach_schedule, sp_delete_schedule

so when the job sees that others are running ATM I am doing this:

exec msdb.dbo.sp_add_schedule [MSDN link][1]
   @schedule_name = @TempScheduleName,
   @enabled = 1,
   @freq_interval = 1,
   @freq_type = 1,
   @freq_subday_type = 4,
   @freq_recurrence_factor = 1,
   @freq_subday_interval = 5,
   @active_start_date = @todaysdate,
   @active_start_time = @todaystime (plus 5min)


exec msdb.dbo.sp_attach_schedule [MSDN Link][2]
   @job_name = @JobName',
   @schedule_name = @TempScheduleName

then the next time the job is run this is also done to remove the temp schedule:

   exec msdb.dbo.sp_detach_schedule
    @job_name = @JobName,
    @schedule_name = @TempScheduleName

   exec msdb.dbo.sp_delete_schedule
   @schedule_name = @TempScheduleName

Sql-server – SQL Server Agent Jobs – Long running job delaying other jobs

An easy workaround solution would be to nudge either of the jobs to start earlier/later and to not run together with the other job.

E.g.

    Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
    Job 2 Schedule:                        01:00, .......

It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table (possibly: IX, IU) before the Job 1 is able to acquire its required locks on the same users table. Then Job 1 has to wait.

You are encountering blocking as previously observed by @MaxVernon. You could run the following script at the full hour to see if you can catch the statements involved:

SELECT sp.spid,
       sp.kpid,
       sp.blocked,
       sp.hostprocess,
       sp.waittype,
       sp.lastwaittype,
       DB_NAME(sp.dbid),
       sp.cpu,
       sp.physical_io,
       sp.memusage,
       CASE 
            WHEN sp.blocked = 0 THEN sp.spid
            WHEN sp.blocked != 0 THEN blocked
       END               AS chain,
       dest.text
FROM   sys.sysprocesses  AS sp
       OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS dest
WHERE  spid > 50
       AND spid <> @@spid
       AND (
               spid IN (SELECT blocked
                        FROM   sys.sysprocesses
                        WHERE  blocked != 0
                               AND spid > 50
                               AND spid != @@spid)
               OR blocked != 0
           )

The output will show statements that are blocking or blocked. You will probably find that the statements are from the Job 1 and Job 2.

Possible solutions:

Adding a NOLOCK hint can help, but should be avoided if possible.
Then you could also consider optimising the code so that the steps in Job 2 run faster.
Changing the schedules of the Job 1 and/or Job 2.

Best Answer

Related Solutions

Sql-server – Can you Schedule a job inside another job

Sql-server – SQL Server Agent Jobs – Long running job delaying other jobs

Possible solutions:

Related Question