Draining Transaction Logs for Primary Databases

Ensure that the Replication Server completely processes the preupgrade log for each primary database you are upgrading.

1. Wait for all remaining transactions to be replicated.
2. Execute:

   admin who, sqm

   Find the entry that corresponds to the inbound queue for this database by looking in the Info field for the queue_number and queue_type entry. For an inbound queue, the queue type is 1. Note the last segment:block entry for the queue.
3. Open the queue dump file:

   sysadmin dump_file, "file_name"

   where file_name is the file to which you are dumping.
4. Create a dummy table to check that the Replication Server has received the latest log record written in the log. You can drop this table later.

   create table dummy (c1 int, c2 char(255))
   go
   sp_setreptable dummy, true
   go
   begin tran
   go
   insert dummy values (1,'hello')
   go 10
   commit tran
   go

5. In the primary Replication Server, execute the admin who, sqm command until the last segment:block entry for the inbound queue changes.
6. In Replication Server, dump the last block of the inbound queue to the dump file you created in step 3:

   sysadmin dump_queue, queue_number, queue_type,
   last_seg, block, 1

   Use the queue_number, queue_type, last_seg, and block values found in the output of the admin who, sqm command in step 5.
7. Use a text editor to examine the dump file to make sure it contains the transaction corresponding to the inserts you performed in step 4.
8. Repeat steps 5 through 7 until the transaction corresponding to the update is in the dump file. After draining the transaction logs, do not allow any other activity in the databases. If activity does occur, you must redrain the transaction logs.