******************************************************************************************/
CREATE FUNCTION [dbo].dba_GetStatementForSpid( @spid SMALLINT ) RETURNS NVARCHAR(4000) BEGIN DECLARE @SqlHandle BINARY(20) DECLARE @SqlText NVARCHAR(4000) SELECT @SqlHandle = sql_handle FROM sys.sysprocesses WITH (nolock) WHERE spid = @spid SELECT @SqlText = [text] FROM sys.dm_exec_sql_text(@SqlHandle) RETURN @SqlText END GO
/*****************************************************************************************
STEP 4: List the current blocking session information
****************************************************************************************/
SELECTes.session_id,es.HOST_NAME,DB_NAME(database_id) AS DatabaseName, CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN (SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35))))))
ELSE es.program_name END AS program_name ,es.login_name ,bes.session_id AS Blocking_session_id,MASTER.DBO.dba_GetStatementForSpid(es.session_id) AS [Statement],bes.HOST_NAME AS Blocking_hostname,CASE WHEN Bes.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(Bes.program_name,CHARINDEX('(job',es.program_name,0)+4,35))))))ELSE Bes.program_name END AS Blocking_program_name,bes.login_name AS Blocking_login_name, MASTER.DBO.dba_GetStatementForSpid(bes.session_id ) AS [Blocking Statement]FROM sys.dm_exec_requests S INNER JOIN sys.dm_exec_sessions es ON es.session_id=s.session_idINNER JOIN sys.dm_exec_sessions bes ON bes.session_id=s.blocking_session_id
This script will list the blocked and blocking statement information and will be helpful to troubleshoot. Below script will help us to identify the sessions which have an open transaction but not active . That is, a sessions with an open transaction but not running any statement in the last 30 seconds./*****************************************************************************************
STEP 4: List the Open session with transaction which is not active
****************************************************************************************/SELECT es.session_id, es.login_name, es.HOST_NAME, DB_NAME(SP.dbid) AS DatabaseName,sp.lastwaittype,est.TEXT,cn.last_read, cn.last_write,
CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))
)ELSE es.program_name END AS program_name FROM sys.dm_exec_sessions esINNER JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id INNER JOIN sys.dm_exec_connections cn ON es.session_id = cn.session_id INNER JOIN sys.sysprocesses SP ON SP.spid=es.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON st.session_id = er.session_id AND er.session_id IS NULL
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est WHERE (DATEDIFF(SS,cn.last_read,GETDATE())+DATEDIFF(SS,cn.last_write,GETDATE()))>30AND lastwaittype NOT IN ('BROKER_RECEIVE_WAITFOR' ,'WAITFOR') GO
If you liked this post, do like my page on FaceBook
