Skip to main content

A great script to quickly make sense of MSSQL Agent Jobs.

Orginally obtained from http://www.sqlservercentral.com/Forums/Topic410557-116-1.aspx 
/*******************************************************************************

Name:   GetJobSchedule (For SQL Server7.0&2000)

Author:   M.Pearson
Creation Date:  5 Jun 2002
Version:  1.0


Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to
   produce a resultset showing the jobs on a server plus their schedules
   (if applicable) and the maximun duration of the job.
   
   The UNION join is to cater for jobs that have been scheduled but not yet
   run, as this information is stored in the 'active_start...' fields of the 
   sysjobschedules table, whereas if the job has already run the schedule 
   information is stored in the 'next_run...' fields of the sysjobschedules table.


Modification History:
-------------------------------------------------------------------------------
Version Date  Name  Modification
-------------------------------------------------------------------------------
1.0  5 Jun 2002 M.Pearson Inital Creation
1.1  6 May 2009 A. Gonzalez Adapted to SQL Server 2005 and to show
        subday frequencies.

*******************************************************************************/


USE msdb
Go


SELECT dbo.sysjobs.Name AS 'Job Name', 
 'Job Enabled' = CASE dbo.sysjobs.Enabled
  WHEN 1 THEN 'Yes'
  WHEN 0 THEN 'No'
 END,
 'Frequency' = CASE dbo.sysschedules.freq_type
  WHEN 1 THEN 'Once'
  WHEN 4 THEN 'Daily'
  WHEN 8 THEN 'Weekly'
  WHEN 16 THEN 'Monthly'
  WHEN 32 THEN 'Monthly relative'
  WHEN 64 THEN 'When SQLServer Agent starts'
 END, 
 'Start Date' = CASE active_start_date
  WHEN 0 THEN null
  ELSE
  substring(convert(varchar(15),active_start_date),1,4) + '/' + 
  substring(convert(varchar(15),active_start_date),5,2) + '/' + 
  substring(convert(varchar(15),active_start_date),7,2)
 END,
 'Start Time' = CASE len(active_start_time)
  WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
  WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
  WHEN 3 THEN cast('00:0' 
    + Left(right(active_start_time,3),1)  
    +':' + right(active_start_time,2) as char (8))
  WHEN 4 THEN cast('00:' 
    + Left(right(active_start_time,4),2)  
    +':' + right(active_start_time,2) as char (8))
  WHEN 5 THEN cast('0' 
    + Left(right(active_start_time,5),1) 
    +':' + Left(right(active_start_time,4),2)  
    +':' + right(active_start_time,2) as char (8))
  WHEN 6 THEN cast(Left(right(active_start_time,6),2) 
    +':' + Left(right(active_start_time,4),2)  
    +':' + right(active_start_time,2) as char (8))
 END,
-- active_start_time as 'Start Time',
 CASE len(run_duration)
  WHEN 1 THEN cast('00:00:0'
    + cast(run_duration as char) as char (8))
  WHEN 2 THEN cast('00:00:'
    + cast(run_duration as char) as char (8))
  WHEN 3 THEN cast('00:0' 
    + Left(right(run_duration,3),1)  
    +':' + right(run_duration,2) as char (8))
  WHEN 4 THEN cast('00:' 
    + Left(right(run_duration,4),2)  
    +':' + right(run_duration,2) as char (8))
  WHEN 5 THEN cast('0' 
    + Left(right(run_duration,5),1) 
    +':' + Left(right(run_duration,4),2)  
    +':' + right(run_duration,2) as char (8))
  WHEN 6 THEN cast(Left(right(run_duration,6),2) 
    +':' + Left(right(run_duration,4),2)  
    +':' + right(run_duration,2) as char (8))
 END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
  WHEN 0 THEN 'Once'
  ELSE cast('Every ' 
    + right(dbo.sysschedules.freq_subday_interval,2) 
    + ' '
    +     CASE(dbo.sysschedules.freq_subday_type)
       WHEN 1 THEN 'Once'
       WHEN 4 THEN 'Minutes'
       WHEN 8 THEN 'Hours'
      END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs 
LEFT OUTER 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 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
  FROM dbo.sysjobhistory
  GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name', 
 'Job Enabled' = CASE dbo.sysjobs.Enabled
  WHEN 1 THEN 'Yes'
  WHEN 0 THEN 'No'
 END,
 'Frequency' = CASE dbo.sysschedules.freq_type
  WHEN 1 THEN 'Once'
  WHEN 4 THEN 'Daily'
  WHEN 8 THEN 'Weekly'
  WHEN 16 THEN 'Monthly'
  WHEN 32 THEN 'Monthly relative'
  WHEN 64 THEN 'When SQLServer Agent starts'
 END, 
 'Start Date' = CASE next_run_date
  WHEN 0 THEN null
  ELSE
  substring(convert(varchar(15),next_run_date),1,4) + '/' + 
  substring(convert(varchar(15),next_run_date),5,2) + '/' + 
  substring(convert(varchar(15),next_run_date),7,2)
 END,
 'Start Time' = CASE len(next_run_time)
  WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
  WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
  WHEN 3 THEN cast('00:0' 
    + Left(right(next_run_time,3),1)  
    +':' + right(next_run_time,2) as char (8))
  WHEN 4 THEN cast('00:' 
    + Left(right(next_run_time,4),2)  
    +':' + right(next_run_time,2) as char (8))
  WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) 
    +':' + Left(right(next_run_time,4),2)  
    +':' + right(next_run_time,2) as char (8))
  WHEN 6 THEN cast(Left(right(next_run_time,6),2) 
    +':' + Left(right(next_run_time,4),2)  
    +':' + right(next_run_time,2) as char (8))
 END,
-- next_run_time as 'Start Time',
 CASE len(run_duration)
  WHEN 1 THEN cast('00:00:0'
    + cast(run_duration as char) as char (8))
  WHEN 2 THEN cast('00:00:'
    + cast(run_duration as char) as char (8))
  WHEN 3 THEN cast('00:0' 
    + Left(right(run_duration,3),1)  
    +':' + right(run_duration,2) as char (8))
  WHEN 4 THEN cast('00:' 
    + Left(right(run_duration,4),2)  
    +':' + right(run_duration,2) as char (8))
  WHEN 5 THEN cast('0' 
    + Left(right(run_duration,5),1) 
    +':' + Left(right(run_duration,4),2)  
    +':' + right(run_duration,2) as char (8))
  WHEN 6 THEN cast(Left(right(run_duration,6),2) 
    +':' + Left(right(run_duration,4),2)  
    +':' + right(run_duration,2) as char (8))
 END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
  WHEN 0 THEN 'Once'
  ELSE cast('Every ' 
    + right(dbo.sysschedules.freq_subday_interval,2) 
    + ' '
    +     CASE(dbo.sysschedules.freq_subday_type)
       WHEN 1 THEN 'Once'
       WHEN 4 THEN 'Minutes'
       WHEN 8 THEN 'Hours'
      END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs 
LEFT OUTER 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 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
  FROM dbo.sysjobhistory
  GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]

Comments

Post a Comment

Popular posts from this blog

Changing Password - in AD, when you're changing one of your other accounts, not the logged in account

  Use Powershell  Step 1 - Put your existing password in a Secure String  $oldPassword = Read-Host "Your old Password" -AsSecureString  Step 2 - Start the password change set-AdAccountPassword -Identity paul-admin -OldPassword  $oldPassword Step 3 - Enter your new password You'll be prompted for your new password... Please enter the desired password for 'CN=xxxx,OU=Admin Users,OU=Users,OU=Privileged,DC=CAIS,DC=com,DC=edu,DC=au' Password: ************************** Repeat Password: **************************
Post a Comment
Read more

Tip to create an underline on a heading shorter than the heading itself

You can use a pseudo element with :before (or :after ): h1 { font - weight : 300 ; display : inline - block ; padding - bottom : 5px ; position : relative ; } h1 : before { content : "" ; position : absolute ; width : 50 %; height : 1px ; bottom : 0 ; left : 25 %; border - bottom : 1px solid red ; } http://jsfiddle.net/9e27b/ This is another solution that centers the heading, the problem here is that the underline gets shorter as the column gets shorter. h2 {   display: inline-block;   padding-bottom: 15px;   position: relative;   width: 100% ;   text-align: center; } h2:before{     content: "";     position: absolute;     width: 8%;     height: 1px;     bottom: 0;     left: 46%;     border-bottom: 1px solid red; }
Post a Comment
Read more

The web.config no extension mime problem Lets Encrypt on IIS/Windows

  Tip when using LetsEncrypt in Windows - Feature Requests - Let's Encrypt Community Support In a paragraph, just use the web.config file to set the mime type, eg...  After trying to figure out why my Lets Encrypt failed to generate in Windows, and discovering that it's the no extension mime type problem, I wondered whether certbot could fill in the missing gap. All that is needed is the following web.config file to be placed in the same directory as the challenge <?xml version="1.0" encoding="UTF-8"?> <configuration> <system.webServer> <staticContent> <mimeMap fileExtension="." mimeType="text/xml" /> </staticContent> </system.webServer> </configuration> Because there's no mime type for files without an extension on IIS, IIS sends back a 404 when verification happens. The web.config file above sets the mime type. After I created the .well-known
1 comment
Read more