PRB: Replication Tasks Don't Print Full Info on Error/Deadlock

• Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

When a replication task runs into an error or deadlock, it reports the error/deadlock message and returns back without providing any information about any committed transactions. The task might have successfully committed some transactions before the error or deadlock occurred, but these transactions are going unreported.

CAUSE

The replication tasks maintain information that is to be logged in the task history after the task completes successfully. But when the task encounters an error/deadlock, this information is ignored and only the error/deadlock message is logged in the task history.

WORKAROUND

There is no way to get information on the exact number of transactions that were committed before the error/deadlock was encountered. This is generally not an issue since the tasks, by default, do not log any information when they are in "auto-start" mode. Generally, the only information of interest is the error/deadlock message. Note that the subscriber has some of the modifications even though the distribution task reports the error/deadlock message. And any error condition will have to be corrected by the administrator; deadlock conditions are usually taken care of, since the task is automatically set to retry.

MORE INFORMATION

1. Tasks with different jobbatchsize and commitbatchsize.
   
   Suppose the distribution task has a jobbatchsize of 100 and a commitbatchsize of 10. After 50 jobs are distributed, a deadlock is encountered. You see the deadlock message, but there is no indication that 50 jobs were committed. This may also apply to the logreader, since the transbatchsize and commitbatchsize can be configured for different values.




2. Tasks that are auto-start or scheduled.
   
   Auto-start replication tasks (logreader, distribution) read from one database/transaction log and write to another database. Say the distribution task reads jobbatchsize rows out of distribution database tables and writes to the subscriber in commitbatchsize batches; when the task is done with the jobbatchsize rows, it checks back with the distribution database to see if there are more rows to distribute (this is also the case with scheduled tasks, where the check is to see if there are more jobs to be distributed, based on the jobbatchsize and commitbatchsize). Assuming both jobbatchsize and commitbatchsize are 100, the task reads 100 jobs, writes these 100 jobs as a single transaction, and checks back to see if there are more jobs. Now, after a few such attempts, and after having inserted a few hundred jobs, the task runs into deadlock; it just reports that the process encountered a deadlock and gives no information about the committed jobs. This also applies to the logreader, since it is designed to check the transaction log for new replicated transactions.

Additional query words: deadlock missing committed transaction info

Keywords :
Version : 6.0 6.5
Platform : WINDOWS
Issue type :