Technical Article

SQL Server Current Activity SuperCheck

,

This gives a quick check of the current activity on a SQL Server. I developed this after getting continually frustrated with "Current Activity" in SQL/EM getting blocked by processes when I needed to see blocking issues on the server. It has been immensely valuable to me in quickly identifying production-stopping issues on the database.

It works best using a grid view. It will display all connections that are active or have open transactions (although this is easily adjustable). The niftiest part, though, it that it shows the active command text (DBCC INPUTBUFFER) for each SPID.

SET NOCOUNT ON
/*
Author:
Benjamin Bolte
brbolte@msn.com

Purpose:
Identify active or blocking connections, and list the active command on the connection.
*/
/*
Status Definitions, per Books Online:

BackgroundSPID is performing a background task. 
SleepingSPID is not currently executing. This usually indicates that the SPID is awaiting a command from the application. 
RunnableSPID is currently executing. 
Dormant  Same as Sleeping, except Dormant also indicates that the SPID has been reset after completing an RPC event. The reset cleans up resources used during the RPC event. This is a normal state and the SPID is available and waiting to execute further commands.  
RollbackThe SPID is in rollback of a transaction. 
DefwakeupIndicates that a SPID is waiting on a resource that is in the process of being freed. The waitresource field should indicate the resource in question. 
SpinloopProcess is waiting while attempting to acquire a spinlock used for concurrency control on SMP systems 

*/create table #ProcCheck(
Status varchar(50) ,
SPID int ,
CPU int ,
Pys_IO int ,
WaitTime int ,
BlockSPID int ,
LastCmd varchar(500) ,
HostName varchar(36) ,
ProgName varchar(100) ,
NTUser varchar(50) ,
LoginTime datetime ,
LastBatch datetime ,
OpenTrans int)

create table #ProcInfo(
EventType varchar(100) ,
Parameters int ,
EventInfo varchar(7000)
)

INSERT INTO #ProcCheck(Status, SPID, CPU, Pys_IO, WaitTime, BlockSPID, HostName, ProgName, NTUSer, LoginTime, LastBatch, OpenTrans)
SELECT status, SPID, CPU, Physical_IO, WaitTime, Blocked, SUBSTRING(HostName, 1, 36), SUBSTRING(Program_Name, 1, 100), SUBSTRING(nt_username, 1, 50), Login_Time, Last_Batch, Open_Tran
FROM master..sysprocesses
where (blocked > 0
or spid in (select blocked from master..sysprocesses (NOLOCK) where blocked > 0)
or open_tran > 0)
and SPID <> @@SPID

declare @spid int ,
@cmd varchar(7000)

declare Procs cursor fast_forward for
SELECT SPID FROM #ProcCheck

OPEN Procs

FETCH NEXT FROM Procs INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'DBCC INPUTBUFFER(' + CONVERT(varchar, @SPID) + ')'

INSERT INTO #ProcInfo
EXEC(@cmd)

SELECT @cmd = EventInfo
FROM #ProcInfo

DELETE FROM #ProcInfo

UPDATE #ProcCheck
SET LastCmd = SUBSTRING(@cmd, 1, 500)
WHERE SPID = @SPID

FETCH NEXT FROM Procs INTO @SPID

END

CLOSE Procs
DEALLOCATE Procs

SELECT * FROM #ProcCheck

DROP TABLE #ProcCheck
DROP TABLE #ProcInfo

Rate

4.2 (5)

Share

Share

Rate

4.2 (5)