Search

Saturday, September 24, 2011

View and Disable SQL Agent Jobs with TSQL


I can view all the SQL Agent Jobs in SSMS. But I want to Copy/Paste this in MS Excel. I Wrote the below script with the Help from sysschedules in BOL. 


Declare @Days Table (
      DayID      int
    , DayName VarChar(32)
);
Insert Into @Days
SELECT 1, 'Sunday' UNION ALL
SELECT 2, 'Monday' UNION ALL
SELECT 4, 'Tuesday' UNION ALL
SELECT 8, 'Wednesday' UNION ALL
SELECT 16, 'Thursday' UNION ALL
SELECT 32, 'Friday' UNION ALL
SELECT 64, 'Saturday';

With MyJob
As(
    SELECT Sch.Name AS 'SchName'
        , Sch.Schedule_ID
        , JobSch.Job_ID
        , CASE WHEN Sch.Freq_Type = 1 THEN 'Once' 
            WHEN Sch.Freq_Type = 4 
                AND Sch.Freq_Interval = 1 
                    THEN 'Daily'
            WHEN Sch.Freq_Type = 4 
                THEN 'Every ' + CAST(Sch.Freq_Interval AS VarChar(5)) + ' Days'
            WHEN Sch.Freq_Type = 8 THEN 
                Replace( Replace( Replace(( 
                    SELECT DayName 
                    From @Days AS x 
                    Where Sch.Freq_Interval & x.DayID <> 0 
                    Order By DayID For XML Raw)
                , '"/><row DayName="', ', '), '<row DayName="', ''), '"/>', '') 
                + CASE WHEN Sch.Dreq_Recurrence_Factor <> 0 
                        AND Sch.Dreq_Recurrence_Factor = 1 
                            THEN '; weekly' 
                    WHEN Sch.Dreq_Recurrence_Factor <> 0 THEN '; every ' 
                + CAST(Sch.Dreq_Recurrence_Factor AS VarChar(10)) + ' weeks' End
            WHEN Sch.Freq_Type = 16 THEN 'On day ' 
                + CAST(Sch.Freq_Interval AS VarChar(10)) + ' of every '
                + CAST(Sch.Dreq_Recurrence_Factor AS VarChar(10)) + ' months' 
            WHEN Sch.Freq_Type = 32 THEN 
                CASE WHEN Sch.Freq_Relative_Interval = 1 THEN 'First'
                    WHEN Sch.Freq_Relative_Interval = 2 THEN 'Second'
                    WHEN Sch.Freq_Relative_Interval = 4 THEN 'Third'
                    WHEN Sch.Freq_Relative_Interval = 8 THEN 'Fourth'
                    WHEN Sch.Freq_Relative_Interval = 16 THEN 'Last'
                END + 
                CASE WHEN Sch.Freq_Interval = 1 THEN ' Sunday'
                    WHEN Sch.Freq_Interval = 2 THEN ' Monday'
                    WHEN Sch.Freq_Interval = 3 THEN ' Tuesday'
                    WHEN Sch.Freq_Interval = 4 THEN ' Wednesday'
                    WHEN Sch.Freq_Interval = 5 THEN ' Thursday'
                    WHEN Sch.Freq_Interval = 6 THEN ' Friday'
                    WHEN Sch.Freq_Interval = 7 THEN ' Saturday'
                    WHEN Sch.Freq_Interval = 8 THEN ' Day'
                    WHEN Sch.Freq_Interval = 9 THEN ' Weekday'
                    WHEN Sch.Freq_Interval = 10 THEN ' Weekend'
                END
                + CASE WHEN Sch.Dreq_Recurrence_Factor <> 0 
                        AND Sch.Dreq_Recurrence_Factor = 1 THEN '; monthly'
                    WHEN Sch.Dreq_Recurrence_Factor <> 0 THEN '; every ' 
                + CAST(Sch.Dreq_Recurrence_Factor AS VarChar(10)) + ' months' End
            WHEN Sch.Freq_Type = 64 THEN 'StartUp'
            WHEN Sch.Freq_Type = 128 THEN 'Idle'
          END AS 'Frequency'
        , IsNull('Every ' + CAST(Sch.freq_subday_interval AS VarChar(10)) + 
            CASE WHEN Sch.freq_subday_type = 2 THEN ' seconds'
                WHEN Sch.freq_subday_type = 4 THEN ' minutes'
                WHEN Sch.freq_subday_type = 8 THEN ' hours'
            END, 'Once') AS 'SubFrequency'
        , Replicate('0', 6 - Len(Sch.active_start_time)) 
            + CAST(Sch.active_start_time AS VarChar(6)) AS 'startTime'
        , Replicate('0', 6 - Len(Sch.active_end_time)) 
            + CAST(Sch.active_end_time AS VarChar(6)) AS 'endTime'
        , Replicate('0', 6 - Len(JobSch.next_run_time)) 
            + CAST(JobSch.next_run_time AS VarChar(6)) AS 'nextRunTime'
        , CAST(JobSch.next_run_date AS char(8)) AS 'nextRunDate'
    From msdb.dbo.sysschedules AS sch
    JOIN msdb.dbo.sysjobschedules AS jobsch
        ON Sch.schedule_id = JobSch.schedule_id
    WHERE Sch.enabled = 1
)

Script to Disable the Job. 


SELECT Job.Name AS 'JobName'
    , Sch.SchName
    , Sch.Frequency
    , Sch.SubFrequency
    , SubString(Sch.StartTime, 1, 2) + ':' 
        + SubString(Sch.StartTime, 3, 2) + ' - ' 
        + SubString(Sch.EndTime, 1, 2) + ':' 
        + SubString(Sch.EndTime, 3, 2) 
        AS 'ScheduleTime' -- HH:MM
    , SubString(Sch.NextRunDate, 1, 4) + '/' 
        + SubString(Sch.NextRunDate, 5, 2) + '/' 
        + SubString(Sch.NextRunDate, 7, 2) + ' ' 
        + SubString(Sch.NextRunTime, 1, 2) + ':' 
        + SubString(Sch.NextRunTime, 3, 2) AS 'NextRunDate'
      /* Note: the sysjobschedules table refreshes every 20 min, 
        so nextRunDate may be out of date */
    , 'Execute msdb.dbo.sp_update_job @job_id = ''' 
        + CAST(Job.job_id AS char(36)) + ''', @enabled = 0;' AS 'disableScript'
From msdb.dbo.sysjobs AS Job
Join MyJob AS sch
    On Job.Job_ID = Sch.Job_ID
Where Job.enabled = 1 -- do not display disabled jobs
Order By NextRunDate;

No comments:

Post a Comment