Stop/start SQL jobs due to maintenance

Sometimes I need to stop alle jobs on a SQL server for some reason, but SQL jobs need to be stoppend and I also want to make sure thas they not automatic start again, until I want them to start.

Therefore I wrote a scipt to disable SQL jobs, log which jobs was disables and waited for alle SQL jobs to finish. After running the script, I can do my maintenance without worrying about if something should be running.

The script creates a table in master database, so if I need to upgrade my SQL server, I choose another database for the table.

USE master
GO

DROP TABLE IF EXISTS DisabledJobs

SELECT j.job_id, IIF(ss.freq_type = 64,1,0) StartAgentStart -- 64 - Starting at agent start
  INTO DisabledJobs
  FROM msdb.dbo.sysjobs j
  JOIN msdb.dbo.sysjobschedules js ON js.job_id = j.job_id
  JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = js.schedule_id
 WHERE j.enabled = 1


DECLARE @job_id UNIQUEIDENTIFIER

DECLARE job_cursor CURSOR READ_ONLY FOR  
SELECT job_id
  FROM DisabledJobs

OPEN job_cursor   
FETCH NEXT FROM job_cursor INTO @job_id  

WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0
   FETCH NEXT FROM job_cursor INTO @job_id  
END

CLOSE job_cursor   
DEALLOCATE job_cursor

DECLARE @RunningJobs INT = 1

WHILE @RunningJobs > 0
BEGIN

  ;WITH last_activity AS (
    SELECT dj.job_id, MAX(session_id) session_id
      FROM DisabledJobs dj
      JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = dj.job_id
     WHERE StartAgentStart = 0
     GROUP by dj.job_id
  )
  SELECT @RunningJobs = COUNT(*)
    FROM last_activity la
    JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = la.job_id AND sja.session_id = la.session_id
    JOIN msdb.dbo.sysjobs j ON j.job_id = la.job_id
   WHERE sja.stop_execution_date IS NULL
     AND sja.start_execution_date > CAST(GETDATE() AS DATE) -- skal være startet i dag
  
  IF @RunningJobs > 0
    WAITFOR DELAY '00:00:05';
END

The script also mark the SQL jobs that starts at SQL Agent startup. This is to make sure that SQL jobs are started again when I enable alle disabled jobs. Especially CDC jobs could be a problem if that is ignored.

So when maintenance is finished I run my script to eneable SQL jobs and start the SQL jobs that needs to be started.

USE master
GO

DECLARE @job_id UNIQUEIDENTIFIER
       ,@StartUp SMALLINT

DECLARE job_cursor CURSOR READ_ONLY FOR  
SELECT j.job_id, dj.StartAgentStart
  FROM DisabledJobs dj
  JOIN msdb.dbo.sysjobs j ON j.job_id = dj.job_id
 WHERE j.enabled = 0

OPEN job_cursor   
FETCH NEXT FROM job_cursor INTO @job_id, @StartUp

WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 1

   IF @StartUp = 1
     EXEC msdb.dbo.sp_start_job @job_id = @job_id

   FETCH NEXT FROM job_cursor INTO @job_id, @StartUp
END

CLOSE job_cursor   
DEALLOCATE job_cursor

DECLARE @DisabledJobs INT

SELECT @DisabledJobs = COUNT(*)
  FROM DisabledJobs dj
  JOIN msdb.dbo.sysjobs j ON j.job_id = dj.job_id
 WHERE j.enabled = 0

IF @DisabledJobs = 0
  DROP TABLE DisabledJobs
ELSE
  SELECT @DisabledJobs