This is an article that I had never planned until today, late in the evening  when I was browsing through the forums at SQLServerCentral. I happened to see a thread where the user who posted the question was facing trouble identifying the name of the SQL Agent Job. You can visit the thread by clicking here. (Not required if you have actually been redirected from that thread).

Problem

When I initially looked at the post, I thought this is not a big deal and suggested to use Job Activity Monitor to look at the Active Jobs. What happenned next was made me sit for couple of hours on a Friday night to solve the problem, that eventually made in me write this article. The user replied back saying, what if there are multiple Jobs concurrently running? How can I identify which individual record in the activity monitor corresponds to a particular Job, since Activity Monitor displays the Job’s hexadecimal code instead of displaying the Job name.

I started to query the sys.sysprocesses and the sysjobs tables from Master and MSDB databases. From the first glance at the Name column of the processes and the Job_Id column of Msdb.dbo.sysjobs. After close observations of a particular Job records, I could find that the hexadecimal values in the program_name column of the sys.sysprocesses and the Job_Id column in Msdb.dbo.sysjobs are nothing but a rearrangement of the first 20 characters, the last 12 characters are same (at least from my observations until now).

When I could figure out the order of the characters, all I had to do was reshuffle those and compare, the complete script of the stored procedure is available below.

Use the following script to create the stored Procedure that is required to fetch the SQL Job Name.

When you next time identify in Activity Monitor that a Job Step is executing for more than the desired amount of time or using more system resources, pick that spid and execute the command as shown below.

EXEC dbo.FETCH_ACTIVE_JOB_NAME @SPID = 60

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty