Draining the transaction logs for primary databases

At this stage all of the applications accessing data on primary databases are stopped. For each primary database you are upgrading, ensure that Replication Server completely processes the pre-upgrade log,.

To be sure that 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, define additionally a trace log file so you can check the information Replication Agent sends 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. In such case use the following procedure:

      Run the following Replication Server command:

      1>admin who, sqm
      2>go
      

      Find the entry that corresponds to the inbound queue for this database:

      1. Look for the info field in 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:

         1> sysadmin dump_file, "file_name"
         2> go
         

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

      3. 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 track whether all modifications to the replicated database have been sent to the Replication Server.

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

      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
         

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

      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 in step 4.

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

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

4. 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 must redrain the logs.

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