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
0
Comments
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.
"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
If anyone knows how to emulate this please let me know. Thanks
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").
regards
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:
Thus, the "Alert" executes a Job which rund the procedure and so on ...
(Just an example, maybe somewhat buggy :bug: )
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool