4

Our infrastructure is as follows:

Publisher on Live db server, Distibutor and subscriber on our mirror server.

Basically yesterday we were doing archiving on a huge table on our live server, (tblEventlog), and I was deleting rows month by time, millions of records. This caused the transaction log to fill up completely...so when we restarted server and got the space back, we now have 144225820 commands in our distribution database (on mirror)...and estimated time of 27 hours to apply and catch up.

Basically I am looking for the quickest fix to this - I know the offending table is tblEventLog. Can we drop this from the mirror and then export a copy (with the deletes applied) from Live...would this have any effect on the transactions waiting in the distribution db? Would the distributor still try and apply them even though the rows are now gone, or would it just skip them?

If so, whats the best way out of this...would rather not reinitialise as its a huge db and stresses live server.

Publisher is SQL Server 2008, Subscriber server is SQL Server 2012.

0

When it comes to replication you have options on how you want to initialize transactional replication. You can do that with a snapshot or from a backup. Only focusing on these two methods as they are the most common.

You may want to consider initializing from a backup to help save time, and to allow your distributor to only have to keep records of what was changed for the time it takes to restore the database.

Microsoft Article that explains the concept

MSSQL Tips article specific for SQL 2008

0

If you try to update that table directly on the Subscriber, the Publisher will try to update the same records and probably cause Replication to fail.

You could try to move that one table to it's own Article, and then reinitialize the snapshot for just that one Article; but this will probably mean tearing down the original Subscription database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.