How to troubleshoot Change History archive database growing to maximum size

Applies to

Cayosoft Guardian environments that use an Archive database for Archive > Change History.

Symptoms

• Archive database size keeps growing and reaches maximum size.
• Retention job may complete, but old data remains and DB size does not decrease.
• Retention may fail with timeout errors, including:
  • Execution Timeout Expired
  • System.TimeoutException
  • “Failed to retain …”

Cause

• Retention rules for Archive databases are disabled.
  
   
• Retention processing on large Change History datasets can time out.
• Index condition on Change History tables in both production and archive databases can slow retention operations.
• Timeout values may be too low for the archive or production DB workload.

Resolution

 

1. Ensure the retention rules for Archive Database are enabled.
2. Confirm the intended retention window.
3. Increase timeout settings used for archive DB operations. 
   
    

4. Configure SQL maintenance plans to reorganize and rebuild indexes online for archive and production databases.

   NOTE: Online indexes rebuild is available on SQL Enterprise edition only.

5. To optimize SQL indexes manually, connect to the Archive database with SSMS and execute the following commands. For Enterprise SQL edition:

Copy

ALTER INDEX IX_HistoryRecords_createdDateTime
                ON dbo.HistoryRecords
        REBUILD WITH (ONLINE = ON);

6. For Enterprise SQL edition, execute the following:

Copy

ALTER INDEX IX_HistoryRecords_createdDateTime
                ON dbo.HistoryRecords
        REORGANIZE;

7. After timeout update, index optimization, run the retention job again.