Sql Server 2005, recreate process activity monitor

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,530
Location
Lansing, MI; USA
Programming Experience
10+
I've got a Sql Server 2005 server that I monitor 4 critical jobs that run each morning and I've noticed that process activity monitor (see pic below) has almost all of the info I need for this:
attachment.php


But there's 2 things I don't like about it:
  1. It doesn't show the sql (a preview of it at least) for each process. I have to run "dbcc inputbuffer(<SPID>)" for each manually (I output it as text, not a table).
  2. It fails to pull the needed data very frequently. Most mornings it takes 10 or 15 tries to just open the window most mornings, and if I get it open half the time it fails to update the data, a timeout error.
So I've started playing around with creating a stored procedure I can just call that would show the info that I would like to see (I would like to include a varchar(500) field for a preview of the sql statement it's running) but I'm stuck on being able to get all of the columns from that screenshot (the screenshot has all the columns I need)

Here's some of what I currently have (but neither of these returns all the data I want together in 1 result set)

VB.NET:
SELECT s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
   s.program_name, s.LoginName, ObjectName = OBJECT_NAME(objectid, s.dbid), Definition = CAST(text AS VARCHAR(MAX))
INTO #Processes
FROM sys.sysprocesses s
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE s.spid > 50;

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
 AS
 (
      SELECT
       s.SPID, s.BlockingSPID, s.Definition,
       ROW_NUMBER() OVER(ORDER BY s.SPID),
       0 AS LevelRow
     FROM
       #Processes s
       JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
     WHERE
       s.BlockingSPID = 0
     UNION ALL
     SELECT
       r.SPID,  r.BlockingSPID, r.Definition,
       d.RowNo,
       d.LevelRow + 1
     FROM
       #Processes r
      JOIN Blocking d ON r.BlockingSPID = d.SPID
     WHERE
       r.BlockingSPID > 0
 )

 SELECT * FROM Blocking
 ORDER BY RowNo, LevelRow;

drop table #Processes;
VB.NET:
SELECT p.spid
, p.blocked
,convert(char(12), d.name) db_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, program_name
, cmd
, p.status
, login_time
, last_batch
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.sid = l.sid
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )

Eventually it would be nice to have this return a view so I can filter on a specific database in a where clause, something like:
VB.NET:
Select * from ProcessList Where [Database] = 'outdoor'

Thanks for your help guys.
 
Back
Top