This is a personal pet hate of mine and of many people I’ve spoken to… we have all these lovely tools to obtain a list of what’s happening on our SQL Server but invariably sp_who, sp_who2, and sysprocesses all find it highly entertaining to provide you with a seemingly encrypted result instead of a job name.
So what happens when you encounter something like this in your output:
“SQLAgent - TSQL JobStep (Job 0xB2525BC0DBA1E844B582EFB55131EACF : Step 1)”
Well it’s actually not that hard to convert back into English, it’s just a matter of using the right function.
Basically all you need to do is to strip out the varbinary looking part and run it through the built in varbinary to hex convertor function.
This is the code I use, there may well be better out there, but this is what I came up with at least:
declare @job varchar(100), @jobID varchar(100)
select @job = 'SQLAgent - TSQL JobStep (Job 0xB2525BC0DBA1E844B582EFB55131EACF : Step 1)'
set @job = replace(@job, '(Job 0x', '(0x')
set @jobID = replace(@job, 'SQLAgent - TSQL JobStep (', '')
if (select charindex(' ', @jobID, 1)) > 0
set @jobID = substring(@jobID, 1, charindex(' ', @jobID, 1)-1)
select replace(@job, @jobID,
select distinct name
where master.dbo.fn_varbintohexstr(job_id) = @jobID
Simple as that. You could easily turn this into a function and use it in conjunction with sysprocesses to make sure that you always have fully readable output.