Session Event Table

With NAV 2013  Microsoft introduced a new table on SQL Server database.  This table records every Logon, Logoff,Start,Stop,Close of a session.

Unfortunately there is no documentation on why this table was created and how to maintain it.  I was doing a database migration for a client recently and client called me the next day that they experience locking issue. 

I logged in into sql server and saw the following error in the event log.

Here is the sql statement

 The SqlTransaction has been rolled back or committed; it is no longer usable.
  Sql statement:
  IF EXISTS (SELECT 1 FROM "NAV"."dbo"."Server Instance" WHERE DATEDIFF(minute, [Last Active], GETUTCDATE()) > 5) INSERT INTO "NAV"."dbo"."Session Event" ("User SID","Server Instance ID","Session ID","Event Type","Event Datetime","Client Type","Database Name","Client Computer Name","User ID","Comment") SELECT se."User SID",se."Server Instance ID",se."Session ID",4 AS "Event Type",CASE WHEN (se."Login Datetime" >= si."Last Active") THEN DATEADD( second, 1, se."Login Datetime" ) ELSE si."Last Active" END AS "Event Datetime",se."Client Type",se."Database Name",se."Client Computer Name",se."User ID",'Force closed by server' AS "Comment" FROM "NAV"."dbo"."Active Session" AS se INNER JOIN "NAV"."dbo"."Server Instance" AS si ON si."Server Instance ID" = se."Server Instance ID" AND DATEDIFF(minute, si.[Last Active], GETUTCDATE()) > 5
HResult: -2146233088

I looked at the Server Instance I saw two records for servers that existed 3 years ago.  I delete those records.  I ran [Event Session] table and saw that it had 6 million records in it.  I created a sql command and deleted everything that was older than today.

Delete  from "NAV"."dbo"."Session Event" (nolock) where [Event Datetime] < dateadd(day,-1, cast(getdate() as date))

So if you are on version 2013 or higher add one more job to clean this table especially if you are running web service integration.