Finding a Deadlocked SQL Server Agent Job Name

Short and simple post – filed under “Joe – you’ve already forgotten about this twice, so write this down somewhere.”

Let’s say you have deadlock output that shows the following information for the “clientapp” attribute:

clientapp=SQLAgent – TSQL JobStep (Job 0xB813B96C59E6004CA8CD542D8A431A2E : Step 1)

Based on this output, we know a SQL Server Agent Job is involved, but what is the SQL Server Agent Job name?

Tested on SQL Server 2012 SP1, I can find it with the following query:

SELECT  [job_id] ,
        [name]
FROM [msdb].[dbo].[sysjobs]
WHERE [job_id] = 0xB813B96C59E6004CA8CD542D8A431A2E;
GO

I’ve forgotten about this twice over time because the job_id uses a uniqueidentifier data type (in this example, the job_id value is 6CB913B8-E659-4C00-A8CD-542D8A431A2E).  And as an aside, the query execution plan shows a CONVERT_IMPLICIT as I would expect and uses a Clustered Index Seek.