SQL Server Performance

Logreader failed with timeout

Discussion in 'Performance Tuning for SQL Server Replication' started by jfou, Sep 21, 2004.

  1. jfou New Member

    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
  2. satya Moderator

  3. jfou New Member

    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
  4. satya Moderator

    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.
  5. jfou New Member

    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
  6. satya Moderator

    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.
  7. jfou New Member

    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
  8. satya Moderator

  9. jfou New Member

    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
  10. Eh New Member

    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..,[?]
  11. Richard Ding New Member

    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
  12. satya Moderator

    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.
  13. juniorkickstart New Member

    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
  14. satya Moderator

    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.
  15. SAMIULLAHKHAN New Member


    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

Share This Page