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 
