Get failed SQL Server agent job?

Question

How to get a list of failed job run last night? I only find the following powershell script. What's the SQL equivalence?

dir $psPath\Jobs | % { $_.EnumHistory() } | ? { $_.RunStatus -ne 1 }

Answer 1

Here is a proc that will do the trick:

IF (object_id('ShowJobHistory') IS NOT NULL)
BEGIN
  print 'Dropping procedure: ShowJobHistory'
  drop procedure ShowJobHistory
END
print 'Creating procedure: ShowJobHistory'
GO
CREATE PROCEDURE ShowJobHistory
@Days int = 14
as
---------------------------------------------------------------------------------------------------
-- Date Created: September 13, 2010
-- Author:       Bill McEvoy
-- Description:  This procedure produces an easy to read report that details all jobs
--               that have run on this server in the specified number of days.
--               
---------------------------------------------------------------------------------------------------
-- Date Revised: 
-- Author:       
-- Reason:       
---------------------------------------------------------------------------------------------------
set nocount on

---------------------------------------------------------------------
-- Generate report                                                 --
---------------------------------------------------------------------


print ' '
print ' '
print 'RECENT JOB HISTORY'
print '=================='
print ' '

select 'ID'        = convert(char(8), h.instance_id),
       'Run Time'  = convert(char(10),convert(datetime,convert(char(8),h.run_date)),120) + ' ' + left(right('00000' + cast(run_time as varchar(6)),6),2) + ':' + left(right('00000' + cast(run_time as varchar(6)),4),2) + ':' +  right(cast(run_time as varchar(6)),2) + ' ',
       'Duration'  = case when (h.run_duration > 1800) then '>' else ' ' end + left(right(convert(char(19),(dateadd(ss,h.run_duration,'')),20),8),8) + ' ',
       'Status'    = case(run_status)
                       when 0 then '** FAILED ** '
                       when 1 then 'Success '
                       when 2 then 'RETRY '
                       when 3 then 'CANCELLED '
                       when 4 then 'IN PROGRESS '
                       else '??'
                     end,
       'Step'      = convert(char(3),h.step_id),
       'Job Name'  = left(s.name,50),
       'Step Name' = left(h.step_name,35),
       'Message'   = left(h.message,200)
  from msdb.dbo.sysjobhistory h
 right join msdb.dbo.sysjobs s on s.job_id = h.job_id
 where h.run_date >= convert(int,(convert(char(8), (getdate()-@Days),112)))
--   and (h.step_id = 0 or run_status = 0)
 order by h.instance_id desc



go
IF (object_id('ShowJobHistory') IS NOT NULL)
  print 'Procedure created'
ELSE
  print 'Procedure NOT created'
GO

Answer 2

Here you go. From: http://www.toadworld.com/platforms/sql-server/w/wiki/10351.sql-server-agent-job-query-samples.aspx

SET  NOCOUNT ON

DECLARE @MaxLength   INT
SET @MaxLength   = 50

DECLARE @xp_results TABLE (
                       job_id uniqueidentifier NOT NULL,
                       last_run_date nvarchar (20) NOT NULL,
                       last_run_time nvarchar (20) NOT NULL,
                       next_run_date nvarchar (20) NOT NULL,
                       next_run_time nvarchar (20) NOT NULL,
                       next_run_schedule_id INT NOT NULL,
                       requested_to_run INT NOT NULL,
                       request_source INT NOT NULL,
                       request_source_id sysname
                             COLLATE database_default NULL,
                       running INT NOT NULL,
                       current_step INT NOT NULL,
                       current_retry_attempt INT NOT NULL,
                       job_state INT NOT NULL
                    )

DECLARE @job_owner   sysname

DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()

INSERT INTO @xp_results
   EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE @xp_results
   SET last_run_time    = right ('000000' + last_run_time, 6),
       next_run_time    = right ('000000' + next_run_time, 6)

SELECT j.name AS JobName,
       j.enabled AS Enabled,
       CASE x.running
          WHEN 1
          THEN
             'Running'
          ELSE
             CASE h.run_status
                WHEN 2 THEN 'Inactive'
                WHEN 4 THEN 'Inactive'
                ELSE 'Completed'
             END
       END
          AS CurrentStatus,
       coalesce (x.current_step, 0) AS CurrentStepNbr,
       CASE
          WHEN x.last_run_date > 0
          THEN
             convert (datetime,
                        substring (x.last_run_date, 1, 4)
                      + '-'
                      + substring (x.last_run_date, 5, 2)
                      + '-'
                      + substring (x.last_run_date, 7, 2)
                      + ' '
                      + substring (x.last_run_time, 1, 2)
                      + ':'
                      + substring (x.last_run_time, 3, 2)
                      + ':'
                      + substring (x.last_run_time, 5, 2)
                      + '.000',
                      121
             )
          ELSE
             NULL
       END
          AS LastRunTime,
       CASE h.run_status
          WHEN 0 THEN 'Fail'
          WHEN 1 THEN 'Success'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Cancel'
          WHEN 4 THEN 'In progress'
       END
          AS LastRunOutcome,
       CASE
          WHEN h.run_duration > 0
          THEN
               (h.run_duration / 1000000) * (3600 * 24)
             + (h.run_duration / 10000 % 100) * 3600
             + (h.run_duration / 100 % 100) * 60
             + (h.run_duration % 100)
          ELSE
             NULL
       END
          AS LastRunDuration
  FROM          @xp_results x
             LEFT JOIN
                msdb.dbo.sysjobs j
             ON x.job_id = j.job_id
          LEFT OUTER JOIN
             msdb.dbo.syscategories c
          ON j.category_id = c.category_id
       LEFT OUTER JOIN
          msdb.dbo.sysjobhistory h
       ON     x.job_id = h.job_id
          AND x.last_run_date = h.run_date
          AND x.last_run_time = h.run_time
          AND h.step_id = 0