StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>These links look like they will get you on the right path...</p> <p><a href="http://www.mssqltips.com/sqlservertip/1622/generate-sql-agent-job-schedule-report/" rel="nofollow">Generate SQL Agent Job Schedule</a></p> <p><a href="http://www.mssqltips.com/sqlservertip/1244/accessing-sql-server-agent-data/" rel="nofollow">Accessing SQL Server Agent Data</a></p> <p>Here is a solution I altered from mssqltips.com.</p> <pre><code> WITH next_run_time AS ( SELECT sJOBSCH.schedule_id AS [ScheduleID] ,[sJOB].[name] AS [JobName] ,CASE WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL ELSE CAST( CAST([sJOBH].[run_date] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [LastRunDateTime] , CASE [sJOBSCH].[NextRunDate] WHEN 0 THEN NULL ELSE CAST( CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [NextRunDateTime] FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN ( SELECT [job_id] ,schedule_id ,MIN([next_run_date]) AS [NextRunDate] ,MIN([next_run_time]) AS [NextRunTime] FROM [msdb].[dbo].[sysjobschedules] GROUP BY [job_id],schedule_id ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id] LEFT JOIN ( SELECT [job_id] ,[run_date] ,[run_time] ,ROW_NUMBER() OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0 ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1 ) , Occurrence AS ( SELECT schedule_id AS [ScheduleID] ,[schedule_uid] AS [ScheduleUID] ,[name] AS [ScheduleName] ,CASE [enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [IsEnabled] ,CASE WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts' WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle' WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring' WHEN [freq_type] = 1 THEN 'One Time' END [ScheduleType] ,CASE [freq_type] WHEN 1 THEN 'One Time' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly - Relative to Frequency Interval' WHEN 64 THEN 'Start automatically when SQL Server Agent starts' WHEN 128 THEN 'Start whenever the CPUs become idle' END [Occurrence] ,CASE [freq_type] WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)' WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on ' + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' WHEN 32 THEN 'Occurs on ' + 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 + ' ' + 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 + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' END AS [Recurrence] ,CASE [freq_subday_type] WHEN 1 THEN 'Occurs once at ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 2 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 4 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 8 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') END [Frequency] ,STUFF( STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-') , 8, 0, '-') AS [ScheduleUsageStartDate] ,STUFF( STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-') , 8, 0, '-') AS [ScheduleUsageEndDate] ,[date_created] AS [ScheduleCreatedOn] ,[date_modified] AS [ScheduleLastModifiedOn] FROM [msdb].[dbo].[sysschedules] ) Select nrt.JobName,nrt.LastRunDateTime,nrt.NextRunDateTime ,o.Occurrence,o.Recurrence From next_run_time AS nrt Inner Join Occurrence o ON nrt.ScheduleID = o.ScheduleID </code></pre> <p>And the results from this query for me.</p> <p>JobName===========syspolicy_purge_history</p> <p>LastRunDateTime====4/23/13 19:47</p> <p>NextRunDateTime====4/24/13 2:00</p> <p>Occurrence=========Daily</p> <p>Recurrence=========Occurs every 1 day(s)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload