Error Fix – Replication subscriber does not exist on the server anymore

Recently one of my client has faced weird situation related to SQL Server Replication. Their main database goes down so they started running mirroring database as a primary database, removed subscriber and distributor. Once they have setup primary server back properly, they have restored mirrored database backup to primary server.

Now situation is complicated as subscriber was removed, distributor was removed but in primary server, they are seeing publication as well as subscriber. They have already tried the way I have explained in one of my earlier article “Forcefully remove replication publisher, subscriber and distributor in SQL Server” but no luck.

When they tried to remove publication by pressing “Del” key after selecting “Publication” under “Replication” in SSMS, they faced following message.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not delete publication 'SMXPPublisher'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476
------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------

Cannot drop the publication because at least one subscription exists for this publication. Drop all subscriptions to the publication before attempting to drop the publication. If the problem persists, replication metadata might be incorrect; consult Books Online for troubleshooting information.
Changed database context to 'SMXP'. (Microsoft SQL Server, Error: 14005)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=14005&LinkId=20476
------------------------------
BUTTONS:

OK
------------------------------

Here is the screen capture of the error:

RemovePublicationError

This is obvious message that they can’t delete Publisher until and unless they remove subscriber under publication but the twist is they have already deleted subscriber from the secondary server and primary server were down at that time so effect doesn’t came there so while deleting subscriber from the primary server, they were greeted with following message.

TITLE: Microsoft SQL Server Management Studio
------------------------------
"MARS:SMXPRepl" does not exist on the server anymore. Please refresh its parent tree node.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=ObjectNoLongerExists&LinkId=20476
------------------------------

BUTTONS:

OK
------------------------------

Here is the capture for this error:

RemoveSubscriberError

Now, there is a big question, how to remove this replication?

After getting in this clumsy situation, they have called me up to help. They needed very fast solution so I provided undocumented and dirty but yet workable way. Here is what I have asked them to perform and update me with the result.

Here is the small TSQL script I told them to run on primary server in replicated database.

USE master
EXEC sp_removedbreplication @dbname='smxp'
GO

sp_dropsubscription @subscriber='smxprepl'

sp_droppublication 'smxppublisher'

After executing above given commands, they were facing following error:

--Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for replication.

Well, my suggestion now is to unmark “MSPeer_lsns” object from replication with following command and then try to execute above script to remove and drop replication.

sp_MSunmarkreplinfo 'MSpeer_lsns'

Again there wasn’t luck with them so finally I send them one more TSQL which did the magic.

DROP TABLE sysarticleupdates
DROP TABLE sysarticles
sp_removedbreplication 'smxp'
GO
DROP TABLE syspublications
DROP TABLE sysschemaarticles
DROP TABLE systranschemas
DROP TABLE syssubscriptions
DROP TABLE sysarticlecolumns
DROP TABLE MSpub_identity_range
DROP TABLE MSpeer_response
DROP TABLE MSpeer_request
DROP TABLE MSpeer_lsns
DROP VIEW sysextendedarticlesview

After executing above given TSQL script, it has removed everything related to replication. However, I don’t recommend this script for normal situation.

Here is the list of my other replication related articles, if you are interested!!!!

Replication Related Articles:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)
  • Find undistributed replication command in SQL Server (Click Here)
  • Keep watch on replication undistributed command in SQL Server via SSIS (Click Here)
  • Keep an eye on replication error in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.