How to kill idle users from NAV server

There are instances where you may be asked to find an easy method for removing the idle users from the NAV database. There has been a well known method where you write a NAV report object to check the idle time of each user and delete the session id by checking their idle time. Then you schedule the NAV report through Navision Application server (NAS).

However, this is an alternative method; in this way, you write a SQL script and schedule it through SQL Job, in the same way you schedule your maintenance plans in your SQL Server such as making daily backups, truncating log files etc.

SQL Script :

USE MyDatabase
 DECLARE @EXECSQL varchar(max)
 SET @EXECSQL = ''select @EXECSQL = @EXECSQL+ 'Kill ' + Convert(varchar, [Connection ID]) + ';'
 From Session WHERE [Application Name]='Microsoft Dynamics NAV Classic client' and [Idle Time]>3000
 EXEC(@EXECSQL)

How to schedule the above SQL Script:
Just enable a job. It’s a matter of pasting the code and give the date & time etc. This is similar to Windows scheduler as well.

Steps for scheduling via a SQL Job:

1.        Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'

2.        In the 'New Job' window enter the name of the job and a description on the 'General' tab.

3.       Select 'Steps' on the left hand side of the window and click 'New' at the bottom.

4.       In the 'Steps' window enter a step name and select the database you want the query to run against.

5.       Paste in the T-SQL command you want to run into the Command window and click 'OK'.

6.       Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).

7.       Click 'OK' - and that’s done!!

Special Note: I just thought of posting this into the blog even for own future references. Thanks very much Isuru (Isuru Dias) for letting this to me.

REPEATABLEREAD Isolation Level in SQL Server

When you have issues around the table blockings etc, one of the solutions may be to change the database transaction isolation level from ‘SERIALIZABLE transaction isolation level” to “REPEATABLE READ transaction isolation level” This will improve general performance in situations where multiple users are experiencing blocking when they are entering journal entries, sales order entries, purchase order entries and similar tasks.
The following example would give you an idea about the different impacts
Statement 1:
CUSTOMER.LOCKTABLE;
CUSTOMER.SETCURRENTKEY("Document No.", "Line No.");
CUSTOMER.SETRANGE(CUSTOMER."Document No.", '1');
CUSTOMER.SETRANGE(CUSTOMER."Line No.", 1, 10);
IF CUSTOMER.FIND('-') THEN
REPEAT
UNTIL (CUSTOMER.NEXT()=0);

Statement 2:
IF CUSTOMER.FIND('-') THEN
REPEAT
UNTIL (CUSTOMER.NEXT()=0);

With SERIALIZABLE transaction isolation level, all existing records will be locked during the first "REPEAT UNTIL" loop and other users will also be blocked from inserting new records within the specified range. The record that has Document No=2, Line No=1 will also be blocked. This will be main draw back in ‘SERIALIZABLE transaction isolation level”. In other words, the second loop will always read exactly the same result as the first loop.
With REPEATABLE READ transaction isolation level, however there is a potential where someone can theoretically insert a new record within the mentioned range which will then appear as an additional record in the second loop.

You can configure Ms Dynamics NAV to use the REPEATABLEREAD isolation level in SQL Server, by running the following TSQL statement against the SQL database; this will disable the 4194304 flag

update [$ndo$dbproperty] set diagnostics = diagnostics | 4194304

Source : http://blogs.msdn.com/b/nav/archive/2011/05/12/microsoft-dynamics-nav-changes-by-version.aspx