Microsoft KB Archive/229086

= FIX: Logreader Reports Timeout Error on Sp_repldone or Sp_replcmds =

Article ID: 229086

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q229086



BUG #: 55023 (SQLBUG_70)



SYMPTOMS
Logreader reports one of the following errors:

The process could not execute sp_repldone/sp_replcounters on.

Unable to execute sp_replcmds on : [odbc] [sqlserver driver] timeout expired

Error details on the last error shows:

category: ODBC

source: odbc sql server driver 0

error message: timeout expired

category: agent

source: __<2> (agent name)

Number: 22017

error message: The process could not set the last distributed transaction

This error occurs with sp_repldone or sp_replcmds when tables with text/image datatypes are replicated and SELECT operations are performed on the text/image columns.



CAUSE
SELECT operations on text/image datatype columns log the retrieval of Text Pointer for the text/image data for every row it scans to get the results of the SELECT statement. This causes logging of Text pointer information for all the rows scanned and can cause excessive logging. Logreader scans through all these records and correctly finds that there is no replicated transaction. However, the time it can take to scan the log can cause the timeout message to be sent to the logreader.



WORKAROUND
You can use either of these workarounds:
 * 1) Avoid SELECT statements on text/image datatypes on tables participating in replication. Alternately, you can use triggers to populate a shadow table in the same database and replicate this shadow table. This avoids the problem if you have SELECTs happening on the original table.
 * 2) Increase the Query timeout (default: 300 seconds) on the logreader by editing the QueryTimeout option of the logreader agent. Refer to the SQL Server Books Online for details on editing options of replication agents.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

Additional query words: repl hang not work logread fail

Keywords: kbbug kbfix KB229086

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.