Hello guys,
Good afternoon.
After a while without posting, today I will demonstrate in this post how to identify jobs running via Query in SQL Server. With this query, you can identify the name and id of the job, which step the job is running, which query it has been running now and how long ago.
How to identify jobs running via Query
During the day to day, we can always monitor the queries that are running in our environment, especially using the great procedure sp_whoisactive. However, even with this excellent tool, it is not so trivial to find out via Query, if a job has been running, how long, which step, etc.
To this end, I will demonstrate a query (I created as a view for easy query) that helps us with this task:
| 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 |
If you are using a version of SQL Server earlier than version 2012, this query above will return an error message on account of TRY_CONVERT. To do this, I have another query, which uses the BINARY_CHECKSUM function to validate if the string is in BINARY format:
| 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 |
Or it has a slightly simpler query, which does not return the job session_id, hostname, user, program, nor the query being executed, but returns all information related to the Jobs that are running on the instance:
| 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 |
Another way to identify running jobs is by using the undocumented system procedure, xp_sqlagent_enum_jobs. This procedure is intended to list all instance jobs, stating the last run date and time, next run date and time, binary flag if the job is currently running (running), the number of the running step ( Current_Step), the number of Retry retries and the current state of execution.
| 1 | EXEC master.dbo.xp_sqlagent_enum_jobs 1, '' |
Working a little with this sp, we can get a more effective result for our need:
| 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 |
That's it folks!
See you!
