The issue described in this SAS KB article applies to all maintenance releases of SAS Content Server on SAS^® 9.4.

---

Overview

SAS Content Server maintains a journal table in the database to track content repository changes across cluster nodes. Over time, especially in environments with frequent content updates, this journal can grow significantly, as each repository Write operation appends a new entry.

To manage database size and maintain performance, it is recommended that you periodically remove obsolete journal entries. This SAS KB article outlines the procedure to identify and safely delete outdated entries from the SAS_SCS2_JOURNAL table. 

Background

SAS Content Server uses three key database tables to manage journal revisions. Understanding their roles is essential before completing any cleanup:

Table Name	Purpose
SAS_SCS2_JOURNAL	Stores each content repository change (revision) made by cluster nodes. This is the table targeted for cleanup.
SAS_SCS2_LOCAL_REVISIONS	Records the latest journal revision number that each cluster node has processed.
SAS_SCS2_GLOBAL_REVISION	Contains the latest global revision number known across the cluster. Useful for verifying overall cluster state.

Cleanup Principle

You can safely delete entries in SAS_SCS2_JOURNAL that are older than the lowest revision_id recorded in SAS_SCS2_LOCAL_REVISIONS. In a single-node environment, revision_id is the sole value present. In a multi-node cluster, use the minimum revision across all nodes as the deletion threshold to ensure that all nodes have processed those entries.

Important: Before proceeding, take a full backup of the environment, including all components required to restore SAS Content Server to a consistent state in the event that any issues occur during or after the cleanup process.

Recommended Maintenance Procedure

Complete the following steps during a scheduled maintenance window:

1. Connect to SAS^® Web Infrastructure Platform Data Server

Log on to the machine where SAS Web Infrastructure Platform Data Server is installed (typically the compute tier).

Open a command prompt or PowerShell window and navigate to the following directory:

cd <SAS_HOME>/SASWebInfrastructurePlatformDataServer/9.4/bin

Connect to the database using the following command:

psql -h localhost -p 9432 -U SharedServices

Enter the SharedServices user name password

 

2. Identify the Current Cluster Revision State

SELECT * FROM public.SAS_SCS2_GLOBAL_REVISION;

Then, determine the minimum revision ID across all nodes:

SELECT MIN(revision_id) FROM public.SAS_SCS2_LOCAL_REVISIONS;

This value is your safe deletion threshold. Entries with a lower revision_id have been processed by all nodes and can be removed.

 

3. Determine the Most Recent Journal Entry

Run the following query to find the latest entry in the journal:

SELECT * FROM public.SAS_SCS2_JOURNAL ORDER BY revision_id DESC LIMIT 1;

This returns the highest revision_id currently in the journal. Comparing this to the threshold from Step 1 helps estimate the number of deletable entries.

 

4. (Optional) Archive Before Deleting

If your organization requires data retention, consider archiving the journal entries before deletion. You can export the rows to a backup table or flat file before executing the DELETE statement.

 

5. Count and Delete the Old Journal Entries

Using the threshold from step 1, count and then delete the outdated entries:

-- Count the entries to be removed:

SELECT COUNT(*) FROM public.SAS_SCS2_JOURNAL WHERE revision_id < XXXXXXXX;

-- Delete old entries:

DELETE FROM public.SAS_SCS2_JOURNAL WHERE revision_id < XXXXXXXX;

Replace XXXXXXXX with the actual threshold from your environment. Do not delete entries with a revision_id equal to or greater than this value, as one or more cluster nodes might still require them.

 

6. (Optional) Reclaim Database Space

After deleting a large number of rows, run the following command to reclaim disk space and update query statistics (PostgreSQL only):

VACUUM ANALYZE public.SAS_SCS2_JOURNAL;

---

Important Considerations

• Delete only entries that are no longer needed by any cluster node. Removing entries at or above the threshold can cause nodes to miss updates, leading to cache or index inconsistencies.
• Ensure that all cluster nodes are online and have written their current local revision to the database before performing cleanup. If a node has not updated its revision, it might miss changes that have already been purged.
• If you plan to add new cluster nodes after purging, be aware that they will not be able to replay purged journal history. In such cases, initialize the new node’s repository state by copying an existing repository and setting the appropriate starting revision.
• If a cluster node has been permanently decommissioned, manually remove its entry from the SAS_SCS2_LOCAL_REVISIONS table. Otherwise, its outdated revision could prevent effective cleanup by lowering the deletion threshold unnecessarily.