Draining the transaction logs for primary databases

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

To ensure the transaction log has been drained off:

1. Wait for all remaining transactions to be replicated.

2. Check that a manual update on a replicate table is forwarded to Replication Server:

   1. If the database is enabled for Replication Agent:

      1> use <dbname>
      2> go
      1> sp_config_rep_agent <dbname>, "traceon", "9201"
      2> go
      

      If you cannot access to the console where Adaptive Server was started, you must also define a trace log file to check the information Replication Agent will send to it:

      1> sp_config_rep_agent <dbname>, "trace_log_file", "path for the log file"
      2> go
      

   2. If the database is using a different method to replicate data, you must ensure that data is replicated by consulting Replication Server queues. Run the following Replication Server command:

      1> admin who, sqm
      2> go
      

      1. 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.

      2. Open a queue dump file by executing the following Replication Server command, where file_name is the name of the file in which you will dump the queue

         1>sysadmin dump_file, "file_name"
         2>go
         

3. Use isql to update one row in a single replicated tale in the primary database:

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

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

   Sybase recommends you use a dummy table with a primary key which you define. It is recommended that you define a replication definition with all replicateable columns

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

5. 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
   

6. Use Notepad or another text editor to examine the dump file to make sure it contains the transaction that corresponds to the update you performed instep 4.

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

8. Stop the application or process reading the transaction log from the primary database:

   • If you are using Rep Agent, log into the Adaptive Server, and stop the Rep Agent:

     1> sp_stop_rep_agent database
     2> go
     

     If you are using LTM or Replicator stop them.

     After draining the transaction logs, disallow all other activity in the databases. If activity does occur, you must redrain the logs.

9. Instruct Replication Server to reject incoming connections from Replication Agent, LTM or Replicator:

   • Log in to the Replication Server and suspend the Log Transfer connection from that database:

     1> suspend log transfer from server.database
     2> go