SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The process could not execute sp_repldone/sp_replcounters


The process could not execute sp_repldone/sp_replcounters

Author
Message
bozo7
bozo7
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 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





TriggerMe
TriggerMe
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5444 Visits: 328

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
bozo7
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 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





bozo7
bozo7
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 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





hot2use
hot2use
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1471 Visits: 71

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
jonathan.rothwell-638596
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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.
Rajni Kant Ranjan
Rajni Kant Ranjan
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 542
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
amin Omidy
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
appukp
appukp
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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)
---------------------------------------------------------------------------------------------------------------
Nagesh S-432384
Nagesh S-432384
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 579
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search