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.
Best Answer
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