Archive for November, 2013

How to query for the most recent job failures for all the SQL Server Agent jobs?

POP QUIZ again HotShot: You need to query the most recent job failures for all the SQL Server Agent jobs so that you can send them over to the development team for further review and analysis. Sure, you can go to each of the jobs in the “Job Activity Monitor” and then sort the jobs by failure and then right-click each one to view their job steps and failures and then copy and paste each one of them into WORD or EXCEL. What if you have more than 100 instances and each instance contain 10 or more job failures and each of the jobs has 10 or more job steps? This will certainly take you close to or more than a week of manual processing. As much as you enjoy your work and capturing the job failures manually, you do need to leave the office and bring milk home before 8:00 PM. What do you do? What do you do? Don’t panic. You can use the script in this blog to save time and the trouble of manual processing.

Critical notes before you hit the execution button:
1. The script utilizes the system tables: msdb..sysjobhistory and msdb..sysjobs.
2. By default, the run_date column is formatted in YYYYMMDD, the run_time column is formatted in HHMMSS without the colons (:), and the run_duration is also formatted in HHMMSS without the colons (:). The script has parsed them and formatted them in YYYY-MM-DD HH:MM:SS.SSS for the run_data and run_time and HH:MM:SS for the run_duation.
3. Enjoy and send me your customized version when you get a chance.
Here’s the script:

-- Script: Query the most recent job failures for all jobs that had failed the latest execution 
-- Developer: JP Chen
-- Date Created: November 05, 2013
SELECT SERVERPROPERTY('SERVERNAME') AS InstanceName, job_name, step_id, step_name, message, run_datetime, run_duration
FROM
(
    SELECT job_name, step_id, step_name, message, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    FROM
    (
        SELECT DISTINCT
            j.name AS job_name, 
            h.step_id,
            h.step_name,
            h.message, 
            (CONVERT(DATETIME, RTRIM(h.run_date)) +  
                (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4) AS run_datetime,
            RIGHT('000000' + CONVERT(VARCHAR(6), h.run_duration), 6) AS run_duration 
        FROM msdb..sysjobhistory h
        inner join msdb..sysjobs j
        ON h.job_id = j.job_id
        join (SELECT j.name, h.step_id, MAX(h.run_date) AS run_date, MAX(h.run_time) AS run_time
				FROM msdb..sysjobhistory AS h JOIN msdb..sysjobs AS j
				ON h.job_id = j.job_id
				GROUP BY j.name, h.step_id) f 
		ON f.name = j.name and f.step_id = h.step_id and f.run_date = h.run_date and f.run_time = h.run_time
        WHERE h.run_status = 0 -- failed jobs        
    ) t 
) t
ORDER BY job_name ASC

REFERENCES:
SQL Server Script to Display Job History
http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx

Sysjobhistory
http://technet.microsoft.com/en-us/library/ms174997(v=sql.105).aspx