Checking for Orphaned Transactions

Commit or roll back orphaned transactions to avoid filling up the inbound queue.

1. Log in to the Replication Server.
2. Execute admin who, sqt to display information about the stable queues at the Replication Server.
3. Use the output from admin who, sqt to identify the entry for the inbound queue of the database whose log was truncated.

   The queue has a two-part name formed from the database ID and the queue-type identifier. For an inbound queue, the queue-type identifier is 1. For example, if the database ID is 101, the inbound queue name is 101:1.

4. Verify that an orphaned transaction exists.
   If the output for the inbound queue shows an open transaction that does not change over long periods of time, the queue probably contains an orphaned transaction. However, it may be difficult to distinguish between an orphaned transaction and a very long transaction.
   1. Dump the stable queue and examine the information about the transaction.
      You can dump only the begin record to find the user name and the time the command was executed, and then see if the user still has open transactions in the database.
   2. Dump the last block in the queue and look at the date for the commands. To determine whether this is the case, dump the last block in the queue and look at the date for the commands.
      You need to dump the last block because even if you find that the user does not have open transactions, the queue may still have an orphaned transaction. If the queue is large, the RepAgent may not be keeping up with it. Use admin who, sqm to find the last block.
5. Use sysadmin purge_first_open to skip an orphaned transaction.
6. Manually apply the orphaned transaction to the replicate database using the output from dumping the stable queue.