Blocked Session Finder

tobarich

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

garak

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.

tobarich

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

tobarich

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

garak

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

stryk

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: )