Session Event Table - Delete dormant records

Toddy_Boy

Dear all

We have a growing Session Event table in Nav 2013. I can't see anything obvious or damaging that'll prevent me from deleting these records nightly via SQL or CAL.

Does anyone else maintain this table in any other way?

vremeni4

Hi Toddy_Boy

You can set how often you want this table to be deleted by setting the parameter

Session Event Table Retain Period

See this link for more details
https://msdn.microsoft.com/en-us/library/dd355055(v=nav.70).aspx

also have a look at the parameter
Session Event Table Purge Frequency

So there is no need for you to create a script, just set the parameters correctly for the NST.

I hope this helps.
Thanks.

vremeni4

Hi Toddy_Boy

You can set how often you want this table to be deleted by setting the parameter

Session Event Table Retain Period

See this link for more details
https://msdn.microsoft.com/en-us/library/dd355055(v=nav.70).aspx

also have a look at the parameter
Session Event Table Purge Frequency

So there is no need for you to create a script, just set the parameters correctly for the NST.

I hope this helps.
Thanks.

Toddy_Boy

Great stuff, thanks for that

RemkoD

In NAV 2017 we still have issues with a fast growing Session Event table and instability in the NAV platform related to the Session Event table. Using the Session Event Table Retain Period wasn’t enough for us. We still had too many records in the table causing trouble. I thought I will share our findings and work-around regarding the Session Event table.

We experience unexpected NAV (background) session crashes caused by a lock on the Session Event table. Also we encountered multiple situations where the Session Event table contains millions and millions of records. These are caused mostly by web service sessions (doesn’t retain a session so each call causes two records) and NAV background sessions (caused by repetitive tasks in the task scheduler).

Luckily Microsoft has resolved most of our issues in cumulative updates.

NAV 2017 CU12 (Build 18976)
229689 Add a missing index to the Active Session table.*
* This fix also includes the removal of explicit locks on the Session Event table. Resolving the session crashes due to a lock.

NAV 2017 CU11 (build-18609)
228726 Many records are accumulated in the SESSION EVENT table with Client Type 2 (Web Service).

NAV 2017 CU8 (build-17501)
219368 The Session Event table grows too fast.

In some situations we have not deployed the latest CU yet. Setting the NST Session Event Table Retain Period on 1 month wasn’t enough either. So we use a SQL job by keeping the Session Event table nice and tidy.

/*
Delete Session Event records for NAV background sessions and NAV web service sessions (SOAP and OData) older than 7 days from the Session Event table 
*/
DELETE FROM [dbo].[Session Event] WHERE ([Client Type] = 2 OR [Client Type] = 5) AND DATEDIFF(DD, [Event Datetime], GETDATE()) > 7

Before I create the job I clean up the Session Event table once manually with TRUNCATE because I do not want the millions of rows passing through the SQL transaction log. I have an example here: Pastebin thingy

I think the amount of records created by background sessions is not yet resolved. So I will stick to the work around until we've found a pretty solution