439
Keeping track of job schedules is essential for database administrators to ensure smooth database operations. The T-SQL script presented below helps simplify the task of gathering job information, such as the job’s last run time, duration, status, and step details, making it easier to monitor and troubleshoot job issues and provides DBAs with valuable insights into job performance, helping to ensure that databases are operating efficiently and effectively. Check this script to improve your job monitoring and ensure that your SQL Server database runs smoothly.
T-Sql
---------------------------------------------------------------------------------
--This script retrieves information on SQL Server jobs and their schedule details
---------------------------------------------------------------------------------
SELECT
sj.[name] AS JobName
, sj.[enabled]
, sj.date_created
, sj.date_modified
, CASE (freq_subday_type)
WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(active_start_time)))+ convert(varchar(6),active_start_time),3,0,':')),6,0,':'),8)
WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
ELSE '??'
END AS [Run Schedule]
--, sjs.last_run_date
--, sjs.last_run_time
, DATEADD(SECOND, (last_run_time/10000*3600) + (((last_run_time%10000-last_run_time%100)/100)*60) + (last_run_time%100), CONVERT(DATETIME,CAST(NULLIF(last_run_date,0) AS NVARCHAR(10)))) AS [last_run_datetime]
, sjs.last_run_duration
, sj.start_step_id
, sjs.step_id
, sjs.step_name
, sjs.subsystem
, sjs.command
, CASE on_success_action
WHEN 1 THEN 'Quit with success'
WHEN 2 THEN 'Quit with failure'
WHEN 3 THEN 'Go to next step'
WHEN 4 THEN 'Go to step ' + CAST(on_success_step_id AS VARCHAR(3))
END On_Success
, CASE on_fail_action
WHEN 1 THEN 'Quit with success'
WHEN 2 THEN 'Quit with failure'
WHEN 3 THEN 'Go to next step'
WHEN 4 THEN 'Go to step ' + CAST(on_fail_step_id AS VARCHAR(3))
END On_Failure
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobsteps sjs
ON sj.job_id = sjs.job_id
LEFT JOIN msdb.dbo.sysjobschedules sjss
ON sjss.job_id = Sj.job_id
LEFT JOIN msdb.dbo.sysschedules ss
ON ss.schedule_id = sjss.schedule_id
--WHERE sj.name in('Jobname here')
ORDER BY
JobName, step_id
/* To use this script, simply copy and paste it into a new query window in SQL Server Management Studio, then execute the script to view the job details. */
/* You can customize the WHERE clause to filter results based on specific job name. */
Note
For further information on this post, kindly refer to the original source provided in the links below.
Source: stackexchange, mssqltips