Getting the full name of SQL Jobs including the steps in execution

While monitoring a database server, we may need to know some details about the sessions, connections and requests that migh be causing performance or blocking issues so that we can take actions to fix them. In doing so, one very important piece of information is the program name that is connected to the database engine. Broadly, it is posible to see the program name in detail by using system stored procedures or DMVs such as 'sp_who2' and 'sys.dm_exec_sessions'. Nevertheless, not every name of the programs may be easy to interpret, especially when it comes to SQL Jobs. For instance, if you detected that a SQL Job is the root of the problem and then needed to know which SQL Job it is, the column program_name of 'sp_who2' or 'sys.dm_exec_sessions' woud only give us the SQL Job ID based on the following format:

SQLAgent - TSQL JobStep (Job 0x2613DA812CD2D248A9BA377DE6DEF355 : Step 1)

Obviously, we cannot do much with that info because there is no SQL Job name, and even worse, no SQL Job step name. However,  we can figure out the name of the SQL Job in msdb.dbo.sysjobs by using the ID.

SELECT * FROM msdb.dbo.sysjobs WHERE job_id=0x2613DA812CD2D248A9BA377DE6DEF355

Despite the fact that it may be relativily easy to get the name of the SQL Job, it is not enough because it is of paramount importance to know the name of the step in execution, and keep in mind that doing this manually this every time when needed is going to be an uphill battle as it is arduous and not optimal, especially if there are many SQL Jobs running and causing struggles. Thinking of this situation, I created a script to automate the task of figuring out details related to SQL Jobs such as the name and also the step name that is running. To be more precise, this logic is inside a function called "ufn_GetJobStepNameDesc" that takes the value of the "program_name" column and returns the name of the SQL Job and the step in execution. Let's take a look at the following code whereby we also filter out the sessions used by SQL Jobs.

SELECT session_id, login_time, login_name, [status], writes,

       logical_reads, [language], DB_NAME(database_id) DatabaseName,

          dbo.ufn_GetJobStepNameDesc([program_name]) AS SQLJobDescription

FROM sys.dm_exec_sessions where [program_name] like 'SQLAgent - TSQL%'

As you can see, I have highlighted the use of the function, and also added other important columns to look at as part of monitoring. So, using dbo.ufn_GetJobStepNameDesc([program_name]) the final outcome would be like this: SQLAgent - TSQL JobStep "<Name of the job step>" (Job: <Job name>).  For instance: SQLAgent - TSQL JobStep "Updating_Accounts" (Job: SAP_Financial_Process)

Here I share with you my script so that you can check it thoroughly and then make the most out if it.

USE [master]

GO

CREATE FUNCTION  [dbo].[ufn_GetJobStepNameDesc] (@step_name_desc VARCHAR(MAX))

RETURNS VARCHAR(max)

AS

BEGIN

       DECLARE @full_step_name_desc VARCHAR(MAX)

       DECLARE @jobstep_id_start INT

       DECLARE @jobstep_id_len INT

       DECLARE @jobstep_id INT

       SELECT @jobstep_id_start=CHARINDEX(': Step', @step_name_desc)+7, @jobstep_id_len=CHARINDEX(')', @step_name_desc)-@jobstep_id_start

       set @jobstep_id= CAST(SUBSTRING(@step_name_desc, @jobstep_id_start,@jobstep_id_len) AS INT)

       DECLARE @job_id_start INT

       DECLARE @job_id_len INT

       DECLARE @hexa_job_id VARBINARY(MAX)

       SELECT @job_id_start=CHARINDEX('(Job 0', @step_name_desc)+5,  @job_id_len=CHARINDEX(':', @step_name_desc)-@job_id_start

       SET @hexa_job_id=CONVERT( VARBINARY, RTRIM(LTRIM(SUBSTRING(@step_name_desc, @job_id_start,@job_id_len))),1)

      

       SELECT @full_step_name_desc='SQLAgent - TSQL JobStep "' + step_name+'" (Job: ' + j.[name] +')'

       FROM msdb.dbo.sysjobsteps js

            INNER JOIN msdb.dbo.sysjobs j ON j.job_id=js.job_id

       WHERE step_id=@jobstep_id and j.job_id=CAST(@hexa_job_id  AS UNIQUEIDENTIFIER)

       RETURN ( @full_step_name_desc)

END

That is all for now, I hope you find this post helpful. Let me know any remarks you may have. Stay tuned.