TransWikia.com

How to completely get rid of replication subscriptions?

Database Administrators Asked by ReluctantDBA on November 5, 2021

I have restored a database in another server. I don’t want any of the transactional subscriptions of the former database. I already called

exec sp_removedbreplication 'MyRestoredDB' 
go

And also

exec sp_cleanupdbreplication
go

And

exec sp_replicationdboption 'MyRestoredDB','Publish','False',1
go

But when I try

truncate table dbo.sample

I get the following error:

Msg 4711, Level 16, State 1, Line 7 Cannot truncate table ‘sample’
because it is published for replication or enabled for Change Data
Capture.

I verified that the database isn’t enabled for CDC. What else I can do to perform the truncate? Delete isn’t an option for reasons not relevant to the question.

One Answer

Take a look at this article: https://www.mssqltips.com/sqlservertip/2710/steps-to-clean-up-orphaned-replication-settings-in-sql-server/

Try dropping the old subscriptions, all publications and then removing replication from the DB, i.e.

exec sp_dropsubscription --on all subscriptions

then

exec sp_droppublication --on all publications

then

exec sp_replicationdboption

Also, take a look at this question: How to fix a messed up replication on MS SQL Server

Answered by HandyD on November 5, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP