Monitoring Solution to Identify SQL Server Job failures

Most of the SQL Server scripts are being scheduled through SQL Agent Jobs. It is better, if we have monitoring solution to identify SQL Server job failures. And also if we have multiple SQL Server instances, monitoring solution can be implemented in centralized manner.

The below mentioned monitoring script solution is used to identify SQL Server job failures. 

Set NOCOUNT ON

SELECT

          @@ServerName [Server],

          RTRIM(name) [Job Name],

          SUBSTRING(CAST(run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(run_date AS    CHAR(8)),2) + '/' +

                    LEFT(CAST(run_date AS CHAR(8)),4) + ' '+ LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +

                    SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +  RIGHT(RIGHT('000000' +

                    CAST(run_time AS VARCHAR(10)),6),2) [Last Run Date],

          [Num Times]

FROM

          msdb..sysjobs jobs,

          (

          SELECT

                    hist.job_id,

                    recent.instance_id AS instance_id,

                    recent.run_date AS run_date,

                    recent.run_time AS run_time,

                    1 as [Num Times]

          FROM

                    msdb..sysjobhistory hist,

                    (

                              SELECT

                                                  job_id,

                                                  MAX(instance_id) instance_id,

                                                  MAX(run_date) as run_date,

                                                  MAX(run_time) as run_time

                              FROM

                                                  msdb..sysjobhistory

                              GROUP BY job_id

                     ) recent

          WHERE

                    hist.job_id = recent.job_id

                    AND hist.instance_id = recent.instance_id

                    AND hist.run_status = 0

                    AND hist.step_id = 0

          ) hist

WHERE

          jobs.job_id=hist.job_id

          and jobs.enabled=1

ORDER BY

          name

           

                   

If we have multiple SQL Server instances, we create a SQL Table to store the name of the all SQL Server instances. Above mentioned SQL script can be used through OSQL Statement inside the batch script and loop through each SQL instances.