Blocked Session Finder

tobarichtobarich Member Posts: 33
Hi: I’ve downloaded the Blocker Finder application but I don`t know how it works. I open 3 sessions with 3 different users, in the fist I execute the Blocker application, in the second I tried to post using the debugger while in the session number 3 tried to post without debugger.
The second session should block the third but nothing appears when I click the button start. Can you help me to use it? Thanks

Comments

  • garakgarak Member Posts: 3,263
    After you press the START Button the System look every 1000ms if a session is blocked and which table is locked.

    so if you post in session 2 whit using the debugger stop the debugger in CU 80 (for example on the position where SalesInvoiceHeader will be inserted). Then trail to post a another sales order in session 3. session 3 waits, and waits, and waits ......

    If you now press the STOP Button on Session 1 you will see, wich user is blocking and which tables are blocked.
    Do you make it right, it works too!
  • tobarichtobarich Member Posts: 33
    Thanks very much for your help, it works now but i get an error on the first line of the function FindBlockedObject():

    "ADORecSet := ADOConnection.Execute(STRSUBSTNO(SQLString,Session."Connection ID"));"

    The error says: "This Automation variable has not been instantiated. You can instantiate it by either creating or assigning it."

    And sometimes another error appears: "Invalida obect name 'sys.dm_tran_locks'.

    If I disable the call to the function FindBlockedObject the aplication works fine and brings the users blocked but without the object detail.
    Can anyone tell me what is wrong? Thanks
  • tobarichtobarich Member Posts: 33
    I now what the problem is. I'm working with SQL Server 2000 and it doesn´t have the table sys.dm_tran_locks.
    If anyone knows how to emulate this please let me know. Thanks
  • garakgarak Member Posts: 3,263
    This tool requires SQL Server 2005.

    For SQL Server 2000 take a look at stored procedure sp_lock or this old SQl code which i'ved used in past with sql 2000. For Navision you must change the code a little bit (see my working in "blocked session finder").
    select convert (int, sli.req_spid) As Spid,
    
    db_name(sli.rsc_dbid) As dbid,
    
    object_name(sli.rsc_objid) As 'Object Name',
    
    sli.rsc_indid As 'Index Id',
    
    substring (v1.name, 1, 4) As 'Lock Type',
    
    substring (sli.rsc_text, 1, 16) as Resource,
    
    substring (v3.name, 1, 8) As 'Lock Mode',
    
    substring (v2.name, 1, 5) As 'Lock Status'
    
    from
    
    master.dbo.syslockinfo sli with (nolock) join master.dbo.spt_values v1 on sli.rsc_type = v1.number
    
    join master.dbo.spt_values v2 on sli.req_status = v2.number
    
    join master.dbo.spt_values v3 on sli.req_mode + 1 = v3.number
    
    where
    
    v1.type = 'LR'
    
    and v2.type = 'LS'
    
    and v3.type = 'L'
    
    and rsc_dbid = db_id() and rsc_objid >0
    
    order by sli.req_spid
    
    go
    

    regards
    Do you make it right, it works too!
  • strykstryk Member Posts: 645
    Well, actually you DON't want to do that "Block Detection" from NAV - why wasting one user license for this task? Quite an expensive issue since ...

    Block Detection could be implemented event-triggered & automatic in SQL Server. I discribed the process in my BLOG http://dynamicsuser.net/blogs/stryk/archive/2008/05/12/blocks-amp-deadlocks-in-nav-with-sql-server.aspx
    (you can also find some Powerpoint-Slides etc. here on MIBUSO, but the BLOG is more current)

    The measures described here are also based on SQL 2005, but it's not that difficult to downgrade this to be applicaple with SQL 2000:

    The "Alert" has to to monitor "SQL Server:Locks - Lock Wait Time (ms)", e.g. when rasing above 5000 (= 5 seconds), and the system-/lock/-process info could not be taken from the DMV, but from sysprocesses etc.. For example it could look like this:
    create table [dbo].[ssi_BlockLog]
     (
      [entry_no] bigint identity constraint [ssi_BlockLog$pk_ci] primary key clustered,
      [timestamp] datetime,
      [db] varchar(128) collate database_default,
      [waitresource] varchar(128),
      [object_name] varchar(128) collate database_default,
      [waittime] bigint,
      [lastwaittype] varchar(128),
      [spid] int,
      [loginame] varchar(128) collate database_default,
      [hostname] varchar(128) collate database_default,
      [program_name] varchar(128) collate database_default,
      [cmd] nvarchar(255) collate database_default,
      [status] varchar(128) collate database_default,
      [cpu] bigint,
      [blocked by] int,
      [loginame 2] varchar(128) collate database_default,
      [hostname 2] varchar(128) collate database_default,
      [program_name 2] varchar(128) collate database_default,
      [cmd 2] nvarchar(255) collate database_default,
      [status 2] varchar(128) collate database_default,
      [cpu 2] bigint 
      )
    go
    
    create procedure ssi_blockdetection as
    
    if exists (select * from master..sysprocesses where [blocked] <> 0) begin
    
          insert into [ssi_BlockLog]
          select getdate(), [db]=db_name(s1.[dbid]), ltrim(rtrim(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.[waittime],s1.[lastwaittype],s1.[spid],
                 s1.[loginame],s1.[hostname],s1.[program_name],s1.[cmd],s1.[status],s1.[cpu],[blocked by]=s1.[blocked],
                 [loginame 2]=s2.[loginame],[hostname 2]=s2.[hostname],[program_name 2]=s2.[program_name],[cmd 2]=s2.[cmd],s2.[status],s2.[cpu]          
          from master..sysprocesses s1 (nolock)
          inner join master..sysprocesses s2 (nolock) on s2.[spid] = s1.[blocked] 
          where s1.[blocked] <> 0
    
    end
    

    Thus, the "Alert" executes a Job which rund the procedure and so on ...

    (Just an example, maybe somewhat buggy :bug: )
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
Sign In or Register to comment.