The process could not execute sp_repldone/sp_replcounters

  • bozo7

    Ten Centuries

    Points: 1278

    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

  • TriggerMe

    SSChampion

    Points: 11752

    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,

  • bozo7

    Ten Centuries

    Points: 1278

    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

  • bozo7

    Ten Centuries

    Points: 1278

    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

  • hot2use

    SSCrazy

    Points: 2855

    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.

  • jonathan.rothwell-638596

    Valued Member

    Points: 63

    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.

  • Rajni Kant Ranjan

    SSC-Addicted

    Points: 443

    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

  • amin Omidy

    Valued Member

    Points: 59

    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

  • appukp

    Valued Member

    Points: 61

    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)

    —————————————————————————————————————

  • Nagesh S-432384

    SSCrazy

    Points: 2201

    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

  • dsankpal

    Valued Member

    Points: 59

    sp_replrestart command solved my problem “The process could not execute ‘sp_repldone/sp_replcounters'”.

    Devendra Bhilare, India, Pune.

  • Matan_Yungman

    SSC Eights!

    Points: 911

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

  • keishahutch

    Grasshopper

    Points: 11

    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,

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

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