Thursday, April 19, 2012

Use this script to generate a SQL Server Job Report


Have you ever wanted to create a report listing all of the SQL Server jobs on your SQL Server 2005 or higher servers? This is easy if you use the script I have created just for this purpose. The script creates this report by joining the sysjobs and sysjobschedules tables and creating a resultset.
To make the report easy to read, I have had to substitute the various integer values found in these tables with readable values. The first part of the stored procedure generates the list of jobs and the days to run for weekly scheduled jobs. The second part is written with CASE statements to incorporate all the possible combinations listed under the sysjobschedules table.
Making the Data Readable
The following are the various numeric entries and their string values, used in the stored procedure, to make it more readable.

freq_type int Frequency of the schedule execution:
1 = Once
4 = Daily
8 = Weekly
16 = Monthly
32 = Monthly relative
64 = Execute when SQL Server

freq_interval int Value indicating on which days the schedule runs.

If freq_type is 4 (daily), the value is every freq_interval days.

If freq_type is 8 (weekly), the value is a bitmask indicating the days in which weekly schedules are run. The freq_interval values are:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

If freq_type is 16 (monthly), the value is freq_interval day of the month.

If freq_type is 32 (monthly relative), freq_interval can be one of these values:
1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
8 = Day
9 = Weekday
10 = Weekend day

freq_subday_type int Units for the freq_subday_interval:
1 = At the specified time
2 = Seconds
4 = Minutes
8 = Hours

freq_subday_interval int Number of freq_subday_type periods to occur between each scheduled execution of the job.

freq_relative_interval int Scheduled job’s occurrence of the freq_interval in each month when freq_type is 
32 (monthly relative):
1 = First
2 = Second
4 = Third
8 = Fourth
16 = Last
========================================================================
DECLARE @x             int
      , @y             int
      , @z             int
      , @counter       smallint
      , @days          varchar(100)
      , @day           varchar(10)
      , @Job_Name      sysname
      , @freq_interval int
      , @Job_ID        varchar(50)

SET NOCOUNT ON

CREATE TABLE #temp (Job_ID   varchar(50)
                   ,Job_Name sysname
                   ,Jdays    varchar(100))

--–This cursor runs throough all the jobs that have a weekly frequency running on different days

DECLARE Job_Cursor CURSOR
    FOR
      SELECT t1.job_id        AS [Job_id]
           , t2.name          AS [Name]
           , t2.freq_interval AS [freq_interval]
        FROM msdb.dbo.sysjobschedules t1
        JOIN msdb..sysschedules t2 ON t1.schedule_id = t2.schedule_id
       WHERE freq_type = 8

OPEN Job_Cursor
FETCH NEXT FROM Job_Cursor
 INTO @Job_ID
    , @Job_Name
    , @freq_interval

WHILE @@fetch_status = 0
BEGIN
  SELECT @counter = 0
       , @x       = 64
       , @y       = @freq_interval
       , @z       = @y
       , @days    = ''
       , @day     = ''

  WHILE @y <> 0
  BEGIN
    SELECT @y = @y - @x
         , @counter = @counter + 1
    IF @y < 0 
    BEGIN
      SET @y = @z
      GOTO start
    END
    SELECT @day = CASE @x
                    WHEN 1
                      THEN 'Sunday'
                    WHEN 2
                      THEN 'Monday'
                    WHEN 4
                      THEN 'Tuesday'
                    WHEN 8
                      THEN 'Wednesday'
                    WHEN 16
                      THEN 'Thursday'
                    WHEN 32
                      THEN 'Friday'
                    WHEN 64
                      THEN 'Saturday'
                  END
    SELECT @days = @day
                 + ','
                 + @days
    start:
    SELECT @x = CASE @counter
                  WHEN 1
                    THEN 32
                  WHEN 2
                    THEN 16
                  WHEN 3
                    THEN 8
                  WHEN 4
                    THEN 4
                  WHEN 5
                    THEN 2
                  WHEN 6
                    THEN 1
                END

    SET @z = @y
    IF @y = 0
      BREAK
  END

  INSERT INTO #temp
       SELECT @Job_ID
            , @Job_Name
            , left(@days, len(@days)-1)
  FETCH NEXT FROM Job_Cursor
   INTO @Job_ID
      , @Job_Name
      , @freq_interval
END
CLOSE Job_Cursor
DEALLOCATE Job_Cursor

--–Final query to extract complete information by joining sysjobs, sysschedules and #temp table

SELECT @@servername    AS [Instance]
     , t3.name         AS [Job_Name]
     , CASE t3.enabled 
         WHEN 1
           THEN 'Enabled'
           ELSE 'Disabled'
       END             AS [JobEnabled]
     , t2.name         AS [Schedule_Name]
     , CASE t2.enabled 
         WHEN 1
           THEN 'Enabled'
           ELSE 'Disabled'
       END             AS [ScheduleEnabled]
     , CASE freq_type 
         WHEN 1
           THEN 'Once'
         WHEN 4
           THEN 'Daily'
         WHEN 8
           THEN 'Weekly'
         WHEN 16
           THEN 'Monthly'
              + cast(freq_interval AS char(2))
              + 'th Day'
         WHEN 32
          THEN 'Monthly Relative'
         WHEN 64
           THEN 'Execute when SQL Server Agent Starts'
       END             AS [Job Frequency]
     , CASE freq_type 
         WHEN 32
           THEN CASE freq_relative_interval
                  WHEN 1
                    THEN 'First'
                  WHEN 2
                    THEN 'Second'
                  WHEN 4
                    THEN 'Third'
                  WHEN 8
                    THEN 'Fourth'
                  WHEN 16
                    THEN 'Last'
                END
           ELSE ''
       END             AS [Monthly Frequency]
     , CASE freq_type
         WHEN 16
           THEN cast(freq_interval AS char(2))
              + 'th Day of Month'
         WHEN 32
           THEN CASE freq_interval 
                  WHEN 1
                    THEN 'Sunday'
                  WHEN 2
                    THEN 'Monday'
                  WHEN 3
                    THEN 'Tuesday'
                  WHEN 4
                    THEN 'Wednesday'
                  WHEN 5
                    THEN 'Thursday'
                  WHEN 6
                    THEN 'Friday'
                  WHEN 7
                    THEN 'Saturday'
                  WHEN 8
                    THEN 'Day'
                  WHEN 9
                    THEN 'Weekday'
                  WHEN 10
                    THEN 'Weekend day'
                END
         WHEN 8
           THEN t4.Jdays
           ELSE ''
       END             AS [Runs On]
     , CASE freq_subday_type
         WHEN 1
           THEN 'At the specified Time'
         WHEN 2
           THEN 'Seconds'
         WHEN 4
           THEN 'Minutes'
         WHEN 8
           THEN 'Hours'
       END             AS [Interval Type]
     , CASE freq_subday_type 
         WHEN 1
           THEN 0
           ELSE freq_subday_interval 
       END             AS [Time Interval]
     , CASE freq_type 
         WHEN 8
           THEN cast(freq_recurrence_factor AS char(2))
              + ' Week'
         WHEN 16
           THEN cast(freq_recurrence_factor AS char(2))
              + ' Month'
         WHEN 32
           THEN cast(freq_recurrence_factor AS char(2))
              + ' Month'
           ELSE ''
       END             AS [Occurs Every]
     , left(active_start_date,4)
     + '-'
     + substring(cast(active_start_date AS char),5,2) 
     + '-'
     + right(active_start_date,2) [Begin Date-Executing Job]
     , left(REPLICATE('0', 6-len(active_start_time))
     + cast(active_start_time AS char(6)),2)
     + ':'
     + substring(REPLICATE('0', 6-len(active_start_time))
     + cast(active_start_time AS char(6)),3,2)
     + ':'
     + substring(REPLICATE('0', 6-len(active_start_time))
     + cast(active_start_time AS char(6)),5,2)
                       AS [Executing At]
     , left(active_end_date,4)
     + '-'
     + substring(cast(active_end_date AS char),5,2) 
     + '-'
     + right(active_end_date,2)
                       AS [End Date-Executing Job]
     , left(REPLICATE('0', 6-len(active_end_time))
     + cast(active_end_time AS char(6)),2)
     + ':'
     + substring(REPLICATE('0', 6-len(active_end_time))
     + cast(active_end_time AS char(6)),3,2)
     + ':'
     + substring(REPLICATE('0', 6-len(active_end_time))
     + cast(active_end_time AS char(6)),5,2)
                       AS [End Time-Executing Job]
     , t3.date_created  AS [Job Created]
     , t2.date_created AS [Schedule Created] 
  FROM msdb..sysjobschedules t1 
  JOIN msdb..sysschedules t2       ON t1.schedule_id = t2.schedule_id
 RIGHT OUTER JOIN msdb..sysjobs t3 ON t1.job_id = t3.job_id
  LEFT OUTER JOIN #temp t4         ON t2.name = t4.Job_Name
                                  AND t1.job_id = t4.Job_ID
 ORDER BY 1

DROP TABLE #temp

========================================================================

Please let me know if you have any issues with the script.


No comments:

Post a Comment