dbcc settrunc

A Transact-SQL command that modifies the secondary truncation point information for an Adaptive Server database.

Syntax

dbcc settrunc('ltm', {'valid' | 'ignore'})

dbcc settrunc('ltm', 'gen_id', db_generation)

dbcc settrunc('ltm', {'begin' | 'end',)

Parameters

valid – Instructs Adaptive Server to respect the secondary truncation point. This option prevents the Adaptive Server from truncating transaction log records that have not been transferred to Replication Server.
ignore – Instructs Adaptive Server to ignore the secondary truncation point. This allows Adaptive Server to truncate log records that the RepAgent has not yet transferred to the Replication Server.
gen_id – Instructs Adaptive Server to reset the database generation number in the log.
db_generation – The new database generation number. Increment the number after restoring dumps to prevent Replication Server from rejecting new transactions as duplicates.
Warning! You cannot execute dbcc settrunc when RepAgent is running.
begin – Sets the Secondary Truncation Point (STP) to the beginning of the log.
end – Sets the STP to the end of the log.

Usage

Use dbcc settrunc for RepAgent-enabled databases.
The secondary truncation point must be valid for Adaptive Server databases containing primary data to be replicated or for databases where replicated stored procedures are stored.
When the secondary truncation point is valid, Adaptive Server does not truncate log records that the Replication Server has not yet received from the RepAgent.
If the secondary truncation point is not modified for an extended period of time, the log may fill up and prevent applications from continuing. You can change the secondary truncation point to ignore—after shutting down the Replication Server and the RepAgent—so that the log can be truncated and applications can continue working. Then use the rs_zeroltm procedure to reset the locator value to zero (0). However, note this warning:

Warning! If you set the secondary truncation point to ignore and then truncate the log, replicated data will be incorrect. You must either re-create subscriptions, reconcile subscriptions by executing rs_subcmp, or load database and transaction dumps and replay the lost transactions. See the Replication Server Administration Guide Volume 2 for instructions for replaying lost transactions. You should increment the database generation number after restoring coordinated dumps. Use admin get_generation to find the current generation number.

See rs_zeroltm for details about running this stored procedure.
Increment the database generation number after restoring to prevent Replication Server from rejecting new log records. See the Replication Server Administration Guide Volume 2 for information about reloading coordinated dumps.
If the primary Replication Server is unable to accept transactions and the primary database transaction log is full and must be truncated, you may need to turn off the secondary truncation point and truncate the log in order to allow Adaptive Server transactions to continue. In this situation, use dbcc settrunc('ltm', 'ignore') to shut down the Replication Agent and turn off the secondary truncation point in the database.

After using dbcc settrunc, you must use the rs_zeroltm stored procedure to reset the locator value for a database to 0. Otherwise, the log page stored in the rs_locater system table may become invalid. Starting the RepAgent may then cause Adaptive Server to register data corruption and to produce errors such as 605 and 813.
Transactions that execute after you have turned off the secondary truncation point are not transferred to the Replication Server. Therefore, primary and replicate databases may not be in synch.

For this reason, after you have truncated the log and after the Replication Server has been brought up successfully, you may have to alter replication definitions, drop and re-create subscriptions, and re-materialize the data in the replicate database. New columns will be null until the data is re-materialized.

If a relatively small number of transactions did not transfer to the Replication Server, you may instead choose to use the rs_subcmp program to reconcile the primary and replicate databases.