Thursday, January 7, 2010


I use replication in SQL Server 2008 extensively, the snapshot and transaction replication methods are used the most. The situation where it is used is that i  replicate production database tables to other (destination) database on a different server, this (destination) database is used as the source to perform all the data transformations. In this way the OLTP production (source) database is not overloaded, while replication was running recently there were some errors. These errors caused replication to be backed up meaning there were rows which were not being pushed down to the subscribers. In the replication monitor, Under the All subscriptions tab right click on the status column corresponding to the subscriber where the problem is occuring. In the window that opens up, click on the distirbutor to subscriber history.  Click on the error line, the error details appear in the bottom section of the window. The error message displayed here is not really helpful, all it does is it shows the Command attempted, along with the Transaction sequence number, note the transaction number which can be seen in the window below:

There is a stored procedure that be used to which we need to pass the transaction sequence number. The result of the stored procedure will display the actual sql command that was being attempted on the subscriber database.
Here is the stored procedure: (This has to be run on server which serves as the distributor, the distribution database):
The SQL below expects a start and end sequence number. Grab the value for [xact_seqno] and plop it into the parameters for the stored proc below. The start and end sequence number needs to be the same.

EXEC Distribution..sp_browsereplcmds @xact_seqno_start = '0x0018B73A00003BCF000800000000', @xact_seqno_end = '0x0018B73A00003BCF000800000000'
The result set contains a column called command which would have the actual Sql command.
I found the above command to be very useful for debugging errors that happen during replication.

No comments:

Post a Comment