Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm attempting to determine the name of the SQL Server Agent Job that is currently running by comparing the output of master.sys.dm_exec_sessions.program_name to msdb..sysjobs.job_id

I'm using the following T-SQL to try to diagnose why I cannot get the results I want:

SELECT program_name, '|' + SUBSTRING(program_name,30,34) + '|' AS JobIDSubstring, t.text
FROM sys.dm_exec_sessions s 
    INNER JOIN sys.dm_exec_requests r on s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
--  INNER JOIN msdb..sysjobsteps sj on SUBSTRING(program_name,30,34) = cast(cast(sj.job_id as varbinary(max)) as varchar(max))
WHERE 
    program_name like 'SQLAgent%' ;

SELECT CAST(job_id as varbinary(max)) as JobStepID, CAST(step_id as varbinary(max)) as StepID, *
FROM msdb..sysjobsteps;

SELECT program_name, '|' + SUBSTRING(program_name,30,34) + '|' AS JobIDSubstring, t.text
FROM sys.dm_exec_sessions s 
    INNER JOIN sys.dm_exec_requests r on s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    INNER JOIN msdb..sysjobsteps sj on SUBSTRING(program_name,30,34) = cast(cast(sj.job_id as varbinary(max)) as varchar(max))
WHERE 
    program_name like 'SQLAgent%' ;

Results:

enter image description here

The last query should return results, however it returns zero rows since the JOIN clause is clearly not working. How should I construct this so the JOIN from sys.dm_exec_sessions to msdb..sysjobsteps works?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Stop using CAST and use CONVERT. Specifically because, in this case, you need to use a certain style parameter in order to get the right string comparison (otherwise it is just converting 0xwhatever to the string value represented by 0xwhatever, which is not 0xwhatever. Compare:

SELECT 
  CONVERT(VARCHAR(32), 0x48656C6C6F21),
  CONVERT(VARCHAR(32), 0x48656C6C6F21, 1);
  ----- only difference -------------^^^

Results:

------   --------------
Hello!   0x48656C6C6F21

Hello! is actually the string represented by the binary value. The second column is just the binary value displayed as a string. Big difference!

With that in mind, change the join criteria on your last query to:

INNER JOIN msdb.dbo.sysjobsteps sj -- why leave out dbo?
  ON SUBSTRING(s.[program_name],30,34) = -- prefix your columns
  CONVERT(VARCHAR(34), CONVERT(VARBINARY(32), sj.job_id), 1) -- no need for MAX types here
  ------- this is the important piece ------------------^^^
share|improve this answer
    
Thanks @aaron just the advice I was looking for! –  Max Vernon Aug 21 at 1:21
    
@MaxVernon No problem. But seriously, just use CONVERT always. –  Aaron Bertrand Aug 21 at 1:22

You might try looking at msdb.dbo.sysjobactivity. This table includes the session_id and the job_id for actively running jobs. I would expect you could take that and then connect back into the sessions DMVs to get your information.

share|improve this answer
    
Thanks for the heads-up about sysjobactivity. –  Max Vernon Aug 21 at 2:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.