SQL Query - Activity Monitor

Hi All,

Here is quick one.

There is times when you look at the SQL Server activity monitor and you find out that some job triggered by the SQL Agent it's consuming resources or locking processes, and many times when that happen you could have a hard time getting the info from the visual interface from Jobs Activity because you have many jobs running at the same time and you need to identify which is the problematic job.

The problem is that when that happen when you look at the ProgramName column you only will see something like this:




I've used a query to the master..sysprocesses table to return the results in the image above (it's really the same the Activity Monitor GUI does).

Well, as you can see the JobId it's coded and there is no clear reference to which job is generating the problem and as I said, if you have many jobs running at the same time it's a little hard to get the job identified.

Well, for that cases I wrote a query that displays the same information from the Activity Monitor, BUT the decodes the JobName from the JobID and displays the JobName so you can quickly identify the source of your problem.




Here is the code you can execute.

'----------------------------------------------------------------------------------
declare @jobid varchar(60),
@jobname varchar(200),
@stmt nvarchar(100),
@stmt_select varchar(100)

if object_id('tempdb..#procesos')is not null
drop table #procesos;

select spid,
loginame,
blocked,
waittime,
lastwaittype,
db_name(dbid) as dbname,
cpu,
physical_io,
memusage,
open_tran,
status,
cmd,
hostname,
login_time,
last_batch,
program_name,
uid,
hostprocess
into #procesos
from master.dbo.sysprocesses
order by spid

declare cur_procesos cursor for
select substring(program_name,patindex('%0x%',program_name), patindex('%:%',program_name)-patindex('%0x%',program_name))
from #procesos
where program_name like '%Jobstep%'

open cur_procesos
fetch next from cur_procesos into @jobid

while @@fetch_status = 0
begin
set @stmt = N'select @stmt_jobname=name from msdb.dbo.sysjobs where job_id = '+@jobid

exec sp_executesql @stmt,
N'@stmt_jobname varchar(100) OUTPUT',
@stmt_jobname = @jobname OUTPUT;

if (@jobname is not null and @jobname <> '')
begin
set @jobname = 'SQLAgentJob: '+@jobname;
update #procesos
set program_name = @jobname
where current of cur_procesos;
end

fetch next from cur_procesos into @jobid
end

close cur_procesos
deallocate cur_procesos

set @stmt_select = 'select * from #procesos'
exec (@stmt_select)

drop table #procesos

'----------------------------------------------------------------------------------

I hope this help you.

RegardS!

MAX!