patternMinor

SQL 2000 - tsql to find out failed jobs in last 24 hours

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

2000tsqllastsqlhoursjobsfailedfindout

Problem

I want to find out which sql agent jobs failed in last 24 hours...
How can I find it using TSQL for SQL 2000?

Regards
Manjot

Solution

SELECT
    j.[name]
  , jh.run_date
  , jh.run_time
  , jh.sql_severity
  , jh.message
FROM
    msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
ON  j.job_id = jh.job_id
WHERE
    jh.run_status = 0 -- Failure
AND jh.run_date > DATEADD(DAY, -1, GETDATE())
ORDER BY
    jh.run_date DESC

Code Snippets

SELECT
    j.[name]
  , jh.run_date
  , jh.run_time
  , jh.sql_severity
  , jh.message
FROM
    msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
ON  j.job_id = jh.job_id
WHERE
    jh.run_status = 0 -- Failure
AND jh.run_date > DATEADD(DAY, -1, GETDATE())
ORDER BY
    jh.run_date DESC

Context

StackExchange Database Administrators Q#4171, answer score: 5

Revisions (0)

No revisions yet.