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:
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:
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.