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:
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:
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:
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 |
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.
1 | EXEC master.dbo.xp_sqlagent_enum_jobs 1, '' |
Trabalhando um pouquinho com essa sp, podemos obter um resultado mais eficaz para a nossa necessidade:
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 |
É isso aí, pessoal!
Até mais!