Symptom
The purge is not deleting some sessions from the Log Database.
In the Database, these sessions have no End Date and no Status (sess_end_date is null and sess_ret_code is null).
Solution
These sessions were probably left in this unfinalized state after a forced Runtime stop during their execution.
Because they have no End Date and no Status, the Purge cannot delete them.
This is a known issue (DI-801).
A solution is to run a SQL Script to update the End Date and the Status, so that the purge can delete them.
Here is a script for Postgresql and one for Sql Server which will set End Date and Status for sessions having no activity since 7 days.
Please contact us if you need help to build a similar query for another database.
-- Postgresql log database update stambia_log.public.stb_log_session_sess set sess_end_date = to_char(to_timestamp(sess_last_tstamp / 1000), 'YYYY/MM/DD HH24:MI:SS:MS'), sess_ret_code = -4 where sess_end_date is null and sess_ret_code is null and to_timestamp(sess_last_tstamp / 1000) < (current_date - interval '7 day')
-- MSSQL log database UPDATE stambia_log.dbo.STB_LOG_SESSION_SESS SET SESS_END_DATE = FORMAT(DATEADD(S, CONVERT(int, LEFT([SESS_LAST_TSTAMP], 10)), '19700101 01:00:00.000'), 'yyyy/MM/dd HH:mm:ss.000'), SESS_RET_CODE = -4 WHERE DATEADD(S, CONVERT(int, LEFT([SESS_LAST_TSTAMP], 10)), '19700101 01:00:00.000') < DATEADD(D, -7, GETDATE()) AND SESS_END_DATE IS NULL AND SESS_RET_CODE IS NULL
Once this query has set the End Date and Status for the session, the purge can be run.
If the Log Database has a big history, it may be better to run several purges.
For example if the oldest session has 300 days and we want to keep 180 days:
purge keep 290 day purge keep 280 day purge keep 270 day purge keep 260 day ... purge keep 190 day purge keep 180 day