Skip to content

Instantly share code, notes, and snippets.

@kveratis
Created August 16, 2013 20:32
Show Gist options
  • Save kveratis/6253300 to your computer and use it in GitHub Desktop.
Save kveratis/6253300 to your computer and use it in GitHub Desktop.
When monitoring processes on MS SQL, you can generally use the sp_who2 to list all the processes running with relevant data. However, this script expands on that by mapping processes that are part of SQL Jobs back to their job name and step.
declare @who table(spid int,status varchar(50),login varchar(50),hostname varchar(50),blkby varchar(50),dbname varchar(50),command varchar(500), cputime int,diskio int, lastbatch varchar(50),programname varchar(255),spid1 int,requestid int)
insert into @who execute sp_who2;
WITH RawJob as
(
SELECT spid
,CAST(CONVERT(varbinary, SUBSTRING(programname, 30, 34), 1) as uniqueidentifier) as JobId -- Grab JobId and convert hex to uniqueidentifier
,CAST(REPLACE(SUBSTRING(programname, 72, 2), ')', '') as INT) as JobStepId -- Grab step id
FROM @who
WHERE programname LIKE 'SQLAgent - TSQL%'
)
,ResolvedJob as
(
SELECT spid, JobId, name as JobName, rj.JobStepId, js.step_name as JobStepName
FROM RawJob rj
INNER JOIN [msdb].[dbo].[sysjobs] j ON rj.JobId = j.job_id
INNER JOIN [msdb].[dbo].[sysjobsteps] js ON j.job_id = js.job_id
)
SELECT a.spid
,a.status
,a.login
,a.hostname
,a.blkby
,a.dbname
,a.cputime
,a.diskio
,er.start_time as StartTime
,er.total_elapsed_time as ElapsedMS
,a.lastbatch
,a.command
,(CASE WHEN rj.JobName IS NOT NULL THEN rj.JobName ELSE a.programname END) as programname
,rj.JobStepId
,rj.JobStepName
FROM @who a
INNER JOIN sys.dm_exec_requests er ON a.spid = er.session_id
LEFT OUTER JOIN ResolvedJob rj ON a.spid = rj.spid
WHERE a.status NOT IN('BACKGROUND', 'sleeping')
order by a.diskio desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment