4

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

5
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
0
2

And....

SELECT j.[name]

, left(cast(jh.run_date as char(10)),4) 

+ '-' + substring(cast(jh.run_date as char(10)),5,2)

+ '-' + substring(cast(jh.run_date as char(10)),7,2) 

+ ' ' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),jh.run_time), 6), 1, 2)

+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), jh.run_time), 6) ,3 ,2)

+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),jh.run_time), 6) ,5 ,2) as [Start_Time]

FROM
    msdb.dbo.sysjobhistory jh

INNER JOIN msdb.dbo.sysjobs j

ON  j.job_id = jh.job_id

and jh.step_id = 0

and jh.run_status = 0

and left(cast(jh.run_date as char(10)),4) 

+ '-' + substring(cast(jh.run_date as char(10)),5,2)

+ '-' + substring(cast(jh.run_date as char(10)),7,2) 

+ ' ' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),jh.run_time), 6), 1, 2)

+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), jh.run_time), 6) ,3 ,2)

+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),jh.run_time), 6) ,5 ,2) >= 
        CONVERT(char(19), GETDATE()-2, 121) 
1
  • Verified that this works on SQL Server 2012 too. Mar 25 '16 at 23:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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