Session Monitor is not a new tool, but it did stop working with SQL Server 2005. So I think that a lot of people stopped using it, which is a shame. The things that were broken now work again, and I would recommend anyone with any kind of blocking problems to implement this tool. With this post I hope to get some people to use it again, and to provide a new tool to anyone who did not know it exists.
The purpose of the Session Monitor tool is to show a live picture of any current blocks in the Microsoft Dynamics NAV database. And to show it in a familiar environment, i.e. a NAV form. It is an extension of the information you get when going to File -> Database -> Information, and drill down on Sessions.
This is how the Session Monitor shows a blocking situation:
As default it refreshes every second.
Some of the useful features of Session Monitor are:
As mentioned, the tool stopped working in SQL Server 2005. But there are corrections for this now. First, download the tool, which is part of the "Performance Troubleshooting Guide", available for download here (PartnerSource login required):
Microsoft Navision SQL Resource Kit
The original tool contains a SQL query (Session Monitor (SQL Server).sql) which is the part that doesn't work for SQL Server 2005. So don't run this query. Run the query from this KB article instead (PartnerSource login required here too):
Note!: Run the query on the NAV database - not on Master, or any other database you have on SQL Server.
In NAV, import the object "Session Monitor (SQL Server).fob" from the Performance Troubleshooting guide.
Make one change - otherwise in some cases it will fail with an INSERT error:
Design codeunit 150011 "Session Monitor Mgt. (SQL Srv)", and replace this line:
RefreshUserActivity() SELECTLATESTVERSION; IF Session.FIND('-') THEN REPEAT SessionTmp := Session; // SessionTmp.INSERT; Remove this line and replace with:
IF SessionTmp.INSERT THEN ; // New line UNTIL Session.NEXT = 0;
Those are the changes needed to get it to run on SQL Server 2005,
Lars Lohndorf-Larsen (Lohndorf)Escalation Engineer