Generate SQL Agent Job Schedule Report

By:   |   Updated: 2008-11-07   |   Comments (18)   |   Related: > 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.name, CAST((sysjobschedules.active_start_time / 10000) AS VARCHAR(10)) + ':' +
RIGHT('00' + CAST((sysjobschedules.active_start_time % 10000) / 100 AS VARCHAR(10)),2) active_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_time) AS ScheduleDscr, sysjobs.enabled
FROM sysjobs INNER JOIN
sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id 

SQL Server 2005  

SELECT dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))  
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS 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_time) AS ScheduleDscr, dbo.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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-11-07

Comments For This Article




Thursday, September 24, 2015 - 11:06:17 AM - Kc9400 Back To Top (38748)

 

 

Hmmmm how do I get it to show the schedule for jobs that run every 13 days? At present it tells me that it runs daily which is incorrect.

 

using the above sql and sql server 2000

Tuesday, October 28, 2014 - 7:41:17 AM - Siji Back To Top (35095)

Hi,

Found this very useful to generate the list of jobs and its schedules along with the schedule description field. HOwever Is there an alternative to get this information without creating a function? (Note : I'm new to the SQL DBA admin role and so am nto familiar with writing scripts etc)

Your input will be much appreciated

Thursday, September 12, 2013 - 11:29:50 PM - Stan Back To Top (26777)

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]


Friday, February 1, 2013 - 5:10:46 AM - Guy Jones Back To Top (21858)

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

Friday, December 28, 2012 - 11:34:38 AM - Astigdaw Back To Top (21177)

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

Tuesday, August 21, 2012 - 3:45:28 AM - Keyur Back To Top (19136)

Great job...Thanks

Friday, August 10, 2012 - 1:04:48 PM - marcello miorelli Back To Top (18992)

 

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

  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 [email protected]

  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, May 29, 2012 - 2:00:46 AM - sneha Back To Top (17688)

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

Thursday, May 24, 2012 - 11:06:33 AM - Ludo Bernaerts Back To Top (17642)

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

 

 

Friday, May 11, 2012 - 5:57:17 AM - Elaelian Back To Top (17415)

Exactly what I was looking for ! Thank you :D

Wednesday, March 7, 2012 - 11:15:02 AM - Kish Adurty Back To Top (16279)

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

 

 

Monday, November 24, 2008 - 6:58:23 AM - Dr DBA Back To Top (2264)

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.

:)

 

Saturday, November 22, 2008 - 11:58:32 AM - jpizano Back To Top (2261)

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

Friday, November 21, 2008 - 7:30:26 AM - Dr DBA Back To Top (2254)

 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


 

Friday, November 21, 2008 - 7:07:56 AM - casilvis Back To Top (2252)

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

 

Wednesday, November 12, 2008 - 7:13:22 AM - KenSimmons Back To Top (2201)

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.

Wednesday, November 12, 2008 - 4:31:42 AM - JimR Back To Top (2200)

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?

Friday, November 7, 2008 - 12:50:08 PM - [email protected] Back To Top (2165)

Outstanding, deployed it to all my servers already














get free sql tips
agree to terms