2

I am running sp_whoisactive on my Prod and offload the data for analysis.

When I run sp_whoisactive or sp_who2 and if there is any SQL Agent Jobs running it would always display the Program Name like SQLAgent - TSQL JobStep (Job 0x984ECB0C94C14943B787BDE548F77311 : Step 1).

I want the job name to be displayed there instead of those numbers.

I have to manually copy that value and run it against select * from msdb..sysjobs where job_id = 0x984ECB0C94C14943B787BDE548F77311

Can any help me with this.

PS: tried to substring only the hexadecimal and convert it to uniqueidentifier and then run it to on the sysjobs tables as a variable but didn't work.

--This is what I tried to do

declare @substring nvarchar(200)   
select @substring = (SUBSTRING(@program,30,34))
select @substring

declare @jobname nvarchar(200) 
select @jobname = (select name from msdb..sysjobs where job_id = convert(uniqueidentifier,@substring))

select @jobname

--Error message

Msg 8169, Level 16, State 2, Line 22
Conversion failed when converting from a character string to uniqueidentifier.

3 Answers 3

2

It works if you go through a binary 16 before making it uniqueidentifier. You also need a proper format code when decoding the string to binary 16.

Below work for me. You obviously have to work out how to get the ProgramName for the right job into your variable.

DECLARE @a varchar(300)

--Get the job id
SET @a = 
(SELECT s.program_name
FROM sys.dm_exec_sessions AS s
WHERE s.program_name LIKE 'SQLAgent - TSQL JobStep%')

--Extract only binary represenation of the job id, as a string
SET @a = SUBSTRING(@a, 30, 34)

--Cast to binary 16 and use the format code 1 for CONVERT
--Then cast that binary 16 to uniqueidentifier
SELECT * FROM msdb..sysjobs WHERE job_id = CAST(CONVERT(binary(16), @a, 1) AS uniqueidentifier)
2
  • Thank you very much that worked...
    – Albert
    Feb 18, 2021 at 11:14
  • I have another similar scenario. The Program Name displays the SSIS package name but I want the Job name and Step ID of it. Is it possible ?
    – Albert
    Feb 18, 2021 at 13:43
4

You can do this easily with sp_WhoIsActive.

EXEC dbo.sp_WhoIsActive 
    @get_additional_info = 1, 
    @get_task_info = 2;

NUTS

If you click on the additional_info column, you'll see this:

NUTS

You don't explicitly need the @get_task_info parameter to retrieve the extra information, it's just a very useful general combination.

2
  • Thanks for your insight, Noted.
    – Albert
    Feb 18, 2021 at 13:29
  • I have another similar scenario. The Program Name displays the SSIS package name but I want the Job name and Step ID of it. I don't see it with @get_additional_info = 1.
    – Albert
    Feb 18, 2021 at 13:51
1

Change the Job_ID in the where clause

Select      Name
From        msdb.dbo.sysjobs
Where       master.dbo.fn_VarbinToHexStr(Job_ID) = '0x00000000000000000000000000000000'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.