Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Generate SQL Agent Job Schedule Report

MSSQLTips author Ken Simmons By:   |   Read Comments (16)   |   Related Tips: More > SQL Server Agent
Problem

I am trying to come up with a list of Job Schedules, so I can make sure that my Jobs are staggered. Is there a way to query the system tables, so I do not have to manually keep track of the schedules?

Solution

The msdb database has a stored procedure called sp_get_schedule_description that can be used to translate schedules into readable descriptions, but this only accepts the parameters of one job at a time. If you create a function using the code from the stored procedure, you can pass in the correct parameters using the sysjobs tables and the function will return a readable description.

First create the following function in the msdb database:

USE msdb
GO
CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,
  @freq_interval INT ,
  @freq_subday_type INT ,
  @freq_subday_interval INT ,
  @freq_relative_interval INT ,
  @freq_recurrence_factor INT ,
  @active_start_date INT ,
  @active_end_date INT,
  @active_start_time INT ,
  @active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT

IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7)
BEGIN
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '
SELECT @loop = @loop + 1
END
IF (RIGHT(@schedule_description, 2) = N', ')
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
END
IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )

RETURN @schedule_description
END

Next, run one of the following queries to return the job schedule information.  

SQL Server 2000  

SELECT sysjobs.nameCAST((sysjobschedules.active_start_time 10000AS VARCHAR(10)) + ':' +
RIGHT('00' CAST((sysjobschedules.active_start_time 10000) / 100 AS VARCHAR(10)),2active_start_time
dbo.udf_schedule_description(sysjobschedules.freq_type
sysjobschedules.freq_interval

sysjobschedules.freq_subday_type
sysjobschedules.freq_subday_interval

sysjobschedules.freq_relative_interval

sysjobschedules.freq_recurrence_factor
sysjobschedules.active_start_date
sysjobschedules.active_end_date
sysjobschedules.active_start_time
sysjobschedules.active_end_timeAS ScheduleDscrsysjobs.enabled
FROM sysjobs INNER JOIN
sysjobschedules ON sysjobs.job_id sysjobschedules.job_id 

SQL Server 2005  

SELECT dbo.sysjobs.nameCAST(dbo.sysschedules.active_start_time 10000 AS VARCHAR(10))  
':' RIGHT('00' CAST(dbo.sysschedules.active_start_time 10000 100 AS VARCHAR(10)), 2AS active_start_time,  
dbo.udf_schedule_description(dbo.sysschedules.freq_type
dbo.sysschedules.freq_interval
dbo.sysschedules.freq_subday_type
dbo.sysschedules.freq_subday_interval
dbo.sysschedules.freq_relative_interval
dbo.sysschedules.freq_recurrence_factor
dbo.sysschedules.active_start_date
dbo.sysschedules.active_end_date
dbo.sysschedules.active_start_time
dbo.sysschedules.active_end_timeAS ScheduleDscrdbo.sysjobs.enabled 
FROM dbo.sysjobs INNER JOIN 
dbo.sysjobschedules ON dbo.sysjobs.job_id dbo.sysjobschedules.job_id INNER JOIN 
dbo.sysschedules ON dbo.sysjobschedules.schedule_id dbo.sysschedules.schedule_id  

The following is a sample result set.    

sql server sql agent job report

Next Steps


Last Update: 11/7/2008


About the author
MSSQLTips author Ken Simmons
Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, November 07, 2008 - 12:50:08 PM - tcronin@cmsstl.com Read The Tip

Outstanding, deployed it to all my servers already


Wednesday, November 12, 2008 - 4:31:42 AM - JimR Read The Tip

I typically think that staggering schedules is not a very accurate way to ensure that jobs don't overlap. If several processes need to run one after the other, can't one simply put them under a cover procedure that calls them successively? Or is there something about jobs that precludes this?


Wednesday, November 12, 2008 - 7:13:22 AM - KenSimmons Read The Tip

A stored procedure is a better solution for processes that need to run successively.  This will just help check to make sure that over time, you have not scheduled all your jobs around the same time and may show you some open slots that you may be able to move some schedules to.


Friday, November 21, 2008 - 7:07:56 AM - casilvis Read The Tip

I modified it with a case statement to add a leading zero to the time so that I could sort the output by time.

active_start_time = case len(CAST((dbo.sysschedules.active_start_time / 10000) AS VARCHAR(10)))

when 1 then '0' + CAST((dbo.sysschedules.active_start_time / 10000) AS VARCHAR(10)) + ':' + RIGHT('00' + CAST((dbo.sysschedules.active_start_time % 10000) / 100 AS VARCHAR(10)),2)

else CAST((dbo.sysschedules.active_start_time / 10000) AS VARCHAR(10)) + ':' + RIGHT('00' + CAST((dbo.sysschedules.active_start_time % 10000) / 100 AS VARCHAR(10)),2)

end

...

order by dbo.sysschedules.active_start_time

 


Friday, November 21, 2008 - 7:30:26 AM - Dr DBA Read The Tip

 Here is my version of code that does the same thing but produces even more information.

 Here are columns produced

Server
Job  Name
Schedule  Name  
Enabled
   Frequency
   Interval   
Time
Next Run Time

 

 

select
'Server'       = left(@@ServerName,20),
'JobName'      = left(S.name,30),
'ScheduleName' = left(ss.name,25),
'Enabled'      = CASE (S.enabled)
                  WHEN 0 THEN 'No'
                  WHEN 1 THEN 'Yes'
                  ELSE '??'
                END,
'Frequency'    = CASE(ss.freq_type)
                  WHEN 1  THEN 'Once'
                  WHEN 4  THEN 'Daily'
                  WHEN 8  THEN
                    (case when (ss.freq_recurrence_factor > 1)
                        then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
                  WHEN 16 THEN
                    (case when (ss.freq_recurrence_factor > 1)
                    then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
                  WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
                  WHEN 64 THEN 'SQL Startup'
                  WHEN 128 THEN 'SQL Idle'
                  ELSE '??'
                END,
'Interval'    = CASE
                 WHEN (freq_type = 1)                       then 'One time only'
                 WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
                 WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
                 WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
                       from (select ss.schedule_id,
                     freq_interval,
                     'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,
                     'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,
                     'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,
                     'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,
                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                 from msdb..sysschedules ss
                                where freq_type = 8
                           ) as F
                       where schedule_id = sj.schedule_id
                                            )
                 WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
                 WHEN (freq_type = 32) then (select freq_rel + WDAY
                    from (select ss.schedule_id,
                                 'freq_rel' = CASE(freq_relative_interval)
                                                WHEN 1 then 'First'
                                                WHEN 2 then 'Second'
                                                WHEN 4 then 'Third'
                                                WHEN 8 then 'Fourth'
                                                WHEN 16 then 'Last'
                                                ELSE '??'
                                              END,
                                'WDAY'     = CASE (freq_interval)
                                                WHEN 1 then ' Sun'
                                                WHEN 2 then ' Mon'
                                                WHEN 3 then ' Tue'
                                                WHEN 4 then ' Wed'
                                                WHEN 5 then ' Thu'
                                                WHEN 6 then ' Fri'
                                                WHEN 7 then ' Sat'
                                                WHEN 8 then ' Day'
                                                WHEN 9 then ' Weekday'
                                                WHEN 10 then ' Weekend'
                                                ELSE '??'
                                              END
                            from msdb..sysschedules ss
                            where ss.freq_type = 32
                         ) as WS
                   where WS.schedule_id =ss.schedule_id
                   )
               END,
'Time' = CASE (freq_subday_type)
                WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
                WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
                WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
                WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
                ELSE '??'
              END,

'Next Run Time' = CASE SJ.next_run_date
                   WHEN 0 THEN cast('n/a' as char(10))
                   ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
                 END
 
   from msdb.dbo.sysjobschedules SJ
   join msdb.dbo.sysjobs         S  on S.job_id       = SJ.job_id
   join msdb.dbo.sysschedules    SS on ss.schedule_id = sj.schedule_id
order by S.name


 


Saturday, November 22, 2008 - 11:58:32 AM - jpizano Read The Tip

This is an excellent utility. We had 50 jobs that we immediately were able to get our hands around.  Thanks!


Monday, November 24, 2008 - 6:58:23 AM - Dr DBA Read The Tip

Would anyone be interested in using my script in a automated process that will produce a report for all your SQL servers? Let me know as I'm thinking about writing an article.

:)

 


Wednesday, March 07, 2012 - 11:15:02 AM - Kish Adurty Read The Tip

Excellent Script: very useful !   can be used for reporting purpose & server migrations:  Thank you..

 

 


Friday, May 11, 2012 - 5:57:17 AM - Elaelian Read The Tip

Exactly what I was looking for ! Thank you :D


Thursday, May 24, 2012 - 11:06:33 AM - Ludo Bernaerts Read The Tip

Excelent script, very usefull.

I modified it a little bit so it gives you also an idea about the acerage duration

 

 

select

 

'Server'

 

=left(@@ServerName,20

),

 

 

'JobName'

 

=left(S.name,30

),

 

 

'ScheduleName'

 

=left(ss.name,25

),

 

 

'Enabled'

 

=CASE (S.enabled

)

 

WHEN 0 THEN'No'WHEN 1 THEN'Yes'ELSE'??'END,

'Frequency'

 

=CASE(ss.freq_type

)

 

WHEN 1 THEN'Once'WHEN 4 THEN'Daily'WHEN 8 THEN

(casewhen (ss.freq_recurrence_factor > 1) then'Every '+convert(varchar(3),ss.freq_recurrence_factor)+' Weeks'else'Weekly'end)

WHEN 16 THEN

(casewhen (ss.freq_recurrence_factor > 1) then'Every '+convert(varchar(3),ss.freq_recurrence_factor)+' Months'else'Monthly'end)

WHEN 32 THEN'Every '+convert(varchar(3),ss.freq_recurrence_factor)+' Months'-- RELATIVEWHEN 64 THEN'SQL Startup'WHEN 128 THEN'SQL Idle'ELSE'??'END,

'Interval'

 

=

CASE

 

WHEN (freq_type = 1)then'One time only'WHEN (freq_type = 4 and freq_interval = 1)then'Every Day'WHEN (freq_type = 4 and freq_interval > 1)then'Every '+convert(varchar(10),freq_interval)+' Days'WHEN (freq_type = 8)then (select'Weekly Schedule'= D1+ D2+D3+D4+D5+D6+D7 from (select ss.schedule_id,

freq_interval

,'D1'=CASEWHEN (freq_interval & 1 <> 0)then'Sun 'ELSE''END,'D2'=CASEWHEN (freq_interval & 2 <> 0)then'Mon 'ELSE''END,'D3'=CASEWHEN (freq_interval & 4 <> 0)then'Tue 'ELSE''END,'D4'=CASEWHEN (freq_interval & 8 <> 0)then'Wed 'ELSE''END,'D5'=CASEWHEN (freq_interval & 16 <> 0)then'Thu 'ELSE''END,'D6'=CASEWHEN (freq_interval & 32 <> 0)then'Fri 'ELSE''END,'D7'=CASEWHEN (freq_interval & 64 <> 0)then'Sat 'ELSE''ENDfrom msdb..sysschedules sswhere freq_type = 8)as Fwhere schedule_id = sj.schedule_id)WHEN (freq_type = 16)then'Day '+convert(varchar(2),freq_interval)WHEN (freq_type = 32)then (select freq_rel + WDAY from (select ss.schedule_id,'freq_rel'=CASE(freq_relative_interval)WHEN 1 then'First'WHEN 2 then'Second'WHEN 4 then'Third'WHEN 8 then'Fourth'WHEN 16 then'Last'ELSE'??'END,'WDAY'=CASE (freq_interval)WHEN 1 then' Sun'WHEN 2 then' Mon'WHEN 3 then' Tue'WHEN 4 then' Wed'WHEN 5 then' Thu'WHEN 6 then' Fri'WHEN 7 then' Sat'WHEN 8 then' Day'WHEN 9 then' Weekday'WHEN 10 then' Weekend'ELSE'??'ENDfrom msdb..sysschedules sswhere ss.freq_type = 32)as WS where WS.schedule_id =ss.schedule_id)else'n/a'END,

'Time'

 

=CASE (freq_subday_type

)

 

WHEN 1 thenleft(stuff((stuff((replicate('0', 6 -len(Active_Start_Time)))+convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)WHEN 2 then'Every '+convert(varchar(10),freq_subday_interval)+' seconds'WHEN 4 then'Every '+convert(varchar(10),freq_subday_interval)+' minutes'WHEN 8 then'Every '+convert(varchar(10),freq_subday_interval)+' hours'ELSE'??'END,

'Next Run Time'

 

=CASE SJ.next_run_dateWHEN 0 THENcast('n/a'aschar(10))

ELSEconvert(char(10),convert(datetime,convert(char(8),SJ.next_run_date)),120)+' '+left(stuff((stuff((replicate('0', 6 -len(next_run_time)))+convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)END,

'Avg Job Duration'

 

=Case WHEN JH.Avg_Duration ISNULLthencast('n/a'aschar(10))

elsecast(JH.Avg_Duration asCHAR(10))END

 

from msdb.dbo.sysjobschedules SJ join msdb.dbo.sysjobs S on S.job_id = SJ.job_idjoin msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_idleftjoin(select job_id as JobId,AVG(run_duration)as Avg_duration from msdb.dbo.sysjobhistory groupby job_id)as JH on S.job_id = JH.jobid

order

 

by S.name

 

 


Tuesday, May 29, 2012 - 2:00:46 AM - sneha Read The Tip

awesome... very usefull for me thx a lot... :)


Friday, August 10, 2012 - 1:04:48 PM - marcello miorelli Read The Tip

 

/*---------------------------------------------------------------------------------------------------------*\

  this script is based on Ken Simmons article

  the article can be found here in the following link:

  http://www.mssqltips.com/sqlservertip/1622/generate-sql-agent-job-schedule-report/

 

  it is a great tool to find out which jobs are running on the servers.

  it has also extra info about jobs.

 

  Marcelo Miorelli jjradha@yahoo.it

  10-Aug-2012 - Sri Krishna Janmasthami Day

 

\*---------------------------------------------------------------------------------------------------------*/

 

select 'Server'=left(@@ServerName,50),

 

       'JobName'=left(S.name,108),

 

       'Category' = coalesce(cat.name, '??'),

 

       'ScheduleName'=left(ss.name,50),

 

       'Enabled'=

          CASE (S.enabled)

            WHEN 0 THEN'No'

            WHEN 1 THEN'Yes'

            ELSE '??'

          END,

 

       'Frequency'=

          CASE(ss.freq_type)

           WHEN 1 THEN'Once'

           WHEN 4 THEN'Daily'

           WHEN 8 THEN 

                       ( case when (ss.freq_recurrence_factor > 1) then 

                                  'Every ' + convert(varchar(3),ss.freq_recurrence_factor)+ ' Weeks'

                         else 'Weekly' end )

 

           WHEN 16 THEN

                       ( case when (ss.freq_recurrence_factor > 1) then

                                 'Every '+convert(varchar(3),ss.freq_recurrence_factor)+ ' Months'

                         else 'Monthly' end )

 

           WHEN 32 THEN 'Every '+ convert(varchar(3),ss.freq_recurrence_factor)+ ' Months' 

           -- RELATIVE

           WHEN 64 THEN'SQL Startup'

           WHEN 128 THEN'SQL Idle'

           ELSE'??'

         END,

 

        'Interval'=

         CASE

           WHEN (freq_type = 1)then'One time only'

           WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'

           WHEN (freq_type = 4 and freq_interval > 1) then'Every '+ convert(varchar(10),freq_interval) + ' Days'

           WHEN (freq_type = 8) then ( select'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7 

                                         from (select ss.schedule_id,

                                                      freq_interval,

                                                     'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun '

                                                                 ELSE ''

                                                            END,

                                                     'D2'= CASE WHEN (freq_interval & 2 <> 0) then 'Mon '

                                                                ELSE ''

                                                            END,

                                                     'D3'= CASE WHEN (freq_interval & 4 <> 0)then 'Tue ' 

                                                               ELSE ''

                                                           END,

                                                     'D4'= CASE WHEN (freq_interval & 8 <> 0)then 'Wed '

                                                                ELSE''

                                                           END,

                                                     'D5'= CASE WHEN (freq_interval & 16 <> 0)then'Thu '

                                                                ELSE ''

                                                           END,

                                                     'D6'= CASE WHEN (freq_interval & 32 <> 0)then'Fri '

                                                               ELSE ''

                                                           END,

                                                     'D7'= CASE WHEN (freq_interval & 64 <> 0)then 'Sat '

                                                               ELSE''

                                                           END 

                                                     from msdb..sysschedules ss 

                                                     where freq_type = 8)as F 

                                         where schedule_id = sj.schedule_id)

 

           WHEN (freq_type = 16)then 'Day '+convert(varchar(2),freq_interval)

 

           WHEN (freq_type = 32)then (select freq_rel + WDAY 

                                        from (select ss.schedule_id

                                                    ,'freq_rel'= CASE(freq_relative_interval)

                                                                     WHEN 1 then'First'

                                                                     WHEN 2 then'Second'

                                                                     WHEN 4 then'Third'

                                                                     WHEN 8 then'Fourth'

                                                                     WHEN 16 then'Last'

                                                                     ELSE'??'

                                                                 END

                                                    ,'WDAY'=     CASE (freq_interval)

                                                                    WHEN 1 then' Sun'

                                                                    WHEN 2 then' Mon'

                                                                    WHEN 3 then' Tue'

                                                                    WHEN 4 then' Wed'

                                                                    WHEN 5 then' Thu'

                                                                    WHEN 6 then' Fri'

                                                                    WHEN 7 then' Sat'

                                                                    WHEN 8 then' Day'

                                                                    WHEN 9 then' Weekday'

                                                                    WHEN 10 then' Weekend'

                                                                    ELSE'??'

                                                                 END

                                                    from msdb..sysschedules ss

                                                   where ss.freq_type = 32)as WS 

                                              where WS.schedule_id =ss.schedule_id)

         else'n/a'

         END,

 

         'Time'=CASE (freq_subday_type)

                    WHEN 1 then left(stuff((stuff((replicate('0', 6 -len(Active_Start_Time)))

                                +convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)

 

                    WHEN 2 then'Every '+convert(varchar(10),freq_subday_interval)+' seconds'

                    WHEN 4 then'Every '+convert(varchar(10),freq_subday_interval)+' minutes'

                    WHEN 8 then'Every '+convert(varchar(10),freq_subday_interval)+' hours'

                    ELSE'??'

         END,

 

         'Next Run Time'=CASE SJ.next_run_date

                   WHEN 0 THEN cast('n/a'as char(10))

                   ELSE convert(char(10),convert(datetime,convert(char(8),SJ.next_run_date)),120)

                        +' '+left(stuff((stuff((replicate('0', 6 -len(next_run_time)))

                        +convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)

         END,

 

         'Avg Job Duration' = coalesce( JH.Avg_Duration,'??'),

 

         'Max. Duration' = coalesce( JH.Max_Duration,'??'),

 

         'Num. of Executions' = coalesce( JH.Num_of_Executions,'0')

 

from msdb.dbo.sysjobschedules SJ 

join msdb.dbo.sysjobs S 

     on S.job_id = SJ.job_id

 

INNER JOIN msdb.dbo.syscategories cat 

           ON S.category_id = cat.category_id

 

join msdb.dbo.sysschedules SS 

     on ss.schedule_id = sj.schedule_id 

left join( 

 

SELECT jh.job_ID,

RTRIM(CAST(CONVERT(CHAR(2), DATEADD(ss,

MAX(CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 2, 2) AS INT)

* 60 * 60

+ CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 4, 2) AS INT)

* 60

+ CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 6, 2) AS INT)),

0), 13) - 1 AS CHAR(2))) + '.'

+ CONVERT(CHAR(8), DATEADD(ss,

  MAX(CAST(SUBSTRING(CAST(run_duration

 + 1000000 AS VARCHAR(7)),

 2, 2) AS INT) * 60 * 60

  + CAST(SUBSTRING(CAST(run_duration

+ 1000000 AS VARCHAR(7)),

4, 2) AS INT) * 60

  + CAST(SUBSTRING(CAST(run_duration

+ 1000000 AS VARCHAR(7)),

6, 2) AS INT)), 0), 14) Max_Duration,

RTRIM(CAST(CONVERT(CHAR(2), DATEADD(ms,

AVG(( CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 2, 2) AS INT)

 * 60 * 60

 + CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 4, 2) AS INT)

 * 60

 + CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 6, 2) AS INT) )

* 1000), 0), 13) - 1 AS CHAR(2)))

+ '.'

+ CONVERT(CHAR(12), DATEADD(ms,

AVG(( CAST(SUBSTRING(CAST(run_duration

+ 1000000 AS VARCHAR(7)),

2, 2) AS INT) * 60

 * 60

 + CAST(SUBSTRING(CAST(run_duration

  + 1000000 AS VARCHAR(7)),

  4, 2) AS INT) * 60

 + CAST(SUBSTRING(CAST(run_duration

  + 1000000 AS VARCHAR(7)),

  6, 2) AS INT) )

* 1000), 0), 14) Avg_Duration,

'Num_of_Executions' = COUNT(*) 

FROM    msdb.dbo.sysjobhistory jh 

WHERE    step_id = 0 

                            GROUP BY jh.job_ID

 

                              ) as JH 

     on S.job_id = JH.job_id

order by S.name

 

 


Tuesday, August 21, 2012 - 3:45:28 AM - Keyur Read The Tip

Great job...Thanks


Friday, December 28, 2012 - 11:34:38 AM - Astigdaw Read The Tip

Did anyone wrote a script to automate the  process that will produce a report for all your SQL servers?


Friday, February 01, 2013 - 5:10:46 AM - Guy Jones Read The Tip

Great script.  One comment - it's doesn't detail whether the multiple schedules are enabled or not.  The script details that the job is enabled but I ran on a server and it showed enabled in the column, where one of the schedules were actually disabled.

 

Thanks


Thursday, September 12, 2013 - 11:29:50 PM - Stan Read The Tip

I needed to find out which SSRS jobs were also scheduled - and I wanted it in time order.

 

(Note: I could not do an innerquery for the reportserver stuff because of collation differences - and could not get the syntax correct - so had a dummy spit and made a temp table :-(

 

STan

 

declare

 

@SSRS table

 

(

 

SQLagentJob

varchar(50),

 

Reportname

varchar(50)

 

)

 

 

Insert

into @SSRS(SQLagentJob,Reportname)

 

SELECT

Schedule.ScheduleID AS SQLAgent_Job_Name,

 

[Catalog]

.Name AS ReportName

 

FROM reportserver..ReportSchedule INNERJOIN

 

reportserver

..Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID INNERJOIN

 

reportserver

..Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNERJOIN

 

reportserver

..[Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID

select

 

left(@@ServerName,20)as [Server],

 

case

 

 

when ssrs.ReportName ISnotnullthen'SSRS - '+ ssrs.ReportName

 

elseleft(S.name,30)

 

endas [JobName],

 

left(

ss.name,25)as [ScheduleName],

 

CASE

(S.enabled)

 

WHEN 0 THEN'No'

 

WHEN 1 THEN'Yes'

 

ELSE'??'

 

ENDas [Enabled],

 

CASE

(ss.freq_type)

 

WHEN 1 THEN'Once'

 

WHEN 4 THEN'Daily'

 

WHEN 8 THEN

 

(casewhen (ss.freq_recurrence_factor > 1)

 

then'Every '+convert(varchar(3),ss.freq_recurrence_factor)+' Weeks'else'Weekly'end)

 

WHEN 16 THEN

 

(casewhen (ss.freq_recurrence_factor > 1)

 

then'Every '+convert(varchar(3),ss.freq_recurrence_factor)+' Months'else'Monthly'end)

 

WHEN 32 THEN'Every '+convert(varchar(3),ss.freq_recurrence_factor)+' Months'-- RELATIVE

 

WHEN 64 THEN'SQL Startup'

 

WHEN 128 THEN'SQL Idle'

 

ELSE'??'

 

ENDas [Frequency],

 

CASE

 

WHEN (freq_type = 1)then'One time only'

 

WHEN (freq_type = 4 and freq_interval = 1)then'Every Day'

 

WHEN (freq_type = 4 and freq_interval > 1)then'Every '+convert(varchar(10),freq_interval)+' Days'

 

WHEN (freq_type = 8)then (select'Weekly Schedule'= D1+ D2+D3+D4+D5+D6+D7

 

from (select ss.schedule_id,

 

freq_interval

,

 

'D1'=CASEWHEN (freq_interval & 1 <> 0)then'Sun 'ELSE''END,

 

'D2'=CASEWHEN (freq_interval & 2 <> 0)then'Mon 'ELSE''END,

 

'D3'=CASEWHEN (freq_interval & 4 <> 0)then'Tue 'ELSE''END,

 

'D4'=CASEWHEN (freq_interval & 8 <> 0)then'Wed 'ELSE''END,

 

'D5'=CASEWHEN (freq_interval & 16 <> 0)then'Thu 'ELSE''END,

 

'D6'=CASEWHEN (freq_interval & 32 <> 0)then'Fri 'ELSE''END,

 

'D7'=CASEWHEN (freq_interval & 64 <> 0)then'Sat 'ELSE''END

 

from msdb..sysschedules ss

 

where freq_type = 8

 

)as F

 

where schedule_id = sj.schedule_id

 

)

 

WHEN (freq_type = 16)then'Day '+convert(varchar(2),freq_interval)

 

WHEN (freq_type = 32)then (select freq_rel + WDAY

 

from (select ss.schedule_id,

 

'freq_rel'=CASE(freq_relative_interval)

 

WHEN 1 then'First'

 

WHEN 2 then'Second'

 

WHEN 4 then'Third'

 

WHEN 8 then'Fourth'

 

WHEN 16 then'Last'

 

ELSE'??'

 

END,

 

'WDAY'=CASE (freq_interval)

 

WHEN 1 then' Sun'

 

WHEN 2 then' Mon'

 

WHEN 3 then' Tue'

 

WHEN 4 then' Wed'

 

WHEN 5 then' Thu'

 

WHEN 6 then' Fri'

 

WHEN 7 then' Sat'

 

WHEN 8 then' Day'

 

WHEN 9 then' Weekday'

 

WHEN 10 then' Weekend'

 

ELSE'??'

 

END

 

from msdb..sysschedules ss

 

where ss.freq_type = 32

 

)as WS

 

where WS.schedule_id =ss.schedule_id

 

)

 

ENDas [Interval],

 

CASE

(freq_subday_type)

 

WHEN 1 thenleft(stuff((stuff((replicate('0', 6 -len(Active_Start_Time)))+convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)

 

WHEN 2 then'Every '+convert(varchar(10),freq_subday_interval)+' seconds'

 

WHEN 4 then'Every '+convert(varchar(10),freq_subday_interval)+' minutes'

 

WHEN 8 then'Every '+convert(varchar(10),freq_subday_interval)+' hours'

 

ELSE'??'

 

ENDas [Time],

 

CASE

SJ.next_run_date

 

WHEN 0 THENcast('n/a'aschar(10))

 

ELSEconvert(char(10),convert(datetime,convert(char(8),SJ.next_run_date)),120)+' '+left(stuff((stuff((replicate('0', 6 -len(next_run_time)))+convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)

 

ENDas [Next Run Time]

 

 

 

from msdb.dbo.sysjobschedules SJ

 

join msdb.dbo.sysjobs S on S.job_id = SJ.job_id

 

join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id

 

leftjoin @SSRS SSRS on ssrs.SQLagentJob = S.name

 

order

by [Time]



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.