SQL Server Performance Forum – Threads Archive

Logreader failed with timeout

On a server with transactionnal replication (SQ2K SP3), the LogReader failed with the following message : The process could not execute ‘sp_replcmds’ on ‘RHODIA-4M1’. NOTE: The step was retried the requested number of times (10) without succeeding. The step failed. [SQLSTATE 42000] (Error 14151). The step failed. This problem appear after a maintenance plan (Optimization & Integrity), and now I have a Log > 10Go and the LogReader couldn’t start with the same message. This server run well with this maintenance plan and this replication for a lot of months. I dont’t know what appended and how can I restart the Logreader. I have more than 5000 transactions not replicated in the log and all servers (source and target of replication) are production servers. I try all I find on MSDN (modify the ReadBatchSize and th QueryTimeOut parameter)but it doesn’t work. Somebody can help me to resume this problem ?
PS : Sorry for my bad English. I hope that you nevertheless succeeded in understanding me.
jfou

Take help of this kBAhttp://support.microsoft.com/?id=811030 to resolve the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you for the answer, but I already tested all that is in this KBA. More surprising, is that this problem arrived suddenly after many months without incidents. Other ideas?
jfou

Are there any recent changes to SQL Server, such as installation of Service pack or hotfix.
Check any issues on the network, H/w for further assessment. Have you changed the querytimeout value recently? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

There’s no recent changes on the server. I tried to modify the value of the querytimeout of the LogReader agent, but that does not solve the problem.
When Logreader is stopped, if I execute the procedure sp_replcmds since SQL Query Analyzer, that functions well.
jfou

Check SQLAgent log for any informatoin and try to stop and start LogReader Agent for a refresh. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The logreader agent history give me following informations : Yesterday, the message was :
Error Message :
The process could not execute ‘sp_replcmds’ on ‘RHODIA-4M1’.
Error detail :
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
————————————————————————————————————— Today, the message is :
Error Message :
The process could not execute ‘sp_repldone/sp_replcounters’ on ‘RHODIA-4M1’.
Error detail :
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
—————————————————————————————————————
The process could not set the last distributed transaction.
(Source: RHODIA-4M1-Arlesienne-3 (Agent); Error number: 22017)
————————————————————————————————————— Note : A backup log was run last night Thank a lot for your help… jfou

As defined in this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;312292&Product=sql2k enable logging in order to see whats happening during this process.
How often you backup the Transaction log?
What is the size of transaction log when its backedup?
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you very much for your assistance, but in the urgency (because of the available disk space), I have to remove the replication and to reconfigure it. Now, it goes well.
jfou

Hi. I’m getting the same problem. It seems strange to me, because this error seems to be occurring out of nowhere. The server is NT4.0 sp6a, SQL 7.0 sp3 (I think…). This machine was running fine in the summer. The replication had no problems at all. Then I had to purge a large table, and had to disable the replication to do so (needed to truncate the table). So somewhere along the process of disabling, I removed the publication completely. I recreated it, but now it’s just not running as it used to. The distribution cleanup kills the server – it slows it down almost to a stop. We had to schedule it to stop during the customer’s peak hours. Now it seems the log reader stops with a time out. I found that article before checking in here (kb 811030), but I’m not sure I should mess around with the settings seeing as how they worked in the summer as they were. The only thing that has changed is 1. the size of the db, and 2. the replication is using stored procedures now. My memory isn’t great, but I don’t recall using stored procedures in the last time I configured replication. Does this make sense?? Thanks..,[?]

In case you haven’t checked these: Is there any blocking going on? Is the linked server or remote server security changed or not? Is password correct? Is disk space an issue?
Richard

I would suggest to upgrade the sQL server to the latest service pack which is SP4 and apply referred hotfixes by MS security update. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi,
I have the same problem, Distribution and Log reader agent is timing out due to distribution clean up agent taking hours and causing blocking on the database. Simply restarting the log reader or distribution agents means they start to run ok again, but i’m not around at 3am to do this (I have a life away from my desk!). I have extended the timeout period for the agent profiles and this made no difference, the clean up agent is just painfully long and obstructive to everything else. Have been battling the issue for weeks now, any suggestions would be great Thanks JKS

Based upon the time taking to completing the cleanup, try to schedule a job to restart Log reader agent. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yesterday, the message was :
Error Message :Microsoft ODBC sql server driver
Error detail : Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number:-2147217871) Thank a lot for your help… jfou

]]>