How to link sysprocesses to sysjobs 🙂
Re:
sysprocesses.program_name 'SQLAgent - TSQL JobStep (Job 0xF1CA1968020D2647B0E1D42483AA2695 : Step 1)'
but how to match it to relevant sysjobs.job_id
There is a simpler way to get the same results... found a post from another user 🙂
-- link sysprocesses to sysjobs and sysjobsteps
select
p.spid,
p.program_name,
j.job_id,
job_name = coalesce(j.[name], ''),
jobstep_id = coalesce(js.[step_id], ''),
jobstep_name = coalesce(js.[step_name], '')
from master.dbo.sysprocesses p
--left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(master.dbo.fn_varbintohexstr(j.job_id),3,100)
left outer join msdb.dbo.sysjobs j on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)
left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id and js.step_id = substring( p.program_name, 72, len(p.program_name)-72 )
where
p.spid >= 50
and p.status <> 'sleeping'
and p.spid <> @@spid
Per:
http://www.sqlservercentral.com/Forums/Topic233404-110-1.aspx#bm829591
http://www.sqlservercentral.com/Forums/Topic829628-8-1.aspx#bm829636