SPID OF RUNNING JOBS IN SQL SERVER

Today I was alerted by one of our long running monitor Job saying that one particular job is running for longer duration than its Average running time.


Now I had logged into my SQL server and queried the sysprocesses table with the below command to get the SPID of the job which is taking much time…

Select * from sys.sysprocesses where program_name like ‘%SQLAgent - TSQL JobStep%’

but unfortunately I got more than 10 records which means there are 10 jobs running on the instance and you know that Program_name columns value has Hexadecimal form,So I need to get the SPID of the Job and the Job Name by using an SQL query ..


So if you guys have the same situation then below query will help you in retrieving the SPID and job names …..

QUERY:

SELECT DISTINCT SP.SPID, SJ.NAME FROM MASTER.DBO.SYSPROCESSES SP JOIN MSDB.DBO.SYSJOBS SJ ON

MASTER.DBO.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), JOB_ID)) COLLATE LATIN1_GENERAL_CI_AI =

SUBSTRING(REPLACE(PROGRAM_NAME, 'SQLAGENT - TSQL JOBSTEP (JOB ', ''), 1, 34)

Hope this helps…..