Draining the transaction logs for primary databases

For each primary database you are upgrading, you need to ensure that Replication Server completely processes the pre-upgrade log.

To drain the transaction logs:

  1. Wait for all remaining transactions to be replicated.

  2. Run the following Replication Server command:

    1> admin who, sqm
    2> go
  3. Find the entry that corresponds to the inbound queue for this database by looking for the Info field for the queue_number:queue_type entry. For an inbound queue, the queue type is 1. Note the Last Seg.Block entry for the queue.

  4. Open a queue dump file by executing the following Replication Server command:

    1> sysadmin dump_file, "file_name"
    2> go

    where file_name is the name of the file to which you will dump the queue.

  5. Use isql to update one row in a single replicated table in the primary database:

    1> update table set column = column 
    2> where key = unique_value
    3> go

    The update command helps to track whether all modifications to the replicated database have been sent to the Replication Server.

    NoteIn Replication Server 10.1 or later, choose a table that does not use the replicate minimal columns clause, or use the alter replication definition command...replicate all columns command to change the replication definition before updating the row. If you alter the replication definition, be sure to change it back after you complete this upgrade procedure.

  6. In the primary Replication Server, execute the admin who, sqm command until the last segment:block entry for the inbound queue changes.

  7. Execute the following Replication Server command to dump the last block of the inbound queue to the dump file you created in step 3:

    1> sysadmin dump_queue, queue_number,
    2> queue_type, last_seg, block, 1
    3> go

    Use the queue_number, queue_type, last_seg, and block values found in the output of the last admin who, sqm command.

  8. Examine the dump file to make sure it contains the transaction that corresponds to the update you performed in step 4. (You can use Notepad to examine the file.)

  9. Repeat steps 5–7 until the transaction that corresponds to the update is in the dump file.

  10. Log into the Replication Server and suspend the Log Transfer connection from that database:

    1> suspend log transfer from server.database
    2> go
  11. If you are using Rep Agent, log into the ASE server, and stop the Rep Agent:

    1> use database
    2> go
    1> sp_stop_rep_agent database
    2> go
  12. If you are using LTM, shut down the LTM.

After draining the transaction logs, do not allow any other activity in the databases. If activity does occur, you will need to redrain the logs.