An orphaned transaction is a transaction in an inbound stable queue that is missing a terminating commit or rollback command. Because Replication Server does not free up a queue segment until every transaction in the queue segment (or in any of the preceding queue segments) has been committed or rolled back, orphaned transactions can cause the inbound queue to fill up.
Checking for orphaned transactions
Log in to the Replication Server and execute admin who, sqt to display information about the stable queues at the Replication Server.
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.
If the output for the inbound queue shows an open transaction that does not change over long periods of time, an orphaned transaction is probably in the queue. However, because it is difficult to distinguish between an orphaned transaction and a very long transaction, you must further verify that an orphaned transaction exists.
To verify that there is an orphaned transaction:
Dump the stable queue and examine the information about the transaction. Refer to Appendix A, “Dumping Stable Queues” for instructions. You can dump just 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.
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. To determine whether this is the case, dump the last block in the queue and look at the date for the commands. Use admin who, sqm to find the last block.
If the transaction is orphaned, use the sysadmin purge_first_open command to skip it.
Use the output from dumping the stable queue to manually apply the orphaned transaction to the replicate database.
