Início / dba / Perguntas / 4171
Accepted
Manjot
Asked: 2011-07-29 00:47:00 +0800 CST

SQL 2000 - tsql para descobrir trabalhos com falha nas últimas 24 horas

  • 772

Quero descobrir quais tarefas do agente sql falharam nas últimas 24 horas... Como posso localizá-lo usando o TSQL para SQL 2000?

Cumprimentos Manjot

sql-server-2000

2 respostas

  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

    E....

    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

relate perguntas