SQL Server: Relatório de Trabalhos (Jobs) e Etapas (Steps) no SQL Server 🔍

Este script SQL é utilizado para gerar um relatório detalhado de trabalhos (jobs) e suas etapas (steps) no SQL Server. O script é altamente útil para administradores de banco de dados, pois fornece uma visão abrangente sobre os trabalhos automatizados no SQL Server. Ele coleta informações como o nome do trabalho, se está habilitado, as datas de criação e modificação, a categoria do trabalho, uma descrição detalhada, além de informações sobre as etapas dos trabalhos, como o tipo de comando executado, o banco de dados utilizado, as ações executadas em caso de sucesso ou falha e as tentativas de repetição. Esse relatório ajuda no gerenciamento, monitoramento e análise de trabalhos, sendo crucial para otimizar processos automatizados no banco de dados.

Objetivo:
🎯
O objetivo do script é oferecer uma visão clara e organizada dos trabalhos (jobs) e suas etapas (steps), facilitando a análise e o controle de tarefas automatizadas no SQL Server. Ele também permite personalizar os resultados de acordo com filtros específicos, como a categoria do trabalho ou palavras-chave no nome do trabalho ou no comando da etapa.

Estrutura do Script:
📝
O script é composto por uma consulta SQL que utiliza várias tabelas do banco de dados msdb para coletar informações detalhadas. As principais tabelas utilizadas são:

• sysjobsteps: Contém informações sobre as etapas de cada trabalho.
• sysjobs: Contém informações sobre os trabalhos em si.
• syscategories: Contém as categorias dos trabalhos.
• sysproxies: Informações sobre proxies usados pelos trabalhos.
• sysschedules: Informações sobre os agendamentos dos trabalhos.

A consulta inclui as seguintes informações em suas colunas:

• JobName: Nome do trabalho.
• IsEnabled: Se o trabalho está habilitado ( Yes ou No ).
• JobCreatedOn: Data de criação do trabalho.
• JobLastModifiedOn: Data da última modificação do trabalho.
• StepNo: Número da etapa dentro do trabalho.
• StepName: Nome da etapa.
• JobOwner: Proprietário do trabalho.
• JobCategory: Categoria do trabalho.
• JobDescription: Descrição do trabalho.
• StepType: Tipo de comando da etapa (e.g., T-SQL, PowerShell, CmdExec).
• RunAs: Conta usada para executar a etapa.
• Database: Banco de dados usado na etapa.
• ExecutableCommand: Comando executado na etapa.
• OnSuccessAction: Ação a ser tomada em caso de sucesso da etapa (e.g., "Quit the job reporting success").
• RetryAttempts: Tentativas de repetição em caso de falha.
• RetryInterval: Intervalo de repetição em minutos.
• OnFailureAction: Ação a ser tomada em caso de falha da etapa.
• IsScheduled: Indica se o trabalho está agendado.
• JobScheduleName: Nome do agendamento do trabalho.
• JobDeletionCriterion: Critério de exclusão do trabalho (e.g., "On Success", "On Failure").

Query SQL:
💻
Aqui está a query SQL completa que gera o relatório de trabalhos e suas etapas no SQL Server:

DECLARE @STRING VARCHAR(100)
DECLARE @JOBCATEGORY VARCHAR(100)
SET @STRING = ''
SET @JOBCATEGORY = 'D2D_Alert'
    SELECT
        [sJOB].[name] AS [JobName] ,
        CASE [sJOB].[enabled]
              WHEN 1 THEN 'Yes'
              WHEN 0 THEN 'No'
        END AS [IsEnabled] ,
        [sJOB].[date_created] AS [JobCreatedOn] ,
        [sJOB].[date_modified] AS [JobLastModifiedOn] ,
        [sJSTP].[step_id] AS [StepNo] ,
        [sJSTP].[step_name] AS [StepName] ,
        [sDBP].[name] AS [JobOwner] ,
        [sCAT].[name] AS [JobCategory] ,
        [sJOB].[description] AS [JobDescription] ,
        CASE [sJSTP].[subsystem]
              WHEN 'ActiveScripting' THEN 'ActiveX Script'
              WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
              WHEN 'PowerShell' THEN 'PowerShell'
              WHEN 'Distribution' THEN 'Replication Distributor'
              WHEN 'Merge' THEN 'Replication Merge'
              WHEN 'QueueReader' THEN 'Replication Queue Reader'
              WHEN 'Snapshot' THEN 'Replication Snapshot'
              WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
              WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
              WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
              WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
              WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
          ELSE sJSTP.subsystem
        END AS [StepType] ,
        [sPROX].[name] AS [RunAs] ,
        [sJSTP].[database_name] AS [Database] ,
        [sJSTP].[command] AS [ExecutableCommand] ,
        CASE [sJSTP].[on_success_action]
              WHEN 1 THEN 'Quit the job reporting success'
              WHEN 2 THEN 'Quit the job reporting failure'
              WHEN 3 THEN 'Go to the next step'
              WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) + ' ' + [sOSSTP].[step_name]
        END AS [OnSuccessAction] ,
        [sJSTP].[retry_attempts] AS [RetryAttempts] ,
        [sJSTP].[retry_interval] AS [RetryInterval (Minutes)] ,
        CASE [sJSTP].[on_fail_action]
              WHEN 1 THEN 'Quit the job reporting success'
              WHEN 2 THEN 'Quit the job reporting failure'
              WHEN 3 THEN 'Go to the next step'
              WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) + ' ' + [sOFSTP].[step_name]
        END AS [OnFailureAction],
        CASE
              WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
              ELSE 'Yes'
          END AS [IsScheduled],
        [sSCH].[name] AS [JobScheduleName],
        CASE [sJOB].[delete_level]
                WHEN 0 THEN 'Never'
                WHEN 1 THEN 'On Success'
                WHEN 2 THEN 'On Failure'
                WHEN 3 THEN 'On Completion'
          END AS [JobDeletionCriterion]
    FROM
        [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] 
            ON [sJSTP].[job_id] = [sJOB].[job_id]
        LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] 
            ON [sJSTP].[job_id] = [sOSSTP].[job_id]
                AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
        LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] 
            ON [sJSTP].[job_id] = [sOFSTP].[job_id]
                AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
        LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] 
            ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
        LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] 
            ON [sJOB].[category_id] = [sCAT].[category_id]
        LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] 
            ON [sJOB].[owner_sid] = [sDBP].[sid]
        LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] 
            ON [sJOB].[job_id] = [sJOBSCH].[job_id]
        LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] 
            ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
     WHERE
      [sCAT].[name] = @JOBCATEGORY
      [sJSTP].[command] LIKE '%' + @String + '%'
      OR [sJOB].[name] LIKE '%' + @String + '%'
    ORDER BY
        [JobName] ,
        [StepNo]

Exemplo de Uso:
💡

1. O administrador define a variável @JOBCATEGORY com o valor 'D2D_Alert' para filtrar os trabalhos que pertencem a essa categoria.
2. Define a variável @STRING com uma palavra-chave, como 'Backup' , para buscar trabalhos que contêm essa palavra no nome ou no comando das etapas.

Resultado Esperado:
📈
O resultado será um relatório organizado com todas as informações detalhadas sobre os trabalhos e suas etapas. Os dados serão filtrados conforme as variáveis definidas e ordenados por nome de trabalho e número da etapa. Por exemplo:

Conclusão:
✅
Este script SQL é uma ferramenta poderosa para administrar e monitorar trabalhos no SQL Server. Ele facilita a visualização das configurações dos trabalhos, incluindo detalhes cruciais para o gerenciamento eficiente dos processos automatizados. Além disso, a flexibilidade de filtros e a clareza do relatório permitem uma análise rápida e objetiva, essencial para manter o desempenho e a integridade do banco de dados.