Hola, chicos,
Buenas tardes.

Después de un tiempo sin publicar, hoy demostraré en esta publicación cómo identificar trabajos en ejecución mediante Consulta en SQL Server. Con esta consulta es posible identificar el nombre y el id del trabajo, el paso que está ejecutando el trabajo, qué consulta está ejecutando ahora y por cuánto tiempo.

Cómo identificar trabajos en ejecución mediante consulta

Durante la vida cotidiana, siempre podemos monitorear las consultas que se ejecutan en nuestro entorno, principalmente utilizando el excelente procedimiento. sp_whoisactive. Sin embargo, incluso con esta excelente herramienta, no es tan trivial saber mediante Query si un trabajo ha estado ejecutándose, durante cuánto tiempo, en qué paso, etc.

Con esto en mente, demostraré una consulta (la creé como una vista para facilitar la consulta) que nos ayuda con esta tarea:

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

Si está utilizando una versión de SQL Server anterior a la versión 2012, esta consulta anterior devolverá un mensaje de error debido a TRY_CONVERT. Para hacer esto, tengo otra consulta, que utiliza la función BINARY_CHECKSUM para validar si la cadena está en formato BINARIO:

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

O hay una consulta un poco más simple, que no devuelve el trabajo session_id, nombre de host, usuario, programa o la consulta que se está ejecutando, pero devuelve toda la información relacionada con los trabajos que se están ejecutando en la instancia:

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
Trabajos_Ejecución1

Otra forma de identificar trabajos en ejecución es mediante el procedimiento del sistema no documentado, xp_sqlagent_enum_jobs. Este procedimiento tiene como objetivo listar todos los trabajos en la instancia, informando la fecha y hora de la última ejecución, fecha y hora de la próxima ejecución, indicador binario si el trabajo se está ejecutando actualmente (running), el número del paso que se está ejecutando (Current_Step), el número de intentos de reintento y el estado actual de la ejecución.

EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''

Jobs_Execucao2
Trabajos_Ejecución2

Trabajando un poco con este sp, podremos obtener un resultado más eficaz para nuestras necesidades:

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
Trabajos_Ejecución3

¡Eso es todo, amigos!
¡Hasta luego!