Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

KB-3411: How to purge a large database session on SQL server

Centrify DirectAudit ,  

12 April,16 at 11:08 AM

Applies to: DirectAudit 2.0.x on SQL server

Question:

How can a large session database be purged on SQL Server?

Answer:  

In order to purge sessions, the database recovery mode must be set to simple.
In full recovery mode, purging sessions will end up consuming more disk space because of all the transaction logs.  
  1. Verify if database recovery mode is set to SIMPLE for the Audit store database.

  2. If the session is large, delete the session’s traces from RawData table first (this has to be done in batches).
    It is also recommended to disable the full-text index before the bulk delete operation.

    It is recommend to run the following queries:

    ALTER FULLTEXT INDEX ON RawData DISABLE

    DELETE
    TOP(10) PERCENT FROM RawData WHERE SessionId='<SESSION_GUID>'

    DELETE TOP(10) PERCENT FROM RawData WHERE SessionId='<SESSION_GUID>'

    ALTER FULLTEXT INDEX ON RawData
    ENABLE

    Running the
    DELETE query ten times should clear the session traces from RawData after which the normal cascade delete can be performed.


NOTE: Up to Centrify DA version 2.0, the default was set to 'full', this was fixed in later releases. 
For purging a single sessions using SQL script please refer to KB-2566.  

Still have questions? Click here to log a technical support case, or collaborate with your peers in Centrify's Online Community.