SQL with Manoj

In my [previous post] I discussed about the system SPs that we can use to get details of a SQL Job, like its Name, ID, Description, Schedule, etc. But what if you need to check these details of various SQL Jobs?

Here in this post we will see how we check the Job Schedule of various SQL Jobs, like:

1. Enable/Disable
2. Frequency (Once, Daily, Weekly, Monthly, etc.)
3. Interval (Specific time, Seconds, Minutes, Hours, etc.)
4. Start Date and Time
5. Description

… by using some system views, like:

– sysJobs
– sysJobsShedules
– sysSchedules
 

select 
	 'Job ID' = j.job_id
	,'Job Name' = j.name
	,'Job Enabled' = case j.enabled
			when 1 then 'Yes'
			else 'No'
		end
	,'Frequency' = case s.freq_type 
		when 1 then 'One time only'
		when 4 then 'Daily'
		when 8 then 'Weekly'
		when 16 then 'Monthly'
		when 32 then 'Monthly, relative to freq_interval'
		when 64 then 'Runs when the SQL Server Agent service starts'
		when 128 then 'Runs when the computer is idle'
	end
	,'Interval' = case s.freq_subday_type
		when 1 then 'At specified time'
		when 2 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Seconds'
		when 4 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Minutes'
		when 8 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Hours'
	end
	,'Start_Date' = 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)
		+ ' ' + 
		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
	,'Schedule Enabled' = case s.enabled
            when 1 then 'Yes'
            else 'No'
        end
    ,'Schedule Desc' = s.name
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 s.schedule_id = js.schedule_id
order by j.name
GO

I’ve created the above query by collating references form forums and MSDN BoL, you can tweak and modify this query as well as per your requirements.