SQL DBA learning curve

English: Labeled (SQL) database icon with shortcut identification (Photo credit: Wikipedia)

Spid-1 (Photo credit: Eexlebots)

sometimes we need to find out the job details if multiple jobs are running at once and are involved in blocking or any other server performance issues …

the master..sysprocesses have column named program_name that represents the program from where a particular SPID is originating ..

for running SQL jobs this will be a hexadecimal string .. we can use below method to get the actual name of the job from sysjobs table

1> get the substring of the running job from sysprocess .. have filtered out generic refresher ETC

select substring(program_name,55,7) ‘to search’
from master..sysprocesses
where program_name like ‘%agent%’ and program_name not like ‘%DatabaseMail – SQLAGENT -%’
and program_name not in (‘SQLAgent – Alert Engine’,’SQLAgent – Generic Refresher’,’SQLAgent – Email Logger’,’SQLAgent – Job invocation engine’,’SQLAgent – Job Manager’)

output will be similar to below

Output :

to search
2FE3386
A68D437
3253286

Get the job details from MSDB database by using the results you got from above query and replace them in where clause

1>

select *
from msdb..sysjobs
where job_id like ‘%A68D437%’

OR

2> if you have more than one job running replace below job_id %strings%

select *
from msdb..sysjobs
where job_id like ‘%A68D437%’ or job_id like ‘%4E742EB%’ or job_id like ‘%FAEDF18%’

Enjoy 🙂