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)
8 Comments. Leave new
Very interesting analysis. Great information. Since last week, I am gathering details about SQL Experience . There are some amazing details on your blog which I didn’t know. Thanks.
I would like to personally congratulate you as your blog.I personally give you a high-five and want to thank you for your contribution to this world.
Thank you for sharing the command it worked :-)
Thanks Pinal
Thank you Pinal !, appreciate your help !
After restoring a database orphaned replications showed up. Using SSMS to delete only generated errors. Running this command on the publication database fixed my issue. Thank you!
Thank you! This has just solved my issue really quickly which had been troubling me for the past couple of hours.
Thanks Pinal, you saved the day yet again!