DECLARE @tblWHO TABLE (SPID bigint, Status varchar(250), Login varchar(300), Hostname varchar(300), BlkBy varchar(100), DBName varchar(300), Command nvarchar(500) ,CPUTime bigint, DiskIO bigint, LastBatch varchar(100), ProgramName varchar(300), SPID2 bigint, REQUESTID bigint) INSERT INTO @tblWHO EXEC sp_who2 SELECT * FROM @tblWho WHERE SPID IN (SELECT TRY_CAST(BlkBy as bigint) FROM @tblWHO WHERE BlkBy NOT IN(' .',' .')) OR COALESCE(TRY_CAST(BlkBy as bigint),0) > 0
v2: with queries
DECLARE @tblWHO TABLE (SPID bigint, Status varchar(250), Login varchar(300), Hostname varchar(300), BlkBy varchar(100), DBName varchar(300), Command nvarchar(500) ,CPUTime bigint, DiskIO bigint, LastBatch varchar(100), ProgramName varchar(300), SPID2 bigint, REQUESTID bigint) INSERT INTO @tblWHO EXEC sp_who2 SELECT tr.open_transaction_count, T.*, p.sql_handle, TEXT as Query FROM @tblWho T LEFT JOIN sys.sysprocesses p ON T.SPID = p.SPID LEFT JOIN sys.dm_tran_session_transactions tr on t.SPID = tr.session_id CROSS APPLY sys.dm_exec_sql_text(sql_handle)
v2b: with queries only for processing that blocking / being blocked
DECLARE @tblWHO TABLE (SPID bigint, Status varchar(250), Login varchar(300), Hostname varchar(300), BlkBy varchar(100), DBName varchar(300), Command nvarchar(500) ,CPUTime bigint, DiskIO bigint, LastBatch varchar(100), ProgramName varchar(300), SPID2 bigint, REQUESTID bigint) INSERT INTO @tblWHO EXEC sp_who2 SELECT tr.open_transaction_count, T.*, p.sql_handle, TEXT as Query FROM @tblWho T LEFT JOIN sys.sysprocesses p ON T.SPID = p.SPID LEFT JOIN sys.dm_tran_session_transactions tr on t.SPID = tr.session_id CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE T.SPID IN ( SELECT SPID FROM @tblWho WHERE SPID IN (SELECT TRY_CAST(BlkBy as bigint) FROM @tblWHO WHERE BlkBy NOT IN(' .',' .')) OR COALESCE(TRY_CAST(BlkBy as bigint),0) > 0 )
1 Comment
Thinan Pillay
-- Run the below script to find the Sage hanging issue or the sleeping SPID
DECLARE @tblWHO TABLE (SPID bigint, Status varchar(250), Login varchar(300), Hostname varchar(300), BlkBy varchar(100), DBName varchar(300), Command nvarchar(500)
,CPUTime bigint, DiskIO bigint, LastBatch varchar(100), ProgramName varchar(300), SPID2 bigint, REQUESTID bigint)
INSERT INTO @tblWHO
EXEC sp_who2
SELECT * FROM @tblWho WHERE SPID IN (SELECT TRY_CAST(BlkBy as bigint) FROM @tblWHO WHERE BlkBy NOT IN(' .',' .')) OR COALESCE(TRY_CAST(BlkBy as bigint),0) > 0
--- Find the SPID that is sleeping and KILL the task by executing the below query
KILL 174 -- Number will be the SPID