Kill Idle Connections

To save concurrent uses in NAV it might be a good idea to kill idle connections automatically.

This can be done by looping through the Session Virtual Table and check the Idle Time field. I like to add the field “Maximum Idle Time” to the User Setup table and compare that field to the Idle Time in the Session table before deciding to kill the session.

The use running this batch must have permission to kill sessions on the SQL Server.
[code htmlscript=”false”]IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
‘Driver={SQL Server Native Client 10.0};’ +
‘Server=<ServerName>;’ +
‘Database=<DatabaseName>;’ +
‘Trusted_Connection=yes;’);
ADOConnection.CommandTimeout(0);

Session – OnAfterGetRecord()
IF "Application Name" <>
‘Microsoft Dynamics NAV Classic client’
THEN
CurrReport.SKIP;
BackSlashPos := STRPOS("User ID",’\’);
IF NOT
UserSetup.GET(
UPPERCASE(COPYSTR("User ID",BackSlashPos + 1)))
THEN
CurrReport.SKIP;
IF FORMAT(UserSetup."Maximum Idle Time") = ” THEN
CurrReport.SKIP;
Idle := "Idle Time";
MaxIdle := (UserSetup."Maximum Idle Time" – 000000T);
IF Idle < MaxIdle THEN
CurrReport.SKIP;

ADOConnection.Execute(STRSUBSTNO(‘KILL %1’,"Connection ID"));

Session – OnPostDataItem()
ADOConnection.Close;
CLEAR(ADOConnection);[/code]

Name DataType Subtype Length
UserSetup Record User Setup
ADOConnection Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Connection
MaxIdle Decimal
Idle Decimal
BackSlashPos Integer

This can be added as a Job Queue Batch.

See here to check for required permission to use the kill statement.

27 Replies to “Kill Idle Connections”

  1. Gunnar,

    I am getting error message:

    —————————
    Microsoft Dynamics NAV Classic
    —————————
    This message is for C/AL programmers:

    The call to member Open failed.
    Microsoft OLE DB Provider for ODBC
    Drivers returned the following message:

    [Microsoft][SQL Server Native Client 10.0]
    Named Pipes Provider: Could not open a
    connection to SQL Server [53].

    —————————
    OK
    —————————

    Any suggestions what could be wrong?

  2. Ahh… mistyped the servername 😉

    By the way, this way of killing a session is really cool. User is not getting any error message (at least, when I tested this, I didn’t). And then, if user clicks anywhere on the MenuSuite, he connects back to the system automatically. Cool! 🙂

  3. Hi,

    I am new to navision. Kindly help me with this.. Im not clear.. How will the “Maximum Idle Time” field work and where should I show this code??

  4. Hi Gunnar,

    I am gettin the following error if I run the report. Can u pls help me…….

    Could not create an instance of ole control or automation server identified by guid={2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection.

  5. session get killed but user can continue using navision not needing to login again…was this happning in your case or am I missing anything

  6. Yes that is intended. The client reconnects if there is available license to do that. However, if you are having problems with idle clients and the concurrent user licenses then this would help you.

  7. Sæll Gunnar, ég er að reyna að gera keyrslu eftir þessu í Nav 2013 R2 en hef ekki reitinn “Idle Time” í Session töflunni? Er þetta ekki standard reitur?

  8. Hi,

    Thanks for the answer. I already used STOPSESSION, but couldn’t find any PowerShell that could show Idle connections. The problem that I’m facing is: How to kill Idle for more than 4 minutes Nav 2016 sessions with exceptions: for some users should not kill sessions even if they have been idle for more than 4 minutes.

    I found 3 possible solutions:
    • Solution 1: Setup Idle Client Timeout/Keep Alive Interval/Reconnect Period on Nav Service. Still this solution is missing exceptions for some users.
    • Solution 2: Use Session Virtual table in Nav to view and kill sessions. Still this virtual table is missing “Idle Time” that was to be found on Nav 2009 R2. Is there a way, to change a virtual table, to extend this Session table in Nav 2016 to be same as in Nav 2009 R2?
    • Solution 3: Use SQL script that worked on Nav 5.0 SQL 2008 R2, that used sys.sysprocesses table to check for idle connections. This script is not showing in SQL 2014 and Nav 2016 the Role Tailored Connections, that we need to kill. Is there a different table to use?

    Thanks in Advance, Mihai M

  9. Hi
    If I use NAS service and Idle Time simultaneously in same service will it get any problem ? if the NAS service get idle , will it get disconnected ?

    Regards
    Shan

Leave a Reply