Bulk Delete Stale Sessions From a Siteminder Session Store on ODBC
Article ID: 438063
Updated On:
Products
Issue/Introduction
In some cases or under certain conditions, you may need to perform a bulk deletion of a large number of stale sessions from the Siteminder Session Store. In active session store, deleting all the sessions would invalidate all sessions forcing all users to log back on. This would include the users with active sessions. This KB details how to bulk delete all stale sessions which have exceeded the Max Timeout of the session.
Environment
PRODUCT: Symantec Siteminder
COMPONENT: Policy Server
SUB_COMPONENT: Session Store
OPERATING SYSTEM: Any
SESSION STORE: ODBC (Any)
Cause
Stale sessions in the Siteminder Session Store are deleted as a part of a regularly running job on the Siteminder Policy Server. In some cases the session store can grow with a large number of stale sessions. This can occur is there are failures during the clean-up job, or the clean-up job runs long and times out. This will lead to a large number of stale records on the session store.
Resolution
ODBC on Windows
The following SQL example will delete all of the expired sessions from the session store which have exceeded the Max Timeout of the session.
======================================================================
/*Deletes stale sessions which have exceeded the MaxTime from 'ss_sessionvar5' table
A Foreign Key Constraint is present requiring that, if a corresponding record exists in 'ss_sessionvar5',
that record must be deleted before it can be deleted from the 'ss_sessionmaintenance5' table
*/
DELETE FROM ss_sessionvar5
WHERE sessionid IN (SELECT sessionid FROM ss_sessionspec5 WHERE expirationtime < (SELECT DATEDIFF (SECOND, '1970-01-01', GETUTCDATE()) AS CurrentEpoch));
/*Deletes stale sessions which have exceeded the MaxTime from 'ss_sessionspec5' table*/
DELETE FROM ss_sessionspec5 WHERE expirationtime < (SELECT DATEDIFF (SECOND, '1970-01-01', GETUTCDATE()) AS CurrentEpoch);
======================================================================
The following (optional) SQL example will delete all of the expired sessions in the session store which have exceeded the Idle Timeout for the session.
======================================================================
/*Deletes stale sessions which have exceeded the IdleTime from 'ss_sessionvar5' table
A Foreign Key Constraint is present requiring that, if a corresponding record exists in 'ss_sessionvar5',
that record must be deleted before it can be deleted in 'ss_sessionmaintenance5' table
*/
DELETE FROM ss_sessionvar5
WHERE sessionid IN (SELECT sessionid from ss_sessionspec5 WHERE (maxidletime + lasttouch) < (SELECT DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) AS CurrentEpoch));
/*Deletes stale sessions which have exceeded the IdleTime from 'ss_sessionspec5' table*/
DELETE FROM ss_sessionspec5
WHERE (SELECT (maxidletime + lasttouch) AS total FROM ss_sessionspec5) <
(SELECT DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) AS CurrentEpoch);
======================================================================
ODBC on Linux
The following SQL example will delete all of the expired sessions from the session store which have exceeded the Max Timeout of the session.
======================================================================
/*Deletes stale sessions which have exceeded the MaxTime from 'ss_sessionvar5' table
A Foreign Key Constraint is present requiring that, if a corresponding record exists in 'ss_sessionvar5',
that record must be deleted before it can be deleted from the 'ss_sessionmaintenance5' table
*/
DELETE FROM ss_sessionvar5
WHERE sessionid IN (SELECT sessionid FROM ss_sessionspec5 WHERE expirationtime < UNIXTIMESTAMP();
/*Deletes stale sessions which have exceeded the MaxTime from 'ss_sessionspec5' table*/
DELETE FROM ss_sessionspec5 WHERE expirationtime < UNIXTIMESTAMP()
======================================================================
The following (optional) SQL example will delete all of the expired sessions in the session store which have exceeded the Idle Timeout for the session.
======================================================================
/*Deletes stale sessions which have exceeded the MaxTime from 'ss_sessionvar5' table
A Foreign Key Constraint is present requiring that, if a corresponding record exists in 'ss_sessionvar5',
that record must be deleted before it can be deleted from the 'ss_sessionmaintenance5' table
*/
DELETE FROM ss_sessionvar5
WHERE sessionid IN (SELECT sessionid from ss_sessionspec5 WHERE (maxidletime + lasttouch) < UNIXTIMESTAMP();
/*Deletes stale sessions which have exceeded the MaxTime from 'ss_sessionspec5' table*/
DELETE FROM ss_sessionspec5 WHERE expirationtime < UNIXTIMESTAMP()
======================================================================
Additional Information
Feedback
