Queued updating subscription is a new feature introduced in SQL Server 2000 transactional replication. It allows subscribers to modify the replicated data, and those changes will first be saved in a SQL queue by default. Later a SQL Queue Reader Agent, which runs at the distributor, will read those queued transactions and apply them to the publisher server.

The SQL queue is actually a table called MSreplication_queue in the subscriber database. It has the information of the publisher, publisher database, publication, transaction ID, command type, command data, and insert date. It has very similar functionality to the MSrepl_commands table in the distribution database. The big difference is that the MSrepl_commands table is used by the log reader agent and distribution agent, and the MSreplication_queue table is only used by the queue reader agent.

The following is some of the important information we can get from MSreplication_queue for troubleshooting purposes.

A queued transaction can include any number of commands. When you troubleshoot slow queue reader agent performance problems, you normally will check how large the queued transactions are, and see if you are just slowed down by a huge transaction.

The MSreplication_queue table has a column called tranid, which sounds similar to the xact_id in the MSrepl_transactions table; however, column xact_id has the data type of varbinary (16), while tranid has the data type of sysname.

The xact_id in MSrepl_transactions looks like this: 0x00000006000000450001

To find out how many commands in the SQL queue belong to the same tranid, we can check the commandcount column of the table MSrepl_queuedtraninfo in the subscriber database.

The data column in MSreplication_queue has the information of the queued commands, but they are not in text format. This column has the data type of varbinary and it looks like this:

To read the command, you need to call sp_replqueuemonitor against the subscriber database. For example, insert one row into the subscriber database using the following command (c1 is the primary key):

The output from sp_replqueuemonitor will show the command like this:

If you update one row on the subscriber database using the following command,

the output from sp_replqueuemonitor will look like this:

exec [upd].[sp_MSsync_upd_t1_1] N'PublisherServer', N'SubscriberDB', 1, 100, NULL,-- updated row with all the columns '7AE5D513-F30D-4111-9711-528D02288382', -- the new version of the row 1, 1, 1, -- here is the data from all the columns in the old row '25900D63-04CD-453F-8E88-D64D0F53E391', 0x0A00 -- version of the old row

There is an identity column called orderkey in the MSreplication_queue table. This column shows the order in which the commands are added to the SQL queue. Its identity value is automatically generated each time a new row is added. However, the orderkey values are not always continuous. Here is one example. Say you try to insert three rows into the subscribing table T1 in one transaction:

Notice this is an open transaction, which means this transaction may be rolled back later. At this point, you will see that three INSERT commands are added to the MSreplication_queue table, which have the orderkey value of 1, 2, and 3. The queue reader agent is smart enough not to read those open transaction commands. So the above three commands will be in the SQL queue until they are committed or rolled back.

If we rollback this open transaction, those three commands will be deleted from the MSreplication_queue table. Next time when another command is added to the queue, its orderkey number will start with 4, and we are missing 1 through 3.

Notice that the order the queued commands are added to the MSreplication_queue table is the same order that those commands are issued on the subscriber. If you issue an INSERT statement which fires an UPDATE statement through a trigger, then you will see in the MSreplication_queue table, the INSERT is added first, followed by the UPDATE command, and both of them will have the same tranid.

If for some reason, the Queue Reader Agent fails to process one command in the MSreplication_queue and sp_replqueuemonitor cannot read it either, to find out if another queue reader agent also has this problem, you can use the SELECT INTO command to copy this row from MSreplication_queue table and send it into an empty test table. For example,

The advantage of doing this is that you can set up a test queued updating replication with a different publisher, distributor, and subscriber so that you have a test Queue Reader Agent. Then manually insert this copied row into the test subscriber's MSreplication_queue table. Of course, the test replication's Queue Reader Agent will not be able to apply this command to its publisher, but at least you can see if this agent or sp_replqueuemonitor can read this command. To help troubleshoot this problem further, you can also send this single row to the SQL Support Engineer instead of the entire queue table.

© 2003 Microsoft