Disable all the SQL jobs at once

Rédigé par M Sozezzo - - Aucun commentaire

When you have several sql jobs that need to be disabled all.
If you can, you can just turn off SQL Agent but, sometime, it is not possible.

This script generates the script to enable and disable.

--generate disable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs

--generate enable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs


When you have your jobs organized by category, you can use this script:
-------------------------------------
-- Script to enable or disable Jobs
-- Filter by Category
-------------------------------------
DECLARE @Categoryname AS NVARCHAR(100) = null;
DECLARE @newJobStatus AS NVARCHAR(1) = '0';

SET NOCOUNT ON

DECLARE @JobName AS NVARCHAR(255);
DECLARE @sqlEnable AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);
DECLARE @isEnabled AS NVARCHAR(1);

SET @sql = 'EXEC msdb.dbo.sp_update_job @job_name = N''${JobName}'', @enabled = ' + @newJobStatus + ';'
BEGIN TRY drop table #jobs; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
SELECT replace(@sql, '${JobName}', sysjobs.NAME) AS sqlEnable
,      sysjobs.NAME                              AS JobName
,      job_id                                  
,      [enabled]                                    isEnabled
INTO #jobs
-- SELECT sysjobs.name 'Job Name' , job_id, [enabled] isEnabled, syscategories.name
FROM       msdb.dbo.sysjobs     
INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
WHERE
    (syscategories.NAME = @Categoryname OR @Categoryname IS NULL) AND NOT ([enabled] = @newJobStatus)

WHILE ( EXISTS ( SELECT JobName    FROM #jobs ) )
BEGIN

    SELECT TOP 1 @JobName = JobName
    ,            @sqlEnable = sqlEnable
    ,            @isEnabled = isEnabled
    FROM #jobs;

    DELETE FROM #jobs
    WHERE
        JobName = @JobName;
   
    print @sqlEnable;
    EXEC (@sqlEnable)

END


Source : https://www.sqlservercentral.com/Forums/Topic1214637-391-1.aspx

Fil RSS des articles de ce mot clé