SQL Server: Como monitorar Jobs através de T-SQL

Dúvida: Como fazer para monitorar JOBS criados no SQL Server usando apenas T-SQL  (ou SELECT) ao invés de usar ferramentas gráficas.

Acredito que uma das maiores vantagens do SQL Server seja a facilidade que o mesmo oferece para Administração e Manutenção. Ao contrário do Oracle/Informix e de outros onde a manutenção e administração se dá quase sempre que na base de comandos, o SQL é uma ferramenta onde QUASE tudo pode ser feito através de ferramentas gráficas. No entanto, muitas vezes, queremos criar uma rotina simples sem cliques para consultar determinada informação. Já ouvi de DBAs que muita coisa não tinha jeito de fazer por código em SQL Server, e aqui vai uma forma fácil de monitorar JOBS sem um único clique. Vamos direto às dicas:

1) Exibir todas as informações sobre determinado Job:

USE msdb
EXEC dbo.sp_help_job @job_name = N'Back Up Full Database',
                               @job_aspect = N'Job' ;
GO

Ou então:

DECLARE @job_id uniqueidentifier
SELECT @job_id=job_id FROM msdb.dbo.sysjobs WHERE name=N'Back Up Full'
EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id
GO

2) Listar jobs que estão em execução no momento:

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

 Ou então:

select sj.job_id, sj.name, sjh.step_name
  from dbo.sysjobhistory sjh 
    inner join sysjobs sj  on sj.job_id = sjh.job_id
 where sjh.instance_id >   (select max(instance_id)   from sysjobhistory sjh1
                                              where sjh1.step_name = '(Job outcome)' 
                                              and sjh1.job_id = sjh.job_id ) 
union
select sj.job_id, sj.name, sjh.step_name from dbo.sysjobhistory sjh
    inner join sysjobs sj  on sj.job_id = sjh.job_id
 where sjh.step_name <> '(Job outcome)'
 and  sjh.instance_id =   ( select max(instance_id)
                                            from sysjobhistory  sjh1
                                            where sjh1.job_id = sjh.job_id )

3)  Listar jobs dos backups com Falha:

EXECUTE MSDB.DBO.SP_HELP_JOBHISTORY NULL, 
   N'Back Up LOG Database', @run_status = 1, @MODE = N'SUMMARY'

Dúvidas? silverio@siltechconsult.com.br