11

I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and re-create it (we have a script to re-create it from scratch). I'm not sure exactly what I did, but now it is in a completely messed up state and I can't fix it.

First, I try to get rid of the subscription (on the publisher server):

EXEC sp_dropsubscription @publication = 'PublicationName', @article = N'all', @subscriber = 'SubscriberServerName'

This seems to work. SELECT * FROM syssubscriptions shows no results. Looking on the subscriber server, SSMS > {SubscriberServer} > Replication > Local Subscriptions - the subscription is not there.

So then I try to delete the publication. SSMS > {Server} > Replication > Local Publications > {PublicationName} > Delete. This gives the following error message:

Could not delete publication 'PublicationName'.
Could not drop article. A subscription exists on it.
Changed database context to 'DatabaseName'. (Microsoft SQL Server, Error: 14046)

Ok, so I try to drop the articles:

EXEC sp_droparticle @publication = 'PublicationName', @article = N'all'

and get this error:

Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.
Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.

Ok, so I try starting the Snapshot Agent and I get this internal SQL exception:

The SQL command 'sp_MSactivate_auto_sub' had returned fewer rows than expected by the replication agent.

So I tried an alternative method of deleting the article, DELETE FROM sysarticles. This seems to have worked - I have now got rid of the articles, but I still get the same 'Cannot drop the publication because at least one subscription exists for this publication' error when I try to delete the publication.

I have also restarted SQL Server - didn't help.

I don't know what is going on here and how do I fix it?

BTW this is what happens when you give a software developer who knows just enough to be dangerous the keys to the database. Fortunately, this isn't a production environment...

0

6 Answers 6

12

TLDR:

It appears that disabling and re-enabling replication probably fixed the issue:

exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false'
exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true'

I guess this is the equivalent of switching it off and then back on again...

Longer version:

A workmate had a go at trying to fix it. He tried a few things but didn't get very far. The one change he did make before giving up was to disable the replication.

I then tried Cody's suggestion. The sp_dropsubscription command complained that no subscriptions exists. So I tried the sp_droppublication command. This complained that replication was not enabled on the database. So I enabled it and re-ran the command. This time it complained that the publication did not exist. I refreshed the Local Publications node in SSMS and sure enough it had gone. I ran the replication set up script, generated a new snapshot and every thing is now working properly. Joy!

I'm not 100% certain that disabling and enabling the replication is what actually fixed the problem, but it is definitely worth trying if the replication gets messed up.

3
  • Great read for newbies. Is it safe to say that you should disable replication first before restoring the database? Apr 19, 2016 at 22:00
  • I'm certainly going to try that next time - from what I've read the replication should not have to be completely blown away and recreated (as I initially thought it would). Disable the replication, restore the database, enable the replication, push a new snapshot. As long as the articles are still valid, it should be good to go. Worth a try anyway...
    – TallGuy
    Apr 20, 2016 at 3:43
  • Total replication newbie here, but following the TLDR; instructions has led to the disappearance of my publications from SSMS. Querying MSPublications in the distribution database reveals that the publication is indeed gone. Is this expected?
    – pim
    Oct 16, 2017 at 16:26
8

I had a mess with replication and solved it with this

DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'DBName'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO 

That and:

exec sp_cleanupdbreplication

Are the saviours when cleaning up messed up replications.

1
  • 1
    I believe your Post just saved me from re-setting up my Test environment. Not sure which command above did it, but I can now Delete Indexes without the Error about them being published for replication. Many Thanks to you.
    – MHSQLDBA
    Apr 13, 2018 at 11:49
2

Restoring the database will break replication, so that's normal. Also most of the other error messages are just follow ons because you haven't been able to remove all the subscriptions (or at least SQL thinks so).

You know you have your publisher (the source database), and at least one subscriber (the destination database), and that these are two different servers. I just want to mention that there's also a distributor which is on either of these servers or another, and likely in a database named distribution. Sometimes it has some useful info in there and sometimes things fall over because the information between the three doesn't match.

Anyway, when you checked the subscribers, did you also check that section on the publisher server to make sure there was nothing else listed? If you find any you can try to remove it manually:

exec sp_dropsubscription @publication = N'xxx', @subscriber = N'xxx', @destination_db = N'xxx', @article = N'all'
-- And if that doesn't work
exec sp_dropsubscription @publication = N'xxx', @subscriber = N'xxx', @destination_db = N'xxx', @article = N'all', @Ignore_Distributor = 1

But assuming they really are all gone, try this on the publisher database:

exec sp_droppublication @publication = N'xxx'
-- And if that doesn't work
exec sp_droppublication @publication = N'xxx', @Ignore_Distributor = 1

Let us know how it goes. Replication when it gets into this state confounds me and other good DBAs nothing to do with being a developer at all :-)

2
  • Thanks for the suggestion. The sp_dropsubscription command complained that no subscriptions exists. The sp_droppublication command complained that replication was not enabled - which led me to what appears to be the solution.
    – TallGuy
    Apr 19, 2016 at 20:27
  • For me, sp_removedbreplication command worked for most of the time whenever I had to forcefully remove the replication.
    – SQLPRODDBA
    Apr 20, 2016 at 4:41
0

The only way I have been able to get rid of phantom replication artifacts is to drop the subscriptions, articles, publications. If there are still phantom subscriptions, then recreate the publication including the phantom subscriber. This seems to work with older versions especially.

0

this is what I normally do when I have a publication that is messed up.

it is a bit ugly but it has worked for me on many occasions on different environments. what causes it? that is sometimes difficult to figure it out, best sometimes is to start from scratch, but even for that you need to clear up all the residuals from the current publication that is faulty.

just to put it into context:

This is what I see from the replication monitor:

enter image description here

and when I use my own replication monitor using T-SQL:

DECLARE @cmd NVARCHAR(max)
DECLARE @publisher SYSNAME, @publisher_db SYSNAME, @publication SYSNAME, @pubtype INT
DECLARE @subscriber SYSNAME, @subscriber_db SYSNAME, @subtype INT
DECLARE @cmdcount INT, @processtime INT
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @JobName SYSNAME
DECLARE @minutes INT, @threshold INT, @maxCommands INT, @mail CHAR(1) = 'N'
SET @minutes = 60 --> Define how many minutes latency before you would like to be notified
SET @maxCommands = 80000  --->  change this to represent the max number of outstanding commands to be proceduresed before notification
SET @threshold = @minutes * 60

IF OBJECT_ID ('TEMPDB..#Replication_Qu_History')  IS NOT NULL
   DROP TABLE #Replication_Qu_History

IF OBJECT_ID ('TEMPDB..##PublicationInfo')  IS NOT NULL
   DROP TABLE  ##PublicationInfo

IF OBJECT_ID ('TEMPDB..#PublisherInfo')  IS NOT NULL
   DROP TABLE  #PublisherInfo

IF OBJECT_ID ('TEMPDB..##SubscriptionInfo')  IS NOT NULL
   DROP TABLE  ##SubscriptionInfo

SELECT * INTO #PublisherInfo
FROM OPENROWSET('SQLOLEDB', 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES;'
, 'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublisher')

SELECT @publisher = publisher FROM #PublisherInfo     

SET @cmd = 'SELECT * INTO ##PublicationInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='
+ @publisher + ''')'
--select @cmd
EXEC sp_executesql @cmd

SELECT @publisher_db=publisher_db, @publication=publication, @pubtype=publication_type  FROM ##PublicationInfo

SET @cmd = 'SELECT * INTO ##SubscriptionInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher='
+ @publisher + ',@publication_type=' + CONVERT(CHAR(1),@pubtype) + ''')'
--select @cmd
EXEC sp_executesql @cmd


ALTER TABLE ##SubscriptionInfo
ADD  PendingCmdCount INT NULL,
EstimatedProcessTime INT NULL


SELECT *
FROM #PublisherInfo

SELECT *
FROM ##SubscriptionInfo 

SELECT *
FROM ##PublicationInfo 

you can see 2 lines on the last box below - and one one them should not be there:

enter image description here

the same when I use this script:

EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='my publisher'

enter image description here

First you do what is shown in the other answers above, if that works, that is fine, it sometimes works, problem solved.

that would be more of less it:

exec master.dbo.sp_replicationdboption @dbname = 'my_PUBLICATION', @optname = N'publish', @value = N'false'
exec master.dbo.sp_replicationdboption @dbname = 'my_PUBLICATION', @optname = N'publish', @value = N'true'

sp_droppublication @publication='my_PUBLICATION'

-- Remove replication objects from a subscription database (if necessary).
exec master.dbo.sp_removedbreplication 'my_PUBLICATION'

exec master.dbo.sp_removedbreplication 'my_PUBLICATION'

use my_PUBLICATION

sp_removedbreplication @type='both'


USE [master]
EXEC sp_replicationdboption 
  @dbname = N'my_PUBLICATION', 
  @optname = N'publish', 
  @value = N'false';
GO


EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='PUBLISHER_SERVER'

sp_replmonitorhelppublisher @publisher='PUBLISHER_SERVER'

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'my_PUBLICATION'; 
SET @publication = N'my_PUBLICATION'; 

-- Remove a transactional publication.
USE my_PUBLICATION
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish', 
  @value = N'false';
GO

Now to get rid completely of this publication we will start by connection to the buscriber, and then publisher, and then distributor as per the script below:

-- Connect Subscriber
:connect [SUBSCRIBER_SERVER]
use [master]
exec sp_helpreplicationdboption @dbname = N'SUBSCRIBER_DATABASE'
go
use [SUBSCRIBER_DATABASE]
exec sp_subscription_cleanup @publisher = N'PUBLISHER_SERVER', @publisher_db = N'my_PUBLICATION_DB', 
@publication = N'my_PUBLICATION'
go


-- Connect Publisher Server
:connect [PUBLISHER_SERVER]
-- Drop Subscription
use [my_PUBLICATION]
exec sp_dropsubscription @publication = N'my_PUBLICATION', @subscriber = N'all', 
@destination_db = N'SUBSCRIBER_DATABASE', @article = N'all'
go
-- Drop publication
exec sp_droppublication @publication = N'my_PUBLICATION'
-- Disable replication db option
exec sp_replicationdboption @dbname = N'my_PUBLICATION_db', @optname = N'publish', @value = N'false'
GO

-- Connect Distributor
:CONNECT [PUBLISHER_SERVER]
go

exec Distribution.dbo.sp_MSremove_published_jobs @server = 'PUBLISHER_SERVER', 
@database = N'my_PUBLICATION'
go

--===========================================================================================
--THAT DOES NOT GENERALLY GET RID OF THE JOBS FOR YOU
-- so you need to find them using these selects, and get rid of them manually yourself:

--select * from Distribution.dbo.MSpublications
--select * from Distribution.dbo.MSpublications
--===========================================================================================


select * from Distribution.[dbo].[MSlogreader_agents]
where publisher_db = N'my_PUBLICATION'

--found 1 job:
--PUBLISHER_SERVER-my_PUBLICATION-11

--script the job
--script the job delete script - and run that - keeping the job creation script just in case
exec msdb.dbo.sp_help_job @job_id=0x93C63D34E357704B818312B93FCA02FB
exec msdb.dbo.sp_delete_job @job_id=0x93C63D34E357704B818312B93FCA02FB



select * from Distribution.[dbo].[MSdistribution_agents]
where publisher_db = N'my_PUBLICATION'

--here found 2 jobs:

--PUBLISHER_SERVER-my_PUBLICATION-my_PUBLICATION--67
--PUBLISHER_SERVER-my_PUBLICATION-my_PUBLICATION--68


--here is the problem - it cannot find the jobs, the jobs are not even there anymore, one of those things
exec msdb.dbo.sp_delete_job @job_id=0x0F1564BAACD5464C988DE8957C25C411
exec msdb.dbo.sp_delete_job @job_id=0x6215C40F999CE248A30EE735E2C0E59D

--Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41 [Batch Start Line 52]
--The specified @job_id ('BA64150F-D5AC-4C46-988D-E8957C25C411') does not exist.


--Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41 [Batch Start Line 53]
--The specified @job_id ('0FC41562-9C99-48E2-A30E-E735E2C0E59D') does not exist.

exec msdb.dbo.sp_delete_job @job_name='PUBLISHER_SERVER-my_PUBLICATION-my_PUBLICATION'
PUBLISHER_SERVER-my_PUBLICATION-my_PUBLICATION--68

at this point re-create the publication as you would normally do

then put the snapshot to run

wait for it to finish generating the snapshot

MAYBE YOU DONT NEED TO RUN THE SNAP - try without running it first, most of the time it does work, also you can add just 1-2 small articles to the publication so that the snap runs quickly

but if you run the snapshot then you need to wait until it finishes before you can go to the next step - drop the publication

enter image description here

after that you generate the scripts to drop that publication as per the picture below: enter image description here

after that hopefully, when you run our original scripts above, or have a look at the replication monitor you will not see the faulty publication, only the good ones, in my case just one:

enter image description here

-1

I had the same problem on my pre-production box, the command

exec sp_cleanupdbreplication

seem to have worked on cleaning up bogus subscription entries...

Not the answer you're looking for? Browse other questions tagged or ask your own question.