When tblActivityLog is very large the Change log auto truncate job may not complete due to a timeout.
There is a stored procedure called netActivityLogTruncate which can be run to allow the job to complete. Make a database backup prior to running this.
1, -- @Archive BIT,
1000000, --@MaxRows BIGINT,
36793782, --@BeforeEntry BIGINT = NULL, this is the pkID of row at 2020-06-28T23:59:58
'2020/06/29', --@CreatedBefore DATETIME = NULL,
N'project://default/' --@PreservedRelation NVARCHAR(255) = NULL
Warning: Do not use these values as you should use the specific IDs and dates relevant to your database.
Here are the typically relevant parameters.
- MaxRows - defines the number of rows the SP will delete.
- BeforeEntry - All pkIDs less than this parameter will be deleted.
- CreatedBefore - All items created before this date time will be removed.
The other parameters are typically set as shown in the example, however you can review tblActivityLog if it is not deleting enough.
In the example the SP will delete a maximum of 1,000,000 rows in the subset of items that are before pkID 36793782 and date 2020/06/29.
Check the number of rows before and after running the SP to ensure sufficient number of rows is deleted, i.e. enough that the job won't timeout.
select count(*) from tblActivityLog
To prevent this in the future, run the job more frequently.