SupportSQL

This is an installment in the “SQL Agent Toolbox” series of articles for SQL Server.

This proc returns all the failed job steps from SQL Server Agent history tables. This is useful to see how many times a job has failed regardless of notifications and the current run status of the job.

Here’s the proc:

CREATE PROCEDURE [gp_DBA_JobStepsFailed]
      @hours_back int = 168
     ,@SendEmail bit = 0
     ,@recipients varchar(100) = ‘administrator@some_company.com’
AS

—e.g. Exec dbo.gp_DBA_JobStepsFailed_Email @hours_back int = -12
—            (show all failed job steps for the past 12 hours)

BEGIN
      IF @hours_back > 0
         BEGIN
                SET @hours_back = @hours_back * -1
         END

      SET NOCOUNT ON
      DECLARE
              @subject varchar(50)
             ,@message varchar(4000)
             ,@minid int
             ,@maxid int
             ,@dt datetime
             ,@dt2 datetime
             ,@dti bigint
             ,@min_run_time int
             ,@max_run_time int

      SELECT
        @dt = dateadd(hh, @hours_back, getdate())
      SELECT
        @dt2 = dateadd(hh, @hours_back, getdate())

      SELECT
        @dti = cast(cast(datepart(year, @dt) AS varchar) + right(‘00’ + cast(datepart(mm, @dt) AS varchar), 2) + right(‘00’ + cast(datepart(dd, @dt) AS varchar), 2) + right(‘00’ + cast(datepart(hh, @dt) AS varchar), 2) + right(‘00’ + cast(datepart(mi, @dt) AS varchar), 2) + right(‘00’ + cast(datepart(ss, @dt) AS varchar), 2) AS bigint)

      SELECT
        @subject = isnull(@@SERVERNAME, ”) + ’ reports SQL Agent job steps failed today. ’
      CREATE TABLE #tmp
             (
              job_id uniqueidentifier
             ,job_name varchar(255)
             ,step_name varchar(255)
             ,database_name varchar(256)
             ,step_cmd varchar(4096)
             ,sql_severity int
             ,sql_message_id int
             ,run_status int
             ,run_date int
             ,run_time int
             ,hist_msg varchar(2048)
             ,ident_id int IDENTITY(1, 1))

      INSERT INTO
        #tmp
        (
         job_id
        ,job_name
        ,step_name
        ,database_name
        ,step_cmd
        ,sql_severity
        ,sql_message_id
        ,run_status
        ,run_date
        ,run_time
        ,hist_msg)
        SELECT DISTINCT
            sj.job_id
           ,left(sj.name, 60)
           ,left(sos.step_name, 60)
           ,left(sos.database_name, 256)
           ,left(sos.command, 4096)
           ,soh.sql_severity
           ,soh.sql_message_id
           ,soh.run_status
           ,soh.run_date
           ,soh.run_time
           ,left(soh.[message], 1024)
        FROM
            msdb..sysjobs sj
        INNER JOIN msdb..sysjobsteps sos ON sos.job_id = sj.job_id
        INNER JOIN msdb..sysjobhistory soh ON soh.job_id = sos.job_id AND
                                              soh.step_id = sos.step_id
        WHERE
            soh.run_status = 0 AND
            cast(cast(soh.run_date AS varchar(8)) + right(‘00’ + cast(soh.run_time AS varchar), 6) AS bigint) >= @dti

      IF ((SELECT count (*) FROM #tmp) > 0)
         BEGIN
        —create formatted message from records
               SELECT
                @minid = min(ident_id)
               ,@maxid = max(ident_id)
               FROM
                #tmp
               SELECT
                @message = isnull(@@SERVERNAME, ”) + ’ FAILED JOBS/STEPS: ’ + char(13) + char(13)
               WHILE (@minid <= @maxid)
                     BEGIN
                           SELECT
                            @message = @message + char(10) + ‘Job name: ’ + job_name + char(13) + char(9) + ‘Step name: ’ + step_name + char(13) + char(10)
                           FROM
                            #tmp
                           WHERE
                            ident_id = @minid

                           SELECT
                            @minid = @minid + 1
                     END
            —email @message
               IF @SendEmail <> 0
                  BEGIN
                        EXEC msdb.dbo.sp_send_dbmail
                          @profile_name = ‘ITOps’
                        , @subject = @subject
                        , @recipients = @recipients
                        , @body = @message 

                  END
               PRINT @message
         END

               SELECT
               @@servername AS SQLServerName
                ,*
               ,convert(datetime,
               substring(convert(varchar(10), run_date), 5, 2) + ‘/’ + substring(convert(varchar(10), run_date), 7, 2) + ‘/’ +
               left(convert(varchar(10), run_date), 4) + ’ ’ +
               left(right(‘00’ + convert(varchar(10), run_time), 6), 2) + ‘:’ +
               substring(right(‘00’ + convert(varchar(10), run_time), 6), 3, 2) + ‘:’ +
               substring(right(‘00’ + convert(varchar(10), run_time), 6), 5, 2))
               AS RunDate,
               getdate() AS PolledOn
               FROM
                #tmp
               ORDER BY
                run_date DESC
               ,run_time DESC
END

—by Alek Kirstein – SupportSQL.com