Skip to content

Dirceu Resende

DBA SQL Server e Analista de BI (PowerBI, SSAS, SSIS, SSRS)

  • Consultoria
    • Consultoria de BI
    • Consultoria de Power BI
    • Consultoria SQL Server
  • Arquivo
  • Séries
    • Certificação
    • Segurança e Auditoria
    • Performance Tuning
    • O que mudou no T-SQL ?
    • Proteção de Dados
  • Vagas de Emprego
  • Eventos Data Platform
  • Sobre
  • Contato

Other Languages

Assinar blog por e-mail

Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.

Junte-se a 536 outros assinantes

Visualizações do Blog

1.645.448 views

Categorias

  • Apache / .htaccess (9)
  • Banco de Dados (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Auditoria (15)
      • Azure (2)
      • CLR (53)
      • Desenvolvimento de Query (83)
      • DMVs e Views de Catálogo (31)
      • Erros (22)
      • Ferramentas (12)
      • Formatação e Validação de Dados (23)
      • Funcionalidades pouco conhecidas (19)
      • Hacks (17)
      • Integrações (30)
      • Manipulação de Arquivos (13)
      • Manutenção (80)
      • Monitoramento (35)
      • O que não fazer (7)
      • OLE Automation (19)
      • Performance Tuning (22)
      • Python (1)
      • Segurança (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Carreira e Cursos (13)
  • Carreira, Cursos e Certificações (28)
  • Celulares (1)
  • Eventos e Palestras (63)
  • Programação (57)
    • C# (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • Javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Sem categoria (10)
  • SEO (4)
  • Virtualização (5)

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

  • Descontos da “Black Friday” nos Treinamentos de SQL Server (Comprem meu curso kkkkk) 27 de novembro de 2020
  • SQL Server – As “novas” funções GREATEST e LEAST 27 de novembro de 2020
  • SQL Server – Como saber a data do último login de um usuário 9 de novembro de 2020
  • Azure na Prática Gratuito #07 – Administrando Banco de Dados no Azure 5 de novembro de 2020
  • Analysis Services – An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file ‘tmcachemanager.cpp’, function ‘TMCacheManager::CreateEmptyCollectionsForAllParents’) 5 de novembro de 2020
  • 22 de agosto de 2015
  • 0
  • Banco de Dados DMVs e Views de Catálogo Manutenção Monitoramento SQL Server SQL Server Agent

Como identificar os jobs em execução via Query no SQL Server

Visualizações: 10.242
Tempo de Leitura: 4 minutos

Olá Pessoal,
Boa tarde.

Depois de um tempinho sem postar, hoje vou demonstrar nesse post como identificar os jobs em execução via Query no SQL Server. Com essa query, é possível identificar o nome e id do job, step que o job está executando, qual query ele está rodando agora e há quanto tempo.

Como identificar os jobs em execução via Query

Durante o dia a dia, podemos ficar sempre monitorando as queries que estão em execução em nosso ambiente, principalmente utilizando a ótima procedure sp_whoisactive. Entretanto, mesmo com essa excelente ferramenta, não é tão trivial descobrir via Query, se um job está em execução, há quanto tempo, em qual step, etc.

Com esse intuito, vou demonstrar uma query (eu criei como uma view para consulta fácil) que nos ajuda nessa tarefa:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT
    F.session_id,
    A.job_id,
    C.name AS job_name,
    F.login_name,
    F.[host_name],
    F.[program_name],
    A.start_execution_date,
    CONVERT(VARCHAR, CONVERT(VARCHAR, DATEADD(ms, ( DATEDIFF(SECOND, A.start_execution_date, GETDATE()) % 86400 ) * 1000, 0), 114)) AS time_elapsed,
    ISNULL(A.last_executed_step_id, 0) + 1 AS current_executed_step_id,
    D.step_name,
    H.[text]
FROM
    msdb.dbo.sysjobactivity                     A   WITH(NOLOCK)
    LEFT JOIN msdb.dbo.sysjobhistory            B   WITH(NOLOCK)    ON A.job_history_id = B.instance_id
    JOIN msdb.dbo.sysjobs                       C   WITH(NOLOCK)    ON A.job_id = C.job_id
    JOIN msdb.dbo.sysjobsteps                   D   WITH(NOLOCK)    ON A.job_id = D.job_id AND ISNULL(A.last_executed_step_id, 0) + 1 = D.step_id
    JOIN (
        SELECT CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) AS job_id, MAX(login_time) login_time
        FROM sys.dm_exec_sessions WITH(NOLOCK)
        WHERE [program_name] LIKE 'SQLAgent - TSQL JobStep (Job % : Step %)'
        GROUP BY CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER)
    )                                           E                   ON C.job_id = E.job_id
    LEFT JOIN sys.dm_exec_sessions              F   WITH(NOLOCK)    ON E.job_id = CAST(TRY_CONVERT( BINARY(16), SUBSTRING(F.[program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) AND E.login_time = F.login_time
    LEFT JOIN sys.dm_exec_connections           G   WITH(NOLOCK)    ON F.session_id = G.session_id
    OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) H
WHERE
    A.session_id = ( SELECT TOP 1 session_id FROM msdb.dbo.syssessions    WITH(NOLOCK) ORDER BY agent_start_date DESC )
    AND A.start_execution_date IS NOT NULL
    AND A.stop_execution_date IS NULL

Caso você esteja utilizando uma versão do SQL Server anterior a versão 2012, essa query acima irá retornar uma mensagem de erro por conta do TRY_CONVERT. Para isso, tenho uma outra query, que utiliza a função BINARY_CHECKSUM para validar se a string está no formato BINARY:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT
    F.session_id,
    A.job_id,
    C.name AS job_name,
    F.login_name,
    F.[host_name],
    F.[program_name],
    A.start_execution_date,
    CONVERT(VARCHAR, CONVERT(VARCHAR, DATEADD(ms, ( DATEDIFF(SECOND, A.start_execution_date, GETDATE()) % 86400 ) * 1000, 0), 114)) AS time_elapsed,
    ISNULL(A.last_executed_step_id, 0) + 1 AS current_executed_step_id,
    D.step_name,
    H.[text]
FROM
    msdb.dbo.sysjobactivity                     A   WITH(NOLOCK)
    LEFT JOIN msdb.dbo.sysjobhistory            B   WITH(NOLOCK)    ON A.job_history_id = B.instance_id
    JOIN msdb.dbo.sysjobs                       C   WITH(NOLOCK)    ON A.job_id = C.job_id
    JOIN msdb.dbo.sysjobsteps                   D   WITH(NOLOCK)    ON A.job_id = D.job_id AND ISNULL(A.last_executed_step_id, 0) + 1 = D.step_id
    JOIN (
        SELECT CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) AS job_id, MAX(login_time) login_time
        FROM sys.dm_exec_sessions WITH(NOLOCK)
        WHERE [program_name] LIKE 'SQLAgent - TSQL JobStep (Job % : Step %)'
        GROUP BY CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER)
    )                                           E                   ON C.job_id = E.job_id
    LEFT JOIN sys.dm_exec_sessions              F   WITH(NOLOCK)    ON E.job_id = (CASE WHEN BINARY_CHECKSUM(SUBSTRING(F.[program_name], 30, 34)) > 0 THEN CAST(TRY_CONVERT( BINARY(16), SUBSTRING(F.[program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) ELSE NULL END) AND E.login_time = F.login_time
    LEFT JOIN sys.dm_exec_connections           G   WITH(NOLOCK)    ON F.session_id = G.session_id
    OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) H
WHERE
    A.session_id = ( SELECT TOP 1 session_id FROM msdb.dbo.syssessions    WITH(NOLOCK) ORDER BY agent_start_date DESC )
    AND A.start_execution_date IS NOT NULL
    AND A.stop_execution_date IS NULL

Ou ainda tem uma query um pouco mais simples, que não retorna a session_id do job, hostname, usuário, programa e nem a query sendo executada, mas retorna todas as informações relativas aos Jobs que estão sendo executados na instância:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
    A.job_id,
    C.name AS job_name,
    E.name AS job_category,
    C.[enabled],
    C.[description],
    A.start_execution_date,
    A.last_executed_step_date,
    A.next_scheduled_run_date,
    CONVERT(VARCHAR, CONVERT(VARCHAR, DATEADD(SECOND, ( DATEDIFF(SECOND, A.start_execution_date, GETDATE()) % 86400 ), 0), 114)) AS time_elapsed,
    ISNULL(A.last_executed_step_id, 0) + 1 AS current_executed_step_id,
    D.step_name
FROM
    msdb.dbo.sysjobactivity                 A   WITH(NOLOCK)
    LEFT JOIN msdb.dbo.sysjobhistory        B   WITH(NOLOCK)    ON  A.job_history_id = B.instance_id
    JOIN msdb.dbo.sysjobs                   C   WITH(NOLOCK)    ON  A.job_id = C.job_id
    JOIN msdb.dbo.sysjobsteps               D   WITH(NOLOCK)    ON  A.job_id = D.job_id AND ISNULL(A.last_executed_step_id, 0) + 1 = D.step_id
    JOIN msdb.dbo.syscategories             E   WITH(NOLOCK)    ON  C.category_id = E.category_id
WHERE
    A.session_id = ( SELECT TOP 1 session_id FROM msdb.dbo.syssessions    WITH(NOLOCK) ORDER BY agent_start_date DESC )
    AND A.start_execution_date IS NOT NULL
    AND A.stop_execution_date IS NULL

Jobs_Execucao1

Uma outra forma de identificar os jobs em execução, é utilizando a procedure de sistema não documentada, xp_sqlagent_enum_jobs. Essa procedure tem o intuito de listar todos os jobs da instância, informando a data e hora da última execução, data e hora da próxima execução, flag binária se o job está em execução no momento (running), o número do step em execução (Current_Step), o número de tentativas do Retry e o estado atual da execução.

Transact-SQL
1
EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''

Jobs_Execucao2

Trabalhando um pouquinho com essa sp, podemos obter um resultado mais eficaz para a nossa necessidade:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
IF (OBJECT_ID('tempdb..#Retorno') IS NOT NULL) DROP TABLE #Retorno
CREATE TABLE #Retorno (
    Job_Id UNIQUEIDENTIFIER,
    Last_Run_Date INT,
    Last_Run_Time INT,
    Next_Run_Date INT,
    Next_Run_Time INT,
    Next_Run_Schedule_ID INT,
    Requested_To_Run INT,
    Request_Source INT,
    Request_Source_ID VARCHAR(MAX),
    Running BIT,
    Current_Step INT,
    Current_Retry_Attempt INT,
    [State] SMALLINT
)    
    
INSERT INTO #Retorno
EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''
 
 
IF (OBJECT_ID('tempdb..#Resultado') IS NOT NULL) DROP TABLE #Resultado
SELECT
    Job_Id,
    (CASE WHEN Last_Run_Date > 0 THEN msdb.dbo.agent_datetime(Last_Run_Date, Last_Run_Time) END) AS Dt_Ultima_Execucao,
    (CASE WHEN Next_Run_Date > 0 THEN msdb.dbo.agent_datetime(Next_Run_Date, Next_Run_Time) END) AS Dt_Proxima_Execucao,
    Next_Run_Schedule_ID,
    Requested_To_Run,
    Request_Source,
    Request_Source_ID,
    Running,
    Current_Step,
    Current_Retry_Attempt,
    State
INTO
    #Resultado
FROM
    #Retorno
WHERE
    Running = 1 -- Em Execução
    
    
SELECT * FROM #Resultado

Jobs_Execucao3

É isso aí, pessoal!
Até mais!

Tags: em execuçãojobslistarqueryrodandorunningsqlsql servertsqlverxp_sqlagent_enum_jobs

You may also like...

  • Prova de certificação Microsoft MCSE 70-774 (Perform Cloud Data Science with Azure Machine Learning) de graça (beta) até 31/03/2017

  • SQL Server – The data types datetime and time are incompatible in the add operator

  • Utilizando sequences em user defined functions no SQL Server

  • Next Como identificar os usuários conectados no SQL Server
  • Previous Como calcular dias úteis no SQL Server (Tabela dCalendario)

Deixe uma resposta Cancelar resposta

Dirceu Resende © 2020. All Rights Reserved.