SQL SERVER – Unable to Remove Replication Publication – Could not Delete the Subscription at Subscriber ‘SubServer’ in Database ‘SubDB’

SQL SERVER - Unable to Remove Replication Publication - Could not Delete the Subscription at Subscriber 'SubServer' in Database 'SubDB' replication While playing with my lab server having broken replication, I encountered an error message when I was trying to remove publication. In this blog, we would learn about how to fix error Could not delete the subscription at Subscriber ‘SubServer’ in database ‘SubDB’.

Basically, I was not able to remove Publication ‘PubName’ and it reported the below error message:

Could not delete the subscription at Subscriber ‘SubServer’ in database ‘SubDB’.
Invalid object name ‘dbo.syspublications’. (Microsoft SQL Server, Error:208)

WORKAROUND/SOLUTION

Based on my research, the error should not come if replication objects were cleaned up correctly. This means that in my environment there must be some orphaned replication objects, or some other commands blocked the previous command. I ran below command and it was successful.

EXEC sp_removedbreplication 'PubDB'

Here the documentation of sp_removedbreplication

This stored procedure removes all replication objects on the publication database on the Publisher instance of SQL Server or on the subscription database on the Subscriber instance of SQL Server. Execute in the appropriate database, or if the execution is in the context of another database on the same instance, specify the database where the replication objects should be removed. (ref)

Hope this would help someone who is having basic knowledge of SQL Replication and became accidental DBA.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

, , ,
Previous Post
SQL SERVER – Useful Queries – Why Huge Transaction Log (LDF) File? Fix Low Disk Free Space on the Drive Used by LDF Files
Next Post
SQL SERVER – FIX: Error – The Job Failed. Unable to Determine If The Owner Domain\User of Job Job_Name Has Server Access

Related Posts

5 Comments. Leave new

Leave a Reply

Menu