SQL Server – SQL Server Agent – Scheduled Jobs

Introduction

SQL Server jobs usually runs when, you schedule them and one usually forgets them. But, ever so often, the end user community cries out and says there are no data.

And, then one is forced to look at the jobs and tries to find those little potholes.

SQL

Here is a bit of working code to list our scheduled jobs and tried to iterate the time they are scheduled to run.


MS SQL Server – v2000


use [msdb]
go
  
SELECT
         tblJob.job_id as jobID
       
	   , tblJob.name as jobName
       
	   , tblJob.[enabled] as isJobEnabled
       
	   , tblJob.category_id as jobCategoryID
       
	   , tblCategory.name as jobCategoryLiteral
       
	   , tblJob.owner_sid as jobOwnerSID
       
	   , jobOwnerSIDLiteral
			= suser_sname(tblJob.owner_sid)
       
	   , tblJob.date_created as jobDateCreated
       
	   , tblJob.date_modified as jobDateModified
       
	   , tblJobSchedule.schedule_id as jobScheduleID
       
	   , tblSchedule.name as jobScheduleName
       
	   , tblSchedule.[enabled] as jobScheduleEnabled
       
	   , tblSchedule.freq_type as jobFrequencyTypeID

       , [jobFrequencyIntervalLiterval]
			= case tblSchedule.freq_type
				when 1 then 'Once'
				when 4 then 'Daily'
				when 8 then 'Weekly'
				when 16 then 'Monthly'
				when 32 then 'Monthly'
				when 64 then 'Runs when the SQL Server Agent service starts'
				when 128 then 'Runs when the computer is idle'
	         end 
  
       , tblSchedule.freq_interval as jobFrequencyIntervalID
       
	   , tblSchedule.active_start_time as jobStartTime
  
       , jobStartTimeLiteral  
			= case datalength(cast(tblSchedule.active_start_time as varchar(30)))
		
				when 6 then
  
					case
  
						when substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) < 12 then
  
							cast(cast(substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) as int) - 0 as varchar)
							+ ':'
							+ substring(cast(tblSchedule.active_start_time as varchar(6)), 3,2) 
							+ ':'
							+ substring(cast(tblSchedule.active_start_time as varchar(6)), 5,2) 
							+ ' AM'
  
						else
								cast(cast(substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) as int) - 12 as varchar)
							+ ':'
							+ substring(cast(tblSchedule.active_start_time as varchar(6)), 3,2) 
							+ ':'
							+ substring(cast(tblSchedule.active_start_time as varchar(6)), 5,2) 
							+ ' PM'
  
					end
  
				when 5 then

				  substring(cast(tblSchedule.active_start_time as varchar(5)), 1,1) 
				  + ':'
				  + substring(cast(tblSchedule.active_start_time as varchar(5)), 2,2) 
				  + ' AM'
  
				when 4 then
				+ '00:'
				+ substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) 
				+ ' AM'
 
			when 3 then
				 substring(cast(tblSchedule.active_start_time as varchar(3)), 1,1) 
					+ ' AM'
 
			when 2 then
				  substring(cast(tblSchedule.active_start_time as varchar(2)), 1,2) 
  
			when 1 then
			   case substring(cast(tblSchedule.active_start_time as varchar(1)), 1,1) 
					when '0' then '12:00 AM'
					else '12:0'
						  + substring(cast(tblSchedule.active_start_time as varchar(1)), 1,1) 
						  + ' AM'
			  end
  
		end  
  
FROM msdb.dbo.sysjobs tblJob
  
--LEFT OUTER JOIN msdb.dbo.sysJobschedules tblJobSchedule
LEFT OUTER JOIN msdb.[dbo].sysjobschedules  tblJobSchedule
  
    ON tblJob.job_id = tblJobSchedule.job_id
  
LEFT OUTER JOIN msdb.dbo.syscategories tblCategory
  
    ON tblJob.category_id = tblCategory.category_id

	 
	and   tblCategory.name not like 'Log Shipping'
	--and   tblCategory.name like 'Log Shipping'
 
	and   tblCategory.name not in
			(
				  'REPL-Checkup'   
				, 'REPL-History Cleanup'
				, 'REPL-Distribution Cleanup'
			)
  
LEFT OUTER JOIN msdb.dbo.sysschedules tblSchedule
  
    ON tblJobSchedule.schedule_id = tblSchedule.schedule_id
  
where tblJob.[enabled] = 1
 
and   tblSchedule.[enabled] = 1


order by
    tblJob.name


MS SQL Server – v2005, v2008, v2012


 
use [msdb]
go
  
SELECT
         tblJob.job_id as jobID
       
	   , tblJob.name as jobName
       
	   , tblJob.[enabled] as isJobEnabled
       
	   , tblJob.category_id as jobCategoryID
       
	   , tblCategory.name as jobCategoryLiteral
       
	   , tblJob.owner_sid as jobOwnerSID
       
	   , jobOwnerSIDLiteral
			= suser_sname(tblJob.owner_sid)
       
	   , tblJob.date_created as jobDateCreated
       
	   , tblJob.date_modified as jobDateModified
       
	   , tblJobSchedule.schedule_id as jobScheduleID
       
	   , tblSchedule.name as jobScheduleName
       
	   , tblSchedule.[enabled] as jobScheduleEnabled
       
	   , tblSchedule.freq_type as jobFrequencyTypeID

       , [jobFrequencyIntervalLiterval]
			= case tblSchedule.freq_type
				when 1 then 'Once'
				when 4 then 'Daily'
				when 8 then 'Weekly'
				when 16 then 'Monthly'
				when 32 then 'Monthly'
				when 64 then 'Runs when the SQL Server Agent service starts'
				when 128 then 'Runs when the computer is idle'
	         end 
  
       , tblSchedule.freq_interval as jobFrequencyIntervalID
       
	   , tblSchedule.active_start_time as jobStartTime
  
       , jobStartTimeLiteral  
			= case datalength(cast(tblSchedule.active_start_time as varchar(30)))
		
				when 6 then
  
					case
  
						when substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) < 12 then
  
							cast(cast(substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) as int) - 0 as varchar)
							+ ':'
							+ substring(cast(tblSchedule.active_start_time as varchar(6)), 3,2) 
							+ ':'
							+ substring(cast(tblSchedule.active_start_time as varchar(6)), 5,2) 
							+ ' AM'
  
						else
								cast(cast(substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) as int) - 12 as varchar)
							+ ':'
							+ substring(cast(tblSchedule.active_start_time as varchar(6)), 3,2) 
							+ ':'
							+ substring(cast(tblSchedule.active_start_time as varchar(6)), 5,2) 
							+ ' PM'
  
					end
  
				when 5 then

				  substring(cast(tblSchedule.active_start_time as varchar(5)), 1,1) 
				  + ':'
				  + substring(cast(tblSchedule.active_start_time as varchar(5)), 2,2) 
				  + ' AM'
  
				when 4 then
				+ '00:'
				+ substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) 
				+ ' AM'
 
			when 3 then
				 substring(cast(tblSchedule.active_start_time as varchar(3)), 1,1) 
					+ ' AM'
 
			when 2 then
				  substring(cast(tblSchedule.active_start_time as varchar(2)), 1,2) 
  
			when 1 then
			   case substring(cast(tblSchedule.active_start_time as varchar(1)), 1,1) 
					when '0' then '12:00 AM'
					else '12:0'
						  + substring(cast(tblSchedule.active_start_time as varchar(1)), 1,1) 
						  + ' AM'
			  end
  
		end  
  
FROM msdb.dbo.sysjobs tblJob
  
--LEFT OUTER JOIN msdb.dbo.sysJobschedules tblJobSchedule
LEFT OUTER JOIN msdb.[dbo].sysjobschedules  tblJobSchedule
  
    ON tblJob.job_id = tblJobSchedule.job_id
  
LEFT OUTER JOIN msdb.dbo.syscategories tblCategory
  
    ON tblJob.category_id = tblCategory.category_id

	 
	and   tblCategory.name not like 'Log Shipping'
	--and   tblCategory.name like 'Log Shipping'
 
	and   tblCategory.name not in
			(
				  'REPL-Checkup'   
				, 'REPL-History Cleanup'
				, 'REPL-Distribution Cleanup'
			)
  
LEFT OUTER JOIN msdb.dbo.sysschedules tblSchedule
  
    ON tblJobSchedule.schedule_id = tblSchedule.schedule_id
  
where tblJob.[enabled] = 1
 
and   tblSchedule.[enabled] = 1


order by
    tblJob.name

Listening To…

Wanted “De la Soul” potholes in my Lawn; but instead will reference “Arrested Developments’s Mr Wendal ( http://www.youtube.com/watch?v=wyDjRd0Tjss ).

References

References – System Tables

References – Sample Code

Leave a Reply

Please log in using one of these methods to post your comment:

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