Cannot drop the table because it is being used for replication

Question

I am not able to modify the structure of a table in a database.

The database is used for replication.

I am getting This error

enter image description here

That's what is saying, it is in use by replication. you need to remove replication and drop table then install replcuation again. — FLICKER, Mar 21 '16 at 06:55
that's not possible in my case because database replicate from more then 30 clients. — pramod maurya, Mar 21 '16 at 07:03
So why are you going to drop the table? Are you a DBA? do you know how to deal with replication? — FLICKER, Mar 21 '16 at 07:06
@FLICKER i am not drooping just modifying table structure adding one new column in table — pramod maurya, Mar 21 '16 at 07:09
You cannot modify any schema is belong to replication. Please contact you DBA. — FLICKER, Mar 21 '16 at 07:10
So the answer is you need to remove replication change your schema and again, install it. It might be complicated depending on your database and network and environment. You also can ask your question on DBA site: http://dba.stackexchange.com/ — FLICKER, Mar 21 '16 at 07:15

score 4 · Accepted Answer · answered Mar 21 '16 at 06:55

4

As the error says the table is used for replication, meaning it's an article in a replication publisher. To be able to modify the table you should remove it from replication, then update the schema, afterwards add it again in the replication. After adding it again you will have to reinitialize the subscriptions to pick up the modified table schema.

answered Mar 21 '16 at 06:55

Mihail Stancescu

4,088
1
16
21

my database replicate from more then 30 clients so if there is any other option that i can try? – pramod maurya Mar 21 '16 at 07:06

Marcello Miorelli · Answer 2 · 2022-09-16T16:05:52.237

I was having this error on my replication

Cannot drop the table 'dbo.repl_application_camp_choice' because it is being used for replication. (Source: MSSQLServer, Error number: 3724)

the first thing I tried - wrongly - is to manually drop the table in the subscriber db.

But the same error was there.

the next thing I tried is this:

USE [ORCASTG]
GO
EXEC sp_msunmarkreplinfo 'dbo.repl_application_camp_choice'

--Msg 3724, Level 16, State 3, Line 5
--Cannot drop the table 'dbo.repl_application_camp_choice' because it is being used for replication.

but it did not work

then I tried this one:

USE [ORCASTG]
GO
DECLARE @subscriptionDB AS sysname  
SET @subscriptionDB = N'ORCASTG'  

USE master  
EXEC sp_removedbreplication @subscriptionDB  
GO  

USE [ORCASTG]
GO
DROP TABLE IF EXISTS [dbo].[repl_application_camp_choice]
GO

and this did the trick

and after running the script above:

I also looked here and here and here.

Cannot drop the table because it is being used for replication

2 Answers2