Post reply

Figure out the Blocking issue

  • December 14, 2013 at 10:42 pm

    #286156

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

  • December 16, 2013 at 7:33 am

    #1673687

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

  • December 16, 2013 at 7:51 am

    #1673700

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

  • December 16, 2013 at 8:13 am

    #1673715

    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

  • October 14, 2015 at 1:32 am

    #1833132

    Hi Rajib,

    Thank you for sharing the script 🙂

  • October 14, 2015 at 2:15 am

    #1833144

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

  • October 21, 2015 at 7:28 am

    #1834651

    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