SQLAgent - TSQL JobStep (Job % to SQL Job Name

SQLAgent - TSQL JobStep (Job % to SQL Job Name



select sp.program_name
from
sys.dm_exec_requests req
left join sys.sysprocesses sp on sp.spid=req.session_id
where (req.session_id>50 or req.session_id is null) and req.session_Id NOT IN (@@SPID)
order by logical_reads




go


program_name
SQLAgent - TSQL JobStep (Job 0x8BF803BD5BFFD3448392075F1E82E037 : Step 1)


what is the job name that execute?


SELECT CASE
   WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
   ELSE RTRIM([program_name])
  END ProgramName
  , Val1.UqID
  , Val1.UqIDStr
FROM sys.sysProcesses P
  CROSS APPLY
   (
   VALUES (
     CASE
      WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
       CAST(
        SUBSTRING(RTRIM([program_name]), 32, 8) + '-' +
        SUBSTRING(RTRIM([program_name]), 40, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 44, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 48, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 52, 12)
       AS uniqueidentifier)
      ELSE NULL
     END ,
     CASE
      WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
       CAST(CONVERT(VARBINARY(MAX),'0x' + SUBSTRING(RTRIM([program_name]), 32, 32),1) AS uniqueidentifier)
      ELSE NULL
     END
     )
   ) Val1(UqID, UqIDStr)
  OUTER APPLY
   (
   SELECT DISTINCT Name
   FROM MSDB.dbo.SysJobs
   WHERE CAST(Job_ID AS varchar(50)) = Val1.UqIDStr
     OR Job_ID =  Val1.UqID
     OR CAST(Job_ID AS varchar(50)) = Val1.UqID
   ) J
WHERE RTRIM([program_name]) ='SQLAgent - TSQL JobStep (Job 0x8BF803BD5BFFD3448392075F1E82E037 : Step 1)'  




Extract from
http://www.sqlservercentral.com/Forums/Topic1648616-392-1.aspx