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:
But there's 2 things I don't like about it:
Here's some of what I currently have (but neither of these returns all the data I want together in 1 result set)
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:
Thanks for your help guys.
But there's 2 things I don't like about it:
- 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).
- 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.
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.