The process could not execute sp_repldone/sp_replcounters

  • We are getting the following error on all of the repliction from one server:

    The process could not execute 'sp_repldone/sp_replcounters'

    Is there a good way to fix it? It all worked until last night when someone executed an update the affected 7 million rows. We have since taken replication off that database. But all the other databases have since had this error.

    The agent is also giving this error:

    The process could not set the last distributed transaction.

    Please help!

    Thanks,

    Ross

  • Did the UPDATE on 7 million rows work?

    If it didn't, did the transaction log run out of space? Also check the transaction log on the Distribution Databases.


    Kindest Regards,

  • The update did work. The problem now is that we have so many transactions that the log reader agents are timing out trying to read them. We have been on the phone with MS PSS  and they have been somewhat of a help, but so far there ideas have not worked.

     

    Ross

  • Well we go our issue taken care of. It was not pretty. Here is the lowdown. Essentially we overloaded replication with an update that affected 7 million rows, along with several other process that were updating lots of rows. So we stopped replicating some publications which basically left all the updated rows in the transaction log as marked for replication. The transaction marked for replication could not be removed from the log during normal backup. This created problems because the logs were growing taking up disk space.

    Normally in this situation executing sp_repldone will clean these up. Problem was we had so many transactions that sp_repldone was running for hours on small databases.

    This left us with one option. Detach the database, rename the log file, reattach the database. A final approach but it worked and the server is functioning better than before.

    Hopefully this can help someone else.

    Ross

  • We had the same problem as Ross was having. Stopping the replication and rebuilding the transactional replication for the single table didn't work.

    At first I tried running the sp_repldone with the options to reset the replication:

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

    and

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 0

    This didn't work.

    What I then did was:

    1. Script all the replications on the database
    2. Delete all the replications and subscriptions to and from the distribution database (right-click | delete)
    3. ... and allowing the settings on the subscriber databases to be deleted.
    4. Take the database out of the list of distributable databases
    5. Add the database back in to the list of distributable databases
    6. Apply the scripts to get the replications back
    7. Re-Initialize all the subscriptions
    8. up and running !!!

    Hope this helps future admins.

    Thanks to Ross too.


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • There is an undocumented stored procdure called sp_replrestart which you run against the source database via query analyser. You will still need to reinitialise the publication after running this but you will then be able to restart the log reader and move on.

  • hi it's good and working fine.

    we have no sync transaction replication so, there is no error and it's workig fine after sp_replrestart .

    Thanks Very much

    Rajni kant ranjan

  • Thank you jonathan.rothwell,

    I ran into same problem after I recover my publisher database then run this store procedure in QA and run agents and reinitialize publisher, it worked fine.

    Thanks

    Amin Omidy DBA

  • I'd faced a similar problem a few months back where the agent wasn't able to process the large log file after a massive update in the 300 sec timeout window and was failing as a result. This issue was addressed by extending the timeout (to 1200 sec in my case).

    Distributor and Log Reader Agent profile property: Query Timeout.

    Here is the error profile to compare:

    Error Message:

    The process could not execute 'sp_repldone/sp_replcounters' on 'DB'.

    Timeout expired

    (Source: ODBC SQL Server Driver (ODBC); Error number: 0)

    ---------------------------------------------------------------------------------------------------------------

    The process could not set the last distributed transaction.

    (Source: DB (Agent); Error number: 22017)

    ---------------------------------------------------------------------------------------------------------------

  • running sp_replflush on Publisher database will also solve the problem without recreating Publications and subscriptions 🙂

    Nag
    ------------------------------------------------
    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • sp_replrestart command solved my problem "The process could not execute 'sp_repldone/sp_replcounters'".

    Devendra Bhilare, India, Pune.

  • Scripting out the publications, deleting and recreating them and the subscriptions worked for us.

  • SQL error: "the process could not execute SP_repldone/sp_replcounters".

    The fix was actually surprisingly easy. You just need to:
    1) drop replication > EXEC dbo.usp_Replication_Drop;
    2) recreate your publication(s) > 
    USE [databaseName]
    GO
    EXEC [dbo].[usp_ReplicationCreatePublication_NameOfPublication]
    WAITFOR DELAY '00:00:30'; -- Wait for snapshot agent job to finish before continuing
    EXEC [dbo].[usp_ReplicationCreateSubscription_NameOfPublication] 'ServerName','DatabaseName'

    Thank you,

  • sp_replrestart command worked for me .  No Parameters required , you can simply run this command on the DB where you have this issue. Note that all the publications on that DB will be restarted.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply