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

Fixing a https Cert in Windows

 I renewed a https cert today in Windows and had problems with the new Cert sticking in Windows It would add fine, without error, but would disappear when the IIS Server Certificates screen would refresh.  Open the Certificates in MMC (Local Machine) and inspecting the Web Hosting gave a clue, the new certificate was there, but with a key - the private key was missing.  Could be because the original certificate was created on a completely different machine and imported to this new server.  At any rate, a simple certutil command fixed it .  A tip I received from  SSL disappears from the certificate list on Windows server - SSL Certificates - Namecheap.com Key steps are:  Double-click the certificate and go to  Details  tab. In certificate details locate the  Serial Number  field, click on it and copy its value. Open Command Prompt, pressing  Win+R  and typing  cmd , then click  OK In the command prompt type: ...
Post a Comment
Read more

Ever need to reset a password in Bonobo Git Server ?

So, you've forgotten your password to bonobo git. Step 1: Get access to the sqlite file, probably called something like  Bonobo.Git.Server.db, sitting in the "wwwroot\Bonobo.Git.Server\App_Data" directory. Step 2: Using another tool, generate an md5 hash of your new desired password (as far as I know, sqlite does not have md5 capability) Step 3: Back in sqlite, using SQL, update the relevant user record, something like update User set Password = UPPER( 'thenewMD5hash') where Username = 'admin' ; Step 4: That's it, you are done, log in with your newly found password.
2 comments
Read more

Save Attachments in Outlook automatically

For years I have wanted something to do this, and finally found it. Worked perfectly for me in Outlook 2013  In the end it is so simple. Guide from http://www.pixelchef.net/content/rule-autosave-attachment-outlook and https://msdn.microsoft.com/en-us/library/ee814736.aspx Open the VBA IDE in Outlook. Alt-F11 will do this. Insert the following code to the Modules section. On the left side there is a tree, expand until you find Modules. Then, if there is not a Module item under Modules, create one by right clicking on Modules. Or right click and choose Insert -> Module. Now, paste the text below in the main VBA window. Close the VBA IDE. Create a Rule that calls the script. Tools -> Rules and Alerts -> New Rule... In the first screen of the new rule wizard, choose "Check messages when they arrive". In the second, you could specify certain criteria that the message must match. Tip: Try "with specific words in the message header" and ...
Post a Comment
Read more