How to find a SQL Server job with a binary job id

Problem

You may run the sp_WhoIsActive to check the currently active sessions in your SQL Server and find an active session from one of your jobs. if you look at the program_name column, you’ll see something like this:

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

So, how do you find the corresponding job?

Solution

You can find all of the SQL Server Agent jobs in the msdb.dbo.sysjobs table, but the job_id column is a uniqueidentifier while you have a binary value of your job. So, you would have to convert it to uniqueidentifier to be able to find the job you are looking for. You can do this using this snippet:

SELECT * 
FROM msdb.dbo.sysjobs
WHERE job_id = CAST(CONVERT(binary(16), '0x44D2817996D76447B116C4442A45F9EF', 1) as uniqueidentifier);