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

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

The log file contains the following information:

Status: 2, code: 0, text: 'The process could not execute 'sp_replcmds' on servername'.
The process could not execute 'sp_replcmds' on servername.
Status: 2, code: 0, text: 'Timeout expired'.
Disconnecting from Publisher servername
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:

Create a new Log Reader Agent profile or modify the existing Log Reader Agent profile.
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.
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://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_replctem_241e.asp

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.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

308865 FIX: Logreader fails with "Could not execute Sp_replcmds" error message

315642 Information that PSS needs to troubleshoot SQL Server replication