主页 / dba / 问题 / 4171
Accepted
Manjot
Asked: 2011-07-29 00:47:00 +0800 CST

SQL 2000 - tsql 找出过去 24 小时内失败的作业

  • 772

我想找出过去 24 小时内哪些 sql 代理作业失败...如何使用 TSQL for SQL 2000 找到它？

问候 Manjot

sql-server-2000

2 个回答

  1. 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
    • 5
  2. Best Answer

    和....

    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)
    • 2

相关问题