Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 }
share|improve this question

2 Answers 2

up vote 2 down vote accepted

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
share|improve this answer

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
share|improve this answer
3  
+1... But you should put that (giant) query in your answer just in case that link changes down the road. –  Michael Fredrickson Mar 2 '12 at 18:08
    
Good idea, updated –  Eric Higgins Mar 2 '12 at 18:16
2  
@MichaelFredrickson how psychic of you. :-) –  Aaron Bertrand Jul 30 '13 at 19:42
    
He's good like that :) Thanks for updating the link –  Eric Higgins Jul 31 '13 at 19:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.