Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • CONSUTING
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 547 other subscribers

Blog Views

1.789.868 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft products (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 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') November 5th, 2020
  • August 22st, 2015
  • 0
  • Database DMVs and Catalog Views Maintenance Monitoring SQL Server SQL Server Agent

How to identify jobs running via Query in SQL Server

Views: 11.075
Reading Time: 4 minutes

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:

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

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:

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

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:

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_Execution1

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.

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

Jobs_Execution2

Working a little with this sp, we can get a more effective result for our need:

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_Execution3

That's it folks!
See you!

tags: runningjobslistQueryrunning sqlsql servertsqlseexp_sqlagent_enum_jobs

You may also like ...

  • SQL Server - How to Practically Share Your Execution Plans on the Web

  • How to estimate how much time is left to finish the backup on SQL Server?

  • Troubleshooting .NET Framework DLL Assembly Signing Issues in SQL Server

  • Next How to identify users logged in to SQL Server
  • Previous How to calculate business days in SQL Server (dCalendar table)

Leave a Comment Cancel reply

Dirceu Resende © 2021. All Rights Reserved.