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