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)

SQL Error Messages, SQL Replication, SQL Scripts, SQL Server
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

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.

    Reply
  • Thank you for sharing the command it worked :-)

    Reply
  • Thanks Pinal

    Reply
  • Prashant Bharucha
    July 4, 2020 12:16 am

    Thank you Pinal !, appreciate your help !

    Reply
  • 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!

    Reply
  • Thank you! This has just solved my issue really quickly which had been troubling me for the past couple of hours.

    Reply
  • Thanks Pinal, you saved the day yet again!

    Reply

Leave a Reply