SQL Agent Nuggets

msdb.dbo.sysjobhistory  run_date is an INT in the format 'yyyyMMdd'. To get a value to compare, e.g. TODAY, use SELECT FORMAT(GETDATE(), 'yyyyMMdd')

This is what I use to monitor a single job while it is running. There are a couple of critical jobs that I need to monitor where I use this script. The queries below provide: current job status job steps completed estimate of remaining time for the job to complete (needs some improvement) remain steps to be completed with run stats from yesterday Specify the job_id to monitor: SELECT job_id FROM msdb.dbo.sysjobs DECLARE @JOB_ID UNIQUEIDENTIFIER = '28D22C4A-F708-45C3-8917-2B08EBA5A550'; -- Check if the job is currently running. Look at start_execution_date to make sure -- it started today. If it's still running, stop_execution_date IS NULL SELECT  start_execution_date , last_executed_step_id , last_executed_step_date , stop_execution_date , job_history_id , next_scheduled_run_date , a.job_id , j.[name] FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id WHERE session_id = ( SELECT MAX(session_id) FROM msdb.dbo.syssessions )  AN...

This is work-in-progress. I add stuff as I think about it. Error Notification Create a process that runs periodically and performs some sort of notification for jobs that have failed recently. Poll the msdb.dbo.sysjobhistory table to determine jobs that have failed.

Get jobs currently running and jobs completed today (only the latest completion time if multiple)  -- -- Get status on jobs using sysjobactivity.  -- -- Get jobs currently running -- DECLARE @TODAY DATE = GETDATE(); SELECT  j.[name] AS JOB_NAME , a.job_id , a.run_requested_date , a.run_requested_source , a.start_execution_date , a.last_executed_step_id , a.last_executed_step_date FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id WHERE a.session_id = ( SELECT MAX(session_id) FROM msdb.dbo.syssessions )  AND a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL ORDER BY a.start_execution_date; -- -- Get jobs with current status completed (only shows the latest) -- SELECT  j.[name] AS JOB_NAME , a.job_id , a.run_requested_date , a.run_requested_source , a.start_execution_date , a.stop_execution_date , a.job_history_id , a.next_scheduled_run_date FROM msdb.dbo.sysjobactivity a JOIN ...

 DECLARE @JOB_ID UNIQUEIDENTIFIER = '42602363-FC74-44BE-ADB1-FA5FEA1917DC' -- Check if the job is currently running. Look at start_execution_date to make sure -- it started today. If it's still running, stop_execution_date IS NULL SELECT * FROM msdb.dbo.sysjobactivity WHERE session_id = ( SELECT MAX(session_id) FROM msdb.dbo.syssessions )  AND job_id = @JOB_ID ---------------------------------------------------------------------------------------- -- -- Show history for job for today, the estimated remaining  -- time for the job to complete, and the detail for the job steps remaining. -- Estimates are based on the prior day. -- -- run_status: -- 0 = Failed -- 1 = Succeeded -- 2 = Retry -- 3 = Canceled -- 4 = In Progress DECLARE  @RUN_DATE INT = FORMAT(GETDATE(), 'yyyyMMdd') , @LAST_STEP_ID_COMPLETED INT; --DECLARE @RUN_DATE INT = '20210105'; DECLARE @YESTERDAY INT = @RUN_DATE - 1; SELECT  msdb.dbo.agent_datetime(run_date, run_time) start_time , ...