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.
-- ============================================================================= -- SQL Server Current Activity SuperCheck -- -- Identify active or blocking connections, and list the active command on the -- connection. -- -- Status Field Definitions -- ------------------------ -- Background SPID is performing a background task. -- Sleeping SPID is not currently executing. This usually indicates that -- the SPID is awaiting a command from the application. -- Runnable SPID 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. -- Rollback The SPID is in rollback of a transaction. -- Defwakeup Indicates that a SPID is waiting on a resource that is in the -- process of being freed. The wait resource field should indicate -- the resource in question. -- Spinloop Process is waiting while attempting to acquire a spin-lock used -- for concurrency control on SMP systems -- -- Author: Benjamin Bolte, <email@example.com> -- http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/30042/ -- ============================================================================= set nocount on; 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;