Toggle Jobs On and Off

This is a useful script if you want to turn a specific list of jobs that are currently enable to a disabled status and vice versa.

Just specify the list of job names in the VALUES section below:

USE msdb
GO

IF OBJECT_ID(N'tempdb..#jobs') IS NOT NULL
BEGIN
    DROP TABLE #jobs
END

CREATE TABLE #jobs
(
    job_name nvarchar(MAX),
)
INSERT INTO #jobs(job_name)
VALUES
    ('Job Name 1 here'),
    ('Job Name 2 here'),
    ('Run index selection Job')

IF OBJECT_ID(N'tempdb..#job_status') IS NOT NULL
BEGIN
    DROP TABLE #job_status
END

SELECT ID = IDENTITY(INT, 1,1),
    [job_name], enabled
INTO #job_status
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
JOIN #jobs j
    ON j.job_name = sJOB.name


DECLARE @job_name nvarchar(MAX) = '';
DECLARE @enabled INT;
DECLARE @iter INT = (SELECT MAX(ID) FROM #job_status);

WHILE @iter > 0
BEGIN
    SET @job_name = ''

    SELECT @job_name = job_name,
        @enabled = CASE WHEN [enabled] = 0 THEN 1 ELSE 0 END
    FROM #job_status
    WHERE ID = @iter;

    EXEC msdb.dbo.sp_update_job @job_name = @job_name,    @enabled = @enabled ;

    PRINT CAST(@job_name AS VARCHAR) + ':' + CAST(@enabled AS VARCHAR)
    SET @iter -= 1;

END

Featured Articles

 Site Author

  • Thanks for visiting!