dmsetrestartposition - Update the restart position of a subscription bookmark

The dmsetrestartposition utility provides a way to directly update the restart position of a subscription bookmark so that it is no longer pointing at the log position of the first operation of a transaction that CDC thinks is still open but is not open.

Note:

If the transaction is aborted, then there is no downside to advancing the restart position to skip this transaction. But if the transaction is committed, any operations that were in that transaction will be lost. In this case, there might be data loss. But if the transaction is very old and it really did commit, you would probably have received a row-not-found or duplicate row exception on the tables whose operations were missed and have refreshed those tables or handled the missing or duplicate row with adaptive apply or CDR.

Ensure that all subscriptions are stopped normal, immediate, or scheduled end before calling this utility. Run the dmremoveclosedtxn utility, restart one or more subscriptions, and see whether that has caused the restart position to advance. Call this utility only if the restart position doesn't change. This utility must not be called if the subscription was actively mirroring and then was stopped abort, failed with an error, or the source or target instance crashed. This is because in those cases, the mirror bookmark that the source has might be old. So, the commit and current positions can revert to the old positions on the source bookmark. In these cases, the subscription must be started and then stopped normal before running this utility.

Syntax

dmsetrestartposition [-I /instance name/} -s /subscription name/ [-p /restart position/]

Parameters

-I /instance name/: Name of CDC instance. This parameter is optional.
-s /subscription name/: Name of subscription. This parameter is required.
[-p /restart position/]: This parameter is optional and specifies the log position to set the restart position to. If this option is not specified, the restart position is set to the current position (converted to a restart position) for all journals. You must only not specify the -p option if there are no open transactions as of the bookmark's current position. Because if there are the in-scope operations in those transactions, will be lost.
Note: Unlike dmsetbookmark, dmsetrestartposition will not set the replication status for any table to active.