SQL Agent Job History Monitoring and Maintenance

Manage and monitor SQL Server Agent job history, ensure successful execution of scheduled tasks, troubleshoot job failures, optimize job performance, maintain logs, and generate reports for analysis. Collaborate with teams to ensure reliable automation and timely data processing across environments.

USE	
	MSDB

SELECT      [ServerName] = @@Servername,
	    [JobName]   = JOB.name,
            [Message]   = HIST.message,
            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'
            WHEN HIST.run_status = 1 THEN 'Succeeded'
            WHEN HIST.run_status = 2 THEN 'Retry'
            WHEN HIST.run_status = 3 THEN 'Canceled'
            END,
            [RunDate]   = CONVERT(DATE, RTRIM(run_date)),
            --[RunTime]   = HIST.run_time,
            --hist.run_duration, 
            RunDuration =
										CASE LEN(hist.run_duration)
											WHEN 1 THEN '00:00:0' + CONVERT(CHAR(1),hist.run_duration)
											WHEN 2 THEN '00:00:' + CONVERT(CHAR(2),hist.run_duration)
											WHEN 3 THEN '00:0' + CONVERT(CHAR(1),LEFT(hist.run_duration,1)) + ':' + CONVERT(CHAR(2),RIGHT(hist.run_duration,2))
											WHEN 4 THEN '00:' + CONVERT(CHAR(2),LEFT(hist.run_duration,2)) + ':' + CONVERT(CHAR(2),RIGHT(hist.run_duration,2))
											WHEN 5 THEN '0' + CONVERT(CHAR(1),LEFT(hist.run_duration,1)) + ':' + LEFT(RIGHT(hist.run_duration,4),2) + ':' + CONVERT(CHAR(2),RIGHT(hist.run_duration,2))
											ELSE
												CONVERT(VARCHAR(4),LEFT(hist.run_duration,LEN(hist.run_duration)-4)) + ':' + LEFT(RIGHT(hist.run_duration,4),2) + ':' + CONVERT(CHAR(2),RIGHT(hist.run_duration,2))
										END
FROM        sysjobs JOB
INNER JOIN  sysjobhistory HIST ON HIST.job_id = JOB.job_id
where 1=1

--job.name = 'BackupAllDB.Subplan_1'
and  CONVERT(DATE, RTRIM(run_date)) = CAST(DATEADD(d,-1,GETDATE()) as DATE)
ORDER BY    1