Saturday, 6 October 2012

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.