Home How to find the session id of running jobs
Reply: 0

How to find the session id of running jobs

user1714
1#
user1714 Published in July 17, 2018, 7:57 am

Trying to get the session id of executing jobs

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x 
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select * 
    from msdb.dbo.sysjobs j 
    inner join JobDetails jd on j.job_id = jd.Job_Id

but it throws the following error

Msg 8169, Level 16, State 2, Line 47 Conversion failed when converting from a character string to uniqueidentifier.

I tried to cast the job_id to varbinary but it produces no result

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x 
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select * 
    from msdb.dbo.sysjobs j 
    inner join JobDetails jd on cast(j.job_id as varbinary) = jd.Job_Id

but when I copy paste a job_id from cte like this

select job_id, name 
    from msdb..sysjobs 
    where job_id = 0x128A47A31EAB8F4DA1AD852093D815F5 

it works. Any idea how to fix this query

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.311391 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO