dmremoveclosedtxn - Remove closed transaction

If CDC misses the commit or abort of a transaction, the transaction can remain in the log parser transaction queues and hold back the restart position for all bookmarks that are created by that parser. Any subscription that uses that parser finds the restart position for its bookmarks is older than it needs to be. If enough time passes after the transaction is closed, you can remove the log files that covers the operations in that transaction.

If the log reader must start reading the logs from that bookmark restart position because the transaction queues cannot be used, the log reader fails trying to read the log records if the log files no longer exist. The shared scrape parser checks for long-running transactions once an hour. By default, a long-running transaction is one that has been in the transaction queues for more than two hours. If a long running transaction is detected, CDC issues warning event 2952. If CDC emits event 2952, you should determine whether one or more transactions referenced by the event are still open. The event contains the transaction ID and first operation current position of the oldest long-running transaction.

The CDC trace log has a trace line beginning with "LONG RUNNING TRANSACTIONS DETECTED" that specifies the transaction ID and the log position of the first operation for each long-running transaction. Then, you need to work with their DBA to determine whether one or more transactions are still open. If a transaction is still open, you must decide what to do about it. You can wait, or you can identify the connection that is associated with the transaction and the owner of that connection. Then you need to decide whether you are going to kill this connection (which will implicitly roll back the open transaction), or somehow have the owning application commit the transaction or roll it back. If the transaction is NOT open, then you need to wait until CDC is at Head Of Log (HOL), that is, all caught up. If CDC is latent, you need to take note of what the HOL position is at that time, then wait until CDC has replicated past that point in the log. Then you need to see whether CDC is still emitting event 2952 for that transaction.

Since CDC checks for long running transactions every one hour by default, you might need to wait up to one more hour after this. If CDC is still emitting event 2952 and saying that transaction is open, then at that point it can be said that CDC is mistaken and has missed the commit or abort of that transaction. Run the dmremoveclosedtxn utility. If the transaction was aborted, this is all that needs to be done. But the transaction might have committed. CDC can't tell which, and the database might not know either. So, in case if it is committed, the utility returns a list of table names for the tables, which were modified by the transaction. You can then decide whether you want to refresh one or more tables in the list. If you don't know with certainty whether the transaction has been aborted, you can refresh the tables.
Important: Before calling the utility, you must ensure that all subscriptions are stopped normal, immediate, or scheduled end.

Syntax

dmremoveclosedtxn [-I ] -p /current position/]

Parameters

-I /instance name/
Name of CDC instance. This parameter is optional.
[-p /current position/]
This parameter is required. current position indicates which transaction is closed and needs to be removed. CDC will remove the transaction whose first operation log position is /current position/ from all log parser persisted transaction queues. You should get /current position/ from the event 2952 message or from the "LONG RUNNING TRANSACTIONS DETECTED" trace message.

The utility returns an indication of success or failure and a list of table names for the tables, which were modified by the transaction.

If you are not sure whether the transaction is aborted, then the you should flag for refreshing any table mappings for the tables noted to have been part of the transaction. There might be more than one subscription with table mappings for the tables that are in the transaction. You can then restart all subscriptions. After waiting a while, you can verify that all subscription bookmark restart positions have advanced.

Procedure

Follow these steps to successfully restart all subscription bookmark positions after running dmremoveclosedtxn:
  1. Stop all regular subscriptions.
  2. DO NOT CLEAR THE STAGING STORE OR STOP THE INSTANCE ABORT.
  3. Run dmremoveclosedtxn.
  4. DO NOT CLEAR THE STAGING STORE OR STOP THE INSTANCE ABORT.
  5. Restart mirroring for all subscribers and allow it to run long enough for the subscription bookmark to be updated in the target TS_BOOKMARK table. You can verify this by running dmrshowbookmark and confirming that the restart position has advanced.