• DBA,  Junior DBA

    SQL server connection and blocking finding script

    SQL server connection and blocking finding script   -- For SQL 2000 select db_name(dbid),* from master.dbo.sysprocesses where spid> 51 and status <>'sleeping' -- and blocked <>0 -- To find the spid and connections which are all running select db_name (r.database_id),r.command,r.wait_resource,s.status,s.session_id, r.blocking_session_id,r.last_wait_type,r.open_transaction_count,sql_handle, * from sys.dm_exec_sessions s join sys.dm_exec_requests r on s.session_id =r.session_id --where s.status <>'sleeping' and s.session_id >=51 and s.session_id <>@@SPID -- exclude your session -- to find the blockings select db_name (r.database_id),r.command,r.wait_resource,s.status,s.session_id, r.blocking_session_id,r.last_wait_type,r.open_transaction_count, * from sys.dm_exec_sessions s join sys.dm_exec_requests r on s.session_id =r.session_id where r.blocking_session_id<>0 -- list blocking select * FROM sys.dm_exec_requests -- to find the code which is run by spid connections SELECT T.TEXT As Query, R.session_id, R.status, R.command…