May 4, 2015 at 11:56 am
Hi,
We have push transactional replication in our env.
34 tables are replicating. The publisher & distributor are the same instance (server).
We need to delete the 15million records from one of the table which is configured to replication.
How to proceed with this?
How to pause the replication and do sync after the delete
May 4, 2015 at 3:05 pm
I would simply delete those 15 million records from source tables and that delete operation will be replicated to the destination table which will remove those records from destination tables too so you wont have to do anything there; so in another words I would not stop or pause the replication.
lets wait what other are saying.
regards,
Dev
May 4, 2015 at 4:06 pm
What is the total table size? If the whole table is 20M rows, you're probably better off breaking replication, doing the delete, and then taking a new snapshot to start with of the table with only 5M rows now. If the table has 2B rows, obviously that would not be a good idea :-D.
If at all possible, delete the rows in batches and give replication time to get "caught up". For example, delete 40,000 rows, wait a bit, delete 40,000 more, etc.. How long to wait depends on your drive speeds, speed of the network to your replicated data, etc..
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 4, 2015 at 4:42 pm
Table total size is 5000MB
May 5, 2015 at 5:32 pm
Hi,
I'm looking for similar process but i DO NOT want to replication the delete from nightly job in source table. I have nightly job that purge records in few source tables retaining only 3 yrs data. I have archive database that contains prior 3 yrs data and current 3 yrs data. Before nightly job DELETES records in Source table i want to STOP replication so that the delete is not replicated in archive database. After the job completes i would like to TURN ON replication so that any new inserts and updates in Source will be replicated in archive database.
Please suggest if this is possible or any other best way to do.
Thanks
Gopi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply