Thursday, October 09, 2008

If you delete a data partition on a merge repl, make sure you do this additional clean up

We wanted to delete old data partition on our merge publication. The Management Studio has a nice UI to view the data partitions and generate the dynamic snapshot per partition, also to delete the partitions you no longer need.



Be warned that this nice UI delete button won't delete the partition folder at the distributor nor will delete the dynamic snapshot job at the distributor. If you try to add a partition with the same filter token, it will fail.

In order to have a fresh start for that partition value you should:

1. Verify that the subscriptions that use that data partition don't exist anymore.
2. Delete the data partition using the Publication Properties UI
3. Delete the data partition using the sp_dropmergepartition stored procedure:

use [Published_Database]
exec sp_dropmergepartition @publication = N'Publication_Name', @suser_sname = N'', @host_name = N'the string we use to filter the data part'
GO


4. Delete the replication partition folder manually at \\distributor\repldata



Happy data merging!


PS SQL Server version 9.00.3228

Labels: , ,