Created
August 16, 2013 20:32
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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