Investigating Job Schedules

One of the very first blog posts I made was on knowing what happened last night (https://sqlbadboy.wordpress.com/2012/10/12/what-happened-last-night/). In that post, I mentioned various ways that we could find out the statuses of jobs that had run. And this is an essential task for a DBA.

However, we should also know what is supposed to happen and when. Although that may be easier said than done, it is not impossible. Generally, this type of knowledge will come with having experience within an organization. But, there are some things we can do to help speed up that process.

Why is it important to know when things are scheduled to run on your SQL Server?

  • If you know when things are scheduled to run, it can help you identify busy times for your SQL Servers. This is valuable information so that you can schedule your database maintenance at less busy times.
  • If you know when things are scheduled to run, it can help you to monitor business processes. For example, if you have a process that is supposed to run every morning and send a report by a certain time and the report is not sent. Then, you can assume there was a problem.
  • If you know when things are scheduled to run, it can help you with baselining servers and planning for hardware resources.

How to find when things are scheduled to run on your SQL Server?

  • Use the Microsoft system stored procedures:
  • Use T-SQL against the system tables (sysjobs, sysjobschedules, sysschedules) in the msdb database:
SELECT 
       sql_server_name = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)),
       job_name = j.name,
       schedule_name = s.name,
       is_scheduled_enabled = s.enabled,
       schedule_description =
              CASE s.freq_type
              WHEN 1 THEN 'Once on ' + CONVERT(VARCHAR, s.active_start_date) + ' at ' + CONVERT(VARCHAR, s.active_start_time)  
              WHEN 4 THEN 'Every day ' 
              WHEN 8 THEN 'Every ' + CONVERT(NVARCHAR, s.freq_recurrence_factor) + ' week(s) on ' + 
                     CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END +
                     CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END 
              WHEN 16 THEN 'Every ' + CONVERT(VARCHAR, s.freq_recurrence_factor) + ' months(s) on day ' + CONVERT(VARCHAR, s.freq_interval) + ' of that month '  
              WHEN 32 THEN 'Every ' + CONVERT(VARCHAR, s.freq_recurrence_factor) + ' months(s) on the ' + 
                     CASE s.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  
                     WHEN (s.freq_interval > 00) AND (s.freq_interval < 08) 
                           THEN DATENAME(dw, '1996120' + CONVERT(VARCHAR, s.freq_interval))  
                     WHEN (s.freq_interval = 08) THEN 'day'  
                     WHEN (s.freq_interval = 09) THEN 'week day'  
                     WHEN (s.freq_interval = 10) THEN 'weekend day'  
                     END + ' of that month '  
              WHEN 64 THEN 'Automatically starts when SQLServerAgent starts.'
              WHEN 128 THEN 'Runs when the computer is idle.'
              END +
              CASE s.freq_subday_type  
              WHEN 1 THEN 'at ' + CONVERT(VARCHAR, s.active_start_time)  
              WHEN 2 THEN 'every ' + CONVERT(VARCHAR, s.freq_subday_interval) + ' second(s)'  
              WHEN 4 THEN 'every ' + CONVERT(VARCHAR, s.freq_subday_interval) + ' minute(s)'  
              WHEN 8 THEN 'every ' + CONVERT(VARCHAR, s.freq_subday_interval) + ' hour(s)'  
              ELSE ''
              END + 
              CASE 
              WHEN s.freq_subday_type IN (2, 4, 8)  
                     THEN ' between ' + CONVERT(VARCHAR, s.active_start_time) + ' and ' + CONVERT(VARCHAR, s.active_end_time)  
              ELSE ''
              END
FROM msdb.dbo.sysjobs j
       INNER JOIN msdb.dbo.sysjobschedules js
              ON j.job_id = js.job_id
       INNER JOIN msdb.dbo.sysschedules s
              ON js.schedule_id = s.schedule_id
ORDER BY j.name

So, hopefully this post will make it just a little bit easier to find out what is going to happen, when it comes to our SQL Servers.

Advertisements
This entry was posted in DBA, TSQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s