How do I remove transactions marked for replication?

Question

How do I remove transactions marked for replication?

Melissa Kadiri

Default port

Points: 1435
More actions
August 2, 2006 at 8:52 am

#94170

I did a dump/load with the force restore option from a published database to a dev db. The log in the dev environment has now grown to 40GB in the past week or so ("simple" model). When I attempt to truncate the transaction log in the dev database, I get the following message:
"The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed."
I executed sp_repldone, and it succeeded. I executed sp_replicationdboption to remove the publish settings, and it succeeded. I executed sp_removedbreplication, and it succeeded.
However, I cannot truncate the tran log. BOL is not much help. Anyone?
Regards, Melissa

Viewing 5 posts - 1 through 4 (of 4 total)

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

Melissa Kadiri Default port Points: 1435 More actions · Answer 1

FYI: sp_detach_db followed by sp_attach_single_file_db (removing the .ldf in between) appears to be the only way to resolve this, following results of a google search.

The bug is SQL2K, and supposedly fixed in SP2; however, we are on SP4 and this is still apparently a bug.

Regards, Melissa

Neil Hambly Valued Member Points: 67 More actions · Answer 2

Ifd you want to 'Clear' these pending transactions for Replication, you can force the action by using a replication system Sproc .. review BOL for the use of the following sp_repldone

Examples

When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log, for example:

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

You will need to redone  replication snapshots are manually sync between Databases etc..

Melissa Kadiri Default port Points: 1435 More actions · Answer 3

Yes, thanks for your note. However, if you refer to my original posting, you will notice that I did execute sp_repldone with success. The information & string was in BOL; but, it was not helpful and the error still occurred with the pending transactions. Only the attach/detach worked.

Regards, Melissa

homebrew01 SSC Guru Points: 55454 More actions · Answer 4

Here are my notes from when I encountered this. Perhaps you had the same problem as me in step 1 because the restored database was not a publisher ?

1) Tried running: sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 but got an error because restored database is not a publisher

2) Created a temporary snapshot publication to mark the DB as a publisher

3) Tried sp_repldone again, and it worked this time.

4) Shrunk the log w/ dump tran advdbupg with no_log

5) Deleted the temporary publication

6) the DB is still marked as a publisher, but I don’t think that matters.

Another fix that may have worked, would be to detach the database, delete the LDF file, then re-attach and let SQL create a new LDF … didn’t try it though