NAV Session Event table: cleanup

The Session Event table (ID 2000000111) is a table present in the NAV database starting from NAV 2013. In this table NAV records every Logon, Logoff, Start, Stop, Close of a session in the current database.

There are certain situations where this table could grow a lot (for example when using intensive web service transactions) and this could affect your database performances, so it’s important to manage the retention of these entries. Today on a forum there was a good question regarding a missing option in the actual NAV releases.

With NAV 2013, in the NAV Server settings you had these two options:

NAVSessionEventPurge_01.jpg

Here:

  • Session Event Table Purge Frequency specifies the day of the week and the start time when the Session Event table is queried for purging. The day-of-the-week part of the value is an integer from 0 (Sunday) to 6 (Saturday). The time part of the value is a valid time of the day expressed in the format hh:mm, where hh is a value from 1 to 24. Default value is 0 6:00 (6:00 a.m. on Sunday).
  • Session Event Table Retain Period is an integer value specifying how many months events in the Session Events table are retained (Default: 3)

With these options, you could specify when and how the Session Event table entries should be deleted.

In the last NAV releases, the Purge Frequency option is disappeared. Now the NAV server settings are as follows:

NAVSessionEventPurge_02

So, how and when are the Session Event records deleted now?

Now, the record from the Session Event table are immediately deleted as soon as they become older than the specified number of months (default = 3).

This deletion is automatically managed by the NAV server, but in some cases it could be useful to have also a script for managing the deletion on SQL Server directly (I don’t like the retention period in months in many cases).

A script that you can execute on SQL Server is the following (where <NameOfYourNAVDatabase> is the name of your NAV database):

use <NameOfYourNAVDatabase>;

declare @days int;
set @days = 3; --days for Session Event logs to retain
delete from [Session Event] (nolock) where [Event Datetime] < dateadd(day, -@days, cast(getdate() as date))

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.