Lock timeout - How to find blocking session

frgustofrgusto Member Posts: 32
edited 2007-11-09 in SQL Performance
A customer is complaining about big problems with locked tables.
"Table X is locked by another user".
Lock timeout is 10s.

Running SQL Profiler logging event lock:timeout, I do catch several timeouts and information about who was locked. But there is no information about which session that is locking the table.
By executing T-SQL command sp_who2 there is a column "blocked_by". In Navision session overview there is a column "Blocking connection ID" this information would be very useful.

Can SQL Profiler show info about "blocked_by" or is there any other logging tool that can do this?

SQL 2000, Navision 4.00 SP3, 70Gb DB, 70 active users.

Regards Fredrik


  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    You cannot look back in this detail by default in NAV or SQL.

    Try having a look at the SQL Perform tools, this tool allows you to do this and more.

    I have it running at over 30 customers and love it for analysing these kind of issues.

    Link: www.sqlperform.com
  • strykstryk Member Posts: 645

    Well, first some bold advertisement: "The NAV/SQL Performance Toolbox" (PTB) also provides some pretty smart features to investigate blocks etc. (for details please visit http://www.stryk.info).

    But you could do this:

    1) In EM create an Alert - e.g. "SSI: Block Check" - monitoring the SQL Performance Counter "Lock Wait Time msec"; it should be raised e.g. when the value raises above 5000, means an Alert is triggered when anything is locked for longer than 5 seconds. Set a response-delay of 10 seconds.

    2) As a response this Alert should trigger a new SQL Agent Job, e.g. "SSI: Block Check" - with one step executing this TSQL:
    if exists (select * from master..sysprocesses where [blocked] <> 0) begin
      print getdate()
      select s1.[waitresource],
             [object_name] = 
             ( select top 1 object_name([rsc_objid]) from master..syslockinfo 
               where (rsc_text = substring( s1.[waitresource] , 
                                charindex ('(', s1.[waitresource]) , 
                                len(s1.[waitresource]) - charindex('(', s1.[waitresource]) + 1))
                 and (req_spid = s1.[spid]) and (req_mode >= 5 )
             s1.[loginame],s1.[hostname],s1.[cmd],[blocked by]=s1.[blocked],
             [loginame 2]=s2.[loginame],[hostname 2]=s2.[hostname],[cmd 2]=s2.[cmd],
      from master..sysprocesses s1 (nolock)
      inner join master..sysprocesses s2 (nolock) on s2.[spid] = s1.[blocked] 
      where s1.[blocked] <> 0 and s1.[waittime] >= 5000
    Define an output-file for the job output.

    This has implemented basic automatic block detection:

    When a lock has to wait for more than 5 seconds, the Alert is raised which triggers the Job. The job writes some info about the "Who is Who" of the block into a file which you could analize afterwards.

    As mentioned, the PTB could do all this in a more convenient and detailed way, but this should work for the start!

    P.S.: The counter "Lock Wait Time" is not really relyable, maybe you have to adjust the threshold value. With SQL 2005 one should use the "Processes blocked" counter instead.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • you can use this in SQL 2005 / SQL 2008 - I use this as part of my diagnostic reports - this shows the full blocking chain of users but doesn't show object data so well - I have another report for that.

    'blk object' = t1.resource_associated_entity_id,
    t2.wait_duration_ms/1000 as wait_time,
    t1.request_session_id as User_Session,t3.login_name as User_name,
    t2.blocking_session_id as Blocked_by,t4.login_name as Blocker_Name
    master.sys.dm_tran_locks as t1 with (nolock) join
    master.sys.dm_os_waiting_tasks as t2 with (nolock) on t1.lock_owner_address = t2.resource_address
    join master.sys.dm_exec_sessions as t3 with (nolock) on t1.request_session_id = t3.session_id
    join master.sys.dm_exec_sessions as t4 with (nolock) on t2.blocking_session_id = t4.session_id
    t1.request_session_id = isnull(null,t1.request_session_id)
  • krikikriki Member, Moderator Posts: 9,086
    Also the session table in Navision (File=>Database=>information=>tab sessions=>drill down on "Current sessions") or create a form on table "Session" can be useful to find blocking session.

    And this stays in Navision. No need to to use SQL!
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Sign In or Register to comment.