Removing Subscriptions From Replication Monitor: A Step-By-Step Guide

Removing subscriptions from the replication monitor can be a tricky process, especially if you encounter errors or issues along the way. It is important to note that the steps to remove subscriptions may vary depending on the specific software and version you are using. However, here are some general guidelines and troubleshooting tips to help you remove subscriptions from the replication monitor successfully.

First, it is recommended to check both the publisher and subscriber to ensure there are no remaining publications or subscriptions. If there are, go ahead and delete them. In some cases, you may need to disable replication and then re-enable it to completely remove all traces of the subscriptions. Additionally, you can try running specific scripts or commands, such as sp_dropsubscription or sp_removedbreplication, to drop the subscriptions. If you are using a graphical user interface, you may also try deleting the subscriptions through the UI.

If you encounter errors or issues during the removal process, there are a few troubleshooting techniques you can try. One common issue is encountering error messages even after successfully removing publications and subscriptions. In this case, you may need to refresh the JOBS window to reflect the changes. Another issue could be missing subscriptions in the Subscription Watch List for a particular publication. This could be due to blocked distribution agent jobs, and restarting those jobs might resolve the issue.

It is also important to note that the underlying tables and metadata associated with the subscriptions may not always be cleaned up properly. You may need to manually delete rows from specific tables, such as MSsubscriptions and MSpublications, to completely remove all traces of the subscriptions. Additionally, recreating the publication with the same name and then deleting it can be an effective way to trick the system into removing all settings.

Overall, removing subscriptions from the replication monitor can be a complex process, and it may require a combination of different techniques to completely remove all traces of the subscriptions. It is always a good idea to script out your replication settings before making any changes so that you have a backup in case anything goes wrong.

Removing orphaned metadata from the distribution database

Firstly, check if you have any orphaned metadata in the distribution database. This can occur when publications or subscriptions are deleted, but their metadata remains in the database. If you are no longer publishing any data, you can try disabling publishing and distribution to remove the orphaned publications and subscriptions.

Next, you may need to look at the actual catalog or metadata tables and clean them up. The GUI and stored procedures often do not clean up the underlying tables properly. Run a select query on the relevant tables, such as MSsubscriptions and MSpublications, to see if there is any data referring to your unwanted subscriptions or publications.

If you find any orphaned metadata, you can delete the corresponding rows from the tables. Before making any changes, you may need to set sp_configure 'Allow Updates' = 1 to enable updates to the tables. Be cautious and back up your data before making any changes to your database.

In some cases, you may need to take more drastic measures. You can try un-installing replication and deleting the distributor database, or even disabling replication and re-enabling it.

If you encounter errors or issues during the process, it is recommended to seek further assistance from Microsoft support or forums, as they can provide specific guidance based on your setup and any error messages you encounter.

Using sp_removedbreplication

The `sp_removedbreplication` system stored procedure is used to remove all replication objects from a database without updating the data at the distributor. This procedure should be used only if other methods of removing replication objects have failed.

Sp_removedbreplication ''

You must run the stored procedure at the Publisher server on the publication database or at the Subscriber on the subscription database.

For example, the following code removes replication objects from the subscription database on `MYSUB`:

- Remove replication objects from the subscription database on MYSUB.

DECLARE @subscriptionDB AS sysname

SET @subscriptionDB = N'AdventureWorks2022Replica'

- Remove replication objects from a subscription database (if necessary).

USE master

EXEC sp_removedbreplication @subscriptionDB

GO

Disabling replication and re-enabling it

Step 1: Drop all subscriptions

Use the following stored procedures with the appropriate parameters:

• Sp_dropsubscription: Run this at the Publisher server on the publication database to drop subscriptions to a particular article, publication, or set of subscriptions.
• Sp_droppullsubscription: Run this at the Subscriber on the pull subscription database to drop a subscription.
• Sp_dropmergesubscription: Run this at the Publisher server on the publication database to drop a subscription to a merge publication and the associated Merge Agent.
• Sp_dropmergepullsubscription: Run this at the Subscriber on the pull subscription database to drop a merge pull subscription.

Step 2: Drop all publications

After removing all subscriptions, you can remove the publications using the following system stored procedures:

• Sp_droppublication: Run this at the Publisher on the publication database to drop a publication and its associated articles.
• Sp_dropmergepublication: Run this at the Publisher on the publication database to drop a merge publication, the associated Snapshot Agent, and the associated articles.

Step 3: Drop the distributor

Before dropping the distributor, you must drop the subscriber designation from the Publisher using the sp_dropsubscriber stored procedure. Then, you can use the sp_dropdistributor stored procedure to remove the distributor. Run this at the distributor.

Step 4: Re-enable replication

After ensuring that all the above steps have been completed successfully, you can re-enable replication. However, it is important to note that you might need to run sp_MSLoad_replication_status to clear out any remaining error marks in the replication monitor. Alternatively, rebooting the system may achieve the same result.

Creating a new subscription with the same name as the problematic one

If you are encountering issues with subscriptions in the Replication Monitor, one possible solution is to create a new subscription with the same name as the problematic one. This approach has been suggested and tested by several users in online forums. Here is a step-by-step guide on how to do this:

• Identify the problematic subscription: Begin by identifying the subscription that is causing issues or errors in the Replication Monitor. Make a note of the subscription name, as you will need it for the next steps.
• Create a new subscription: Open the appropriate software for managing your subscriptions, such as SQL Server Management Studio (SSMS) or Azure portal. Navigate to the relevant section for creating a new subscription.
• Use the same name: When creating the new subscription, use the exact same name as the problematic subscription. Ensure that you are creating the new subscription in the same location or context as the original one.
• Delete the problematic subscription: Once you have created the new subscription with the same name, you can proceed to delete the problematic subscription. Follow the standard process for deleting subscriptions in your software.
• Refresh and verify: After deleting the problematic subscription, refresh the Replication Monitor to see if the issues have been resolved. Verify that the new subscription with the same name is functioning correctly and that there are no remaining errors or issues.

It is important to note that this solution may not work in all scenarios, and there are other suggested methods for removing problematic subscriptions. Additionally, when working with subscriptions, it is always recommended to have proper backups and to test any changes in a controlled environment before applying them to production systems.

Deleting the subscription and publication and starting over

Deleting a subscription and publication and starting over can be done by following these steps:

Firstly, it is important to note that deleting a subscription does not automatically remove objects or data from the subscription; these must be manually removed.

To delete a push subscription to a snapshot or transactional publication, you can follow these steps:

• Connect to the Publisher in SQL Server Management Studio and expand the server node.
• Expand the Replication folder, then the Local Publications folder.
• Expand the publication associated with the subscription you want to delete.
• Right-click the subscription and click Delete.
• In the confirmation dialog box, choose whether to connect to the Subscriber to delete subscription information. If you do not want to connect to the Subscriber, you should connect manually later to delete the information.

Alternatively, you can delete a push subscription at the Subscriber:

• Connect to the Subscriber in SQL Server Management Studio and expand the server node.
• Expand the Replication folder, then the Local Subscriptions folder.
• Right-click the subscription you want to delete and click Delete.
• In the confirmation dialog box, choose whether to connect to the Publisher to delete subscription information. If you do not want to connect to the Publisher, you should connect manually later to delete the information.

To delete a push subscription to a snapshot or transactional publication, you can use Transact-SQL:

• At the Publisher, on the publication database, execute sp_dropsubscription and specify @publication and @subscriber.
• Set a value of 'all' for @article.
• If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects at the Distributor.
• At the Subscriber, on the subscription database, execute sp_subscription_cleanup to remove replication metadata.

If you encounter issues with orphaned metadata in the distribution database, you can try disabling publishing and distribution to remove orphaned publications and subscriptions. In some cases, you may need to uninstall replication and delete the distributor database.

To delete a push subscription to a merge publication, follow these steps:

• At the Publisher, execute sp_dropmergesubscription, specifying @publication, @subscriber, and @subscriber_db.
• If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects.
• At the Subscriber, on the subscription database, execute sp_mergesubscription_cleanup. Specify @publisher, @publisher_db, and @publication to remove merge metadata.

In some cases, you may need to manually clean up the underlying tables as the GUI and stored procedures may not always clean up properly. You can do this by selecting and deleting data referring to unwanted subscriptions in the MSsubscriptions and MSsubscriptions tables.

Frequently asked questions