Figure out the Blocking issue

  • Rajib Kundu

    Grasshopper

    Points: 22

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

  • devdocs

    SSC Enthusiast

    Points: 148

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

  • Rajib Kundu

    Grasshopper

    Points: 22

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

  • devdocs

    SSC Enthusiast

    Points: 148

    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

  • gijithau

    Newbie

    Points: 5

    Hi Rajib,

    Thank you for sharing the script 🙂

  • Ronnie Singh

    Grasshopper

    Points: 12

    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 '.'.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the helpful script.

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

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