Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The process could not execute sp_repldone/sp_replcounters Expand / Collapse
Author
Message
Posted Thursday, March 11, 2004 9:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 15, 2007 9:22 AM
Points: 238, Visits: 1

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




Post #105590
Posted Thursday, March 11, 2004 4:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 6:29 PM
Points: 871, Visits: 277

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,



Post #105698
Posted Friday, March 12, 2004 9:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 15, 2007 9:22 AM
Points: 238, Visits: 1

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




Post #105848
Posted Monday, March 15, 2004 9:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 15, 2007 9:22 AM
Points: 238, Visits: 1

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




Post #106173
Posted Monday, June 28, 2004 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:26 AM
Points: 127, Visits: 44

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.
Post #123218
Posted Friday, February 15, 2008 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 25, 2011 1:53 AM
Points: 1, Visits: 13
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.
Post #456198
Posted Saturday, April 26, 2008 9:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:41 AM
Points: 156, Visits: 346
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
Post #491064
Posted Wednesday, June 04, 2008 4:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:06 AM
Points: 1, Visits: 54
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
Post #511844
Posted Thursday, June 12, 2008 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 14, 2009 6:28 AM
Points: 13, Visits: 49
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)
---------------------------------------------------------------------------------------------------------------

Post #515914
Posted Tuesday, March 30, 2010 12:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 05, 2014 3:29 AM
Points: 111, Visits: 472
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
Post #892474
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse