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:
2. Frequency (Once, Daily, Weekly, Monthly, etc.)
3. Interval (Specific time, Seconds, Minutes, Hours, etc.)
4. Start Date and Time
… by using some system views, like:
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.
Sometimes it get difficult or time-taking to get each and every SQL Job details, its schedule and other information. Also checking all these information in SSMS is time taking specially when your machine is running a client, and the server is located in some remote place. On the other side if you get some handy system Queries you can fire them in SSMS and get the desired results immediately, without browsing too much on heavy GUI tools.
Here are some of the System Stored Procedure queries that can be used to check the SQL Job details, like:
1. Job Details:
– Job ID/Name/Description
– Date Created/Modified
– Last/Next run Date/Time
– Current execution status
– Step ID/Names
– SQL Commands in each step
– On Success/Fail action
– Last run Duration/Retries/Date/Time
3. Job Schedule:
– Schedule ID/Name/Description
– Enable/Disable Status
– Job Frequency/Interval
– Job Start Date/Time
– Next run Date/Time
–> sp_help_job: system SP gives you all these information in 4 different result sets as shown in the image below. You can pass Job-Name or Job-ID as parameter to the system SP.
exec msdb.dbo.sp_help_job @job_name = 'Acquire_Source_Data' -- OR -- exec msdb.dbo.sp_help_job @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27'
–> sp_help_jobstep & sp_help_jobschedule: these 2 SPs also gives similar information provided by above SP (sp_help-job), but with single result-set. So you can execute them independently and use them in your logic.
exec msdb.dbo.sp_help_jobstep @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27' exec msdb.dbo.sp_help_jobschedule @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27' --