SQL Server: Configure Transaction Log Retention

using MS-REPLICATION

Step 1. Review the Recommended Configuration Settings

To configure log retention when MS-REPLICATION is in use, you need to configure the PollingInterval and ReadBatchSize properties on the Log Reader Agent.

There are some trade-offs to consider when choosing values for the above properties:

• If the PollingInterval is too low, changes will be removed from the active transaction log before the CDC process can read them.
• Decreasing the ReadBatchSize means that the Log Reader Agent will take longer to move transactions to the distribution database and therefore replication latency will increase.
• Performance can be improved by increasing ReadBatchSize for workloads of smaller transactions (fewer than 500 commands).
• For larger work loads (transactions with 500-1000 commands), increasing ReadBatchSize has little peformance improvement and may introduce latency in the replication process.

Our recommended values for the two properties are as follows:

1. Set the PollingInterval to 86399 seconds (1 day).

• If the activity on the source is very high and disk usage becomes an issue, this may need to be set to a more frequent interval.

2. ReadBatchSize should be estimated based on the average number of transactions made on the database per day.

Step 2. Update the Properties in SQL Server Management Studio

1. Expand the Local Publications folder, and then right-click and select Launch Replication Monitor.

2. Under Agent, choose Log Reader Agent.

3. Right-click on the pertinent record, select Agent Profile

4. Select the pertinent profile and set the PollingInterval and ReadBatchSize accordingly.

using MS-CDC

Step 1. Retrieve the Configuration of the Capture Job

The CDC capture job is used to mark the transaction log entries as captured and remove them from the active transaction log. To check the configuration of the capture job, run the following SQL query:

exec sys.sp_cdc_help_jobs;

Step 2. Check the Capture Job Configuration Settings

There are three important capture job settings:

Adjusting these settings is a trade-off between how often the CDC process fails due to log truncation, and the disk space used by the transaction logs on the source. If these settings are too aggressive, transactions may be removed from the log before they have been captured which will cause the CDC process to fail and the connection to go down in Etleap. If transactions are occurring on the source more frequently on average than they are being removed from the log, then the log will grow and more disk space will be used.

To ensure that your settings are appropriately configured, please consider the following points:

• If the pollinginterval is too low, changes will be removed from the active transaction log before the CDC process can read them.
• If maxtrans and maxscans are too high, the capture job will process all events in the transaction log.
  • This can lead to the CDC process failing when the capture job next runs as it may truncate log entries that have not been read yet.
• If maxtrans and maxscans are too low and the pollinginterval is high, this can lead to an increased transaction log disk size because it isn’t being purged fast enough.

Step 3. Calculate the Correct Values for the Capture Job Settings

Our recommended settings for the capture job settings are as follows:

1. Set the pollinginterval to 86399 seconds (1 day).

• If the activity on the source is very high and disk usage becomes an issue, this may need to be set to a more frequent interval.

2. maxtrans * maxscans should equal the average number of log events per day

Use the following query to determine how many transactions were truncated the last time the capture job ran:

SELECT session_id, start_time, end_time, duration, scan_phase,
    error_count, tran_count, last_commit_time, log_record_count, schema_change_count,
    empty_scan_count, failed_sessions_count
FROM sys.dm_cdc_log_scan_sessions;

• Each row is one scan cycle done by the capture job (unless empty_scan_count is greater than 0, in which case that row is as many scan cycles as empty_scan_count).
• Look at the tran_count for the last run to see how many logs were truncated by each scan cycle, and the capture job operation as a whole.

Step 4. Update the Capture Job Settings Accordingly

Run the following command to update the capture job:

USE <db name>;
 
EXECUTE sys.sp_cdc_change_job
    @job_type = N'capture',
    @maxscans = <your maxscans value>,
    @maxtrans = <your maxtrans value>,
    @pollinginterval = <seconds to pause>,
    @continuous = 1;

Then restart the capture job to apply the changes:

EXEC sys.sp_cdc_stop_job @job_type = N'capture';
EXEC sys.sp_cdc_start_job @job_type = N'capture';