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