Figure out the Blocking issue

  • Comments posted to this topic are about the item Figure out the Blocking issue

  • hi Rajib, Thanks for sharing a useful script.. Is this applicable for SQL Server 2008 and above?/

  • Yes . The script is applicable for SQL server 2005-2012.:-)

  • But when I am trying to execute the script on SQL Server 2005 with sysadmin rights, it's giving me below error on "CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL" line.

    Error : Incorrect syntax near '.'.

    and it's being successfully executed on SQL Server 2008

  • Hi Rajib,

    Thank you for sharing the script 🙂

  • I get the same error:


    SELECT client_net_address as HOSTIP,Blocking.session_id as BlockingSessionId , Sess.login_name AS BlockingUser , BlockingSQL.text AS BlockingSQL , Waits.wait_type WhyBlocked

    , Blocked.session_id AS BlockedSessionId , USER_NAME(Blocked.user_id) AS BlockedUser , BlockedSQL.text AS BlockedSQL , DB_NAME(Blocked.database_id) AS DatabaseName

    FROM sys.dm_exec_connections AS Blocking INNER JOIN sys.dm_exec_requests AS Blocked ON Blocking.session_id = Blocked.blocking_session_id

    INNER JOIN sys.dm_os_waiting_tasks AS Waits ON Blocked.session_id = Waits.session_id RIGHT OUTER JOIN sys.dm_exec_sessions Sess ON Blocking.session_id = sess.session_id

    CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)

    AS BlockingSQL CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL ORDER BY BlockingSessionId, BlockedSessionId


    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near '.'.

  • Thanks for the helpful script.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply