Microsoft KB Archive/811030

= PRB: Log Reader Agent Does Not Function and You Receive an Error Message During Transaction Replication =

Article ID: 811030

Article Last Modified on 5/30/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



SYMPTOMS
When you use transaction replication to distribute the data, you may experience the following symptoms:

Log Reader Agent does not replicate the data and it displays the following error message:

The process could not execute 'sp_replcmds' on.

The log file contains the following information:

Status: 2, code: 0, text: 'The process could not execute 'sp_replcmds' on '.

The process could not execute 'sp_replcmds' on.

Status: 2, code: 0, text: 'Timeout expired'.

Disconnecting from Publisher

The agent failed with a 'Retry' status. Try to run the agent at a later time.



CAUSE
To facilitate transactional replication, Log Reader Agent moves the transactions that are marked for replication from the transaction log on the publisher to the distribution database. You may receive this error message when Log Reader Agent cannot move the transaction log within the time limit that you set in the Log Reader Agent QueryTimeout property.



WORKAROUND
To work around this problem:
 * 1) Create a new Log Reader Agent profile or modify the existing Log Reader Agent profile.
 * 2) Set the value of QueryTimeout property to 0 for the Log Reader Agent profile.

-or-

Increase the value of QueryTimeout property and decrease the value of ReadBatchSize property of the Log Reader Agent profile so that the processing of the transaction log is successful.
 * 1) Set the Log Reader Agent profile in that is referred to in step 2 as the default profile for Log Reader Agent.

Warning If you decrease the value of the Log Reader Agent ReadBatchSize property, Log Reader Agent may take longer to process the transaction log. Therefore, run enough tests before you set the value of the ReadBatchSize and QueryTimeout properties to make sure that the Log Reader Agent performance has not degraded significantly.

For information about how to create a new Log Reader Agent profile or to modify the Log Reader Agent existing profile, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/library/aa216617(SQL.80).aspx



MORE INFORMATION
The Log Reader Agent QueryTimeout property is defined as the waiting time in seconds before the queries that the agent issues time out.

Note For SQL Server 2000, the value of the QueryTimeout property must be a positive integer.

The Log Reader Agent ReadBatchSize property specifies the maximum number of transactions that are read from the transaction log of the publishing database. Decrease the value of the ReadBatchSize property to reduce the time that Log Reader Agent takes to process a transaction log batch.

