Início / dba / Perguntas / 13148
Accepted
BradC
Asked: 2012-02-16 14:26:58 +0800 CST

Obtenha a etapa concluída mais recentemente para cada trabalho SQL

  • 772

Estou tentando obter uma lista de trabalhos e sua etapa concluída mais recentemente do banco de dados MSDB. Para um único trabalho, isso é bastante direto, algo como:

 SELECT TOP 1 j.job_id, j.name, h.step_name, h.run_date, h.run_time, h.step_id
 FROM msdb.dbo.sysjobs j
 INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE j.name = 'my favorite job'
 ORDER BY h.run_date DESC, h.run_time DESC, h.step_id DESC

Mas como posso obter uma lista de TODOS os trabalhos e sua etapa concluída mais recentemente?

Observe que este é SQL 2000, então não posso usar PARTITION OVER. Também não consigo fazer um SELECT MAX() porque não é um único campo RunDateTime, tenho que classificar de forma descendente por dois campos (OrderDay, OrderTime).

Edit: Provavelmente não é possível, mas eu adoraria poder fazer algo como:

SELECT j.job_id, ...
FROM msdb.dbo.sysjobs j
INNER JOIN (SELECT TOP 1 step_name, run_date, run_time, step_id
            FROM msdb.dbo.sysjobhistory
            WHERE job_id = j.job_id
            ORDER BY run_date desc, run_time desc, step_id desc) as H

ou algo assim, mas não parece gostar de sintaxe assim...

sql-server-2000 subquery

2 respostas

  1. Best Answer

    Infelizmente, como pode haver empates (a penúltima etapa pode levar 0 segundos e, portanto, terminar "exatamente" ao mesmo tempo que a última etapa), isso não é muito bonito no SQL Server 2000. Em vez de calcular todos os datetime várias vezes, acho que a maneira mais fácil é colocá-lo em uma tabela #temp. Você pode considerar a criação de uma exibição permanente com a conversão de data e hora.

    SELECT job_id, step_name, run_date, run_time, step_id, 
    run_datetime = CONVERT(DATETIME, RTRIM(run_date) + ' ' + 
    STUFF(STUFF(RIGHT('00000' + RTRIM(run_time), 6),3,0,':'),6,0,':'))
  INTO #t
      FROM msdb.dbo.sysjobhistory;

SELECT j.job_id, j.name, t.step_name, t.run_date, t.run_time,
       t.step_id, t.run_datetime
FROM #t AS t
INNER JOIN 
    (
      SELECT job_id, run_datetime = MAX(run_datetime)
      FROM #t GROUP BY job_id
    ) AS m 
    ON m.job_id = t.job_id
    AND m.run_datetime = t.run_datetime
INNER JOIN 
    (
      SELECT job_id, run_datetime, step_id = MAX(step_id) 
      FROM #t GROUP BY job_id, run_datetime 
    ) AS s 
    ON s.job_id = m.job_id
    AND s.run_datetime = m.run_datetime
    AND s.step_id = t.step_id
INNER JOIN 
    msdb.dbo.sysjobs AS j
    ON t.job_id = j.job_id
    AND m.job_id = j.job_id
    AND s.job_id = j.job_id;

DROP TABLE #t;
    • 3

  2. Acabei de perceber que sysjobhistory tem uma chave de identidade, "INSTANCE_ID". Não sei porque pensei que a mesa não tinha...

    Encontrar o maior INSTANCE_ID para cada trabalho (excluindo a etapa 0) torna isso muito mais fácil e evita a tabela temporária:

    SELECT j.job_id, j.name, j.enabled
    , h1.last_row
    , h2.step_id, h2.step_name, h2.run_date, h2.run_time, h2.run_duration
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN 
    (SELECT job_id, max(instance_id) as last_row
     FROM msdb.dbo.sysjobhistory
     WHERE step_id > 0
     GROUP BY job_id) as h1
    ON j.job_id = h1.job_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h2
    ON h1.job_id = h2.job_id
    AND h1.last_row = h2.instance_id
ORDER BY j.name

    Ainda tenho que colocar a conversão de data/hora lá, mas isso parece funcionar.

    • 0

relate perguntas