Secondary Truncation Point Management

To improve secondary truncation point management in a multipath replication environment, set the frequency of truncation point request intervals for Adaptive Server RepAgent.

You can only use the scan batch size parameter of sp_config_rep_agent in a non-multipath replication environment to set the secondary transaction point. scan batch size specifies the maximum number of log records to send to Replication Server in each batch. When the maximum number of records is met, RepAgent requests Replication Server for a new secondary truncation point.

However, in a multipath replication environment with a single scanner, the distribution of data in the primary Adaptive Server database log is uneven because some replication paths may not receive data as frequently as other paths. Therefore, truncation point requests are infrequent if RepAgent bases the requests on scan batch size. When two or more replication paths share the same scanner, and truncation occurs based on the number of commands sent, the secondary truncation point is moved only as often as the replication path with the least transaction volume. No matter how many commands are replicated on the first path, truncation does not occur until the minimum number of commands has also been processed by the second path. If the truncation point moves slowly, or not at all, the Adaptive Server transaction log grows until the log can be truncated. With multiple scanners, some threads may process more database records than others. If the truncation point requests are based on scan batch size, the truncation page movement slows or may even stop.

Instead of managing truncation point requests based on the number of log records in a batch, use the trunc point request interval parameter with sp_config_rep_agent to directly set the frequency of secondary truncation point requests from RepAgent to Replication Server:

 sp_config_rep_agent dbname, 'trunc point request interval', 'trunc_point_request_interval_value'

Using trunc point request interval, only the RepAgent sender threads control the truncation point request intervals and . In a multipath environment, each path has an associated sender thread. Therefore, trunc point request interval is not dependent on the number of scanners and is effective in single and multiple scanner configurations. Each sender is independent of other senders and each sender can move the trunctation point so the truncation page can move more quickly.

The range of values for trunc_point_request_interval_value is from 1 second up to the value of MAXINT which is 2,147,483,647 seconds. The default is 10 seconds. For example, to set the frequency of secondary truncation point requests to 5 seconds at the pdb1 database, enter:

sp_config_rep_agent pdb1, 'trunc point request interval', '5'

You do not need to to restart RepAgent after you make a change to the parameter value as the parameter is dynamic. However, RepAgent applies the new value only when the previous interval expires. For example, if current interval is 60 seconds and you change the interval to 100 seconds, the new 100 second interval only starts after the current 60 second interval expires.

RepAgent sends the truncation point request to Replication Server when a RepAgent sender thread is available to process the request. If the truncation point interval expires but the sender has not completed the batch it is processing, RepAgent queues the request and only executes the request once the batch processing is complete. Therefore, the actual truncation point request intervals may vary from each other amd from what you have set with trunc point request interval. In addition, the intervals between truncation point requests increase if you set a high value for ltl batch size, as RepAgent waits for the sender to process a larger batch before requesting a trunctation point.

See the NumberOfTruncPointRequested and NumberOfTruncPointMoved fields in the monRepScanners Adaptive Server monitoring table to track truncation point status.