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:
- 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:
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))