using SP_WhoIsActive trying to track down the job step

Question

Post reply

using SP_WhoIsActive trying to track down the job step

dwilliscp

SSCrazy Eights

Points: 9270
More actions
December 11, 2015 at 7:13 am

#303167

I am getting, for Program Name, SQLAgent - TSQL JobStep (Job 0xBEB303926F0CE848BA3F9858417DB773 : Step 3). The problem is that I do not have a job ID that matches. How do I find the job ID and Job step?
select * from sysjobsteps where job_id = '0xBEB303926F0CE848BA3F9858417DB773'
also tried
select * from sysjobsteps where step_uid = '0xBEB303926F0CE848BA3F9858417DB773'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11649 More actions · Answer 1

You mean something like this?

USE msdb

SELECT

s.name,

s.job_id,

sjs.step_name,

sjs.step_uid

FROM

sysjobs s

JOIN sysjobsteps sjs ON sjs.job_id = s.job_id

EDIT: Sorry, I didn't pay attention to the post title but it might have been worth reiterating in the post. I don't currently have any jobs running right now that I can check but I suspect the format is a little different that what the actual ID really is. Run the query above and look for the job and question in compare the two ID's.

EDIT 2: Ok, so I caught one of my jobs in progress. Just as I suspected the results from 'program_name' was:

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

But in fact the actual ID of the Job is:

06B8C1E3-EA6C-4570-80E5-8923E0DDE18D

If you try to look up documentation of 'sp_whoisactive' I'm sure you can find out why it's in that format but there you go.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Remove the ' from around the hex value

select * from sysjobsteps where job_id = 0xBEB303926F0CE848BA3F9858417DB773

The hex value is the GUID converted to hex and binary is passed in without quotes due to it being binary information not text.

Y.B. SSChampion Points: 11649 More actions · Answer 3

anthony.green (12/11/2015)
Remove the ' from around the hex value
select * from sysjobsteps where job_id = 0xBEB303926F0CE848BA3F9858417DB773
The hex value is the GUID converted to hex and binary is passed in without quotes due to it being binary information not text.

Ahh...thanks for the clarification Anthony. 'dwillis' I can confirm that this in fact works.