Home > General, T-sql > get running job name from sysprocesses

get running job name from sysprocesses

English: Labeled (SQL) database icon with shor...

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

Spid-1

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 πŸ™‚

Advertisements
Categories: General, T-sql Tags: , ,
  1. March 31, 2017 at 4:59 pm

    This is not a critique at all, I’m just mostly pointing out how WordPress alters the characters. The hyphens between “SQLAgent” and “Alert Engine” (and the other similar text in your WHERE clause) were altered from one hyphen character that SQL uses, to another hyphen character that SQL does not use. WordPress changed a “-” to “–”. (That’s a different character that looks similar.) I had to do a search and replace.

    Another thing was that SQL had a lot of trailing spaces after the “program_name” so I had to change it to this:
    WHERE
    program_name NOT LIKE ‘SQLAgent – Alert Engine%’ AND
    program_name NOT LIKE ‘SQLAgent – Generic Refresher%’ AND
    program_name NOT LIKE ‘SQLAgent – Email Logger%’ AND
    program_name NOT LIKE ‘SQLAgent – Job invocation engine%’ AND
    program_name NOT LIKE ‘SQLAgent – Job Manager%’

    Same idea. I just figured I’d pass this along to help anyone else who has a similar issue.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: