sqlskills-logo-2015-white.png

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.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.