Cannot drop the database XXX because it is being used for replication.

Question

Cannot drop the database XXX because it is being used for replication.

e.francone

SSChasing Mays

Points: 650
More actions
May 11, 2004 at 4:46 am

#85512

When I try to drop the XXX database SQLServer returns me this error message:
Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database XXX because it is being used for replication.
I've also tried to execute the following code but nothing has changed.
exec sp_removedbreplication 'XXX'
go
exec sp_dboption 'XXX','published',false
go
exec sp_dboption 'XXX','merge publish',false
go
The XXX DB is a restore of "Distribution" database of a Production Server that is a publication server!
Someone can help me?
Thanks for all suggestions!!!
Frank

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

Please, run sp_helpreplicationdb to check with databases are marked for replication.

Distribution is for replication but should not be marked for replication, so you should be able to restore it at any time.

e.francone SSChasing Mays Points: 650 More actions · Answer 2

I want to drop XXX Db that contains a restore of "distribution" database of a production server. I've tried to use sp_helpreplicationdb but I'm not be able to drop XXX DB.

Can you give me some more advices?

Thanks.

Frank

Christopher Klein SSCrazy Points: 2648 More actions · Answer 3

what type of replication?

What I've had to run to make sure my scripts are starting clean for merge replication are as follows:

'Drop any subscriptions

exec sp_dropmergesubscription @publication = N'<publication>''

'Drop the publication

exec sp_dropmergepublication @publication = N'<publication>'

'Set the DB to not be for replication

exe sp_replicationdboption @dbname= N'<db>' @optname=N'merge publish' @value = 'FALSE'

If you're using trans rep I believe the commands are similar.

Also if you go into EM and right-click the publication you can choose 'generate script' which will give you the option to see the script for creating or droping the publications.

regards,

Chris

majbou SSC Enthusiast Points: 155 More actions · Answer 4

although it's not recommended, sometimes you 'll have to update the REPLINFO column in SYSOBJECTS.

Maj.

Nonenone-147172 SSC Enthusiast Points: 127 More actions · Answer 5

You can restore another database backup, that don`t have replications configured, over the database that you want to delete(with the "force restore over existing database" option marked). Doing this you will overwrite the sysobjects lines that saves the replication parameters and the SQL Server will alow you to delete the database.

I hope It`s easy to understand..

Mila.

e.francone SSChasing Mays Points: 650 More actions · Answer 6

e.francone

SSChasing Mays

Points: 650

May 28, 2004 at 9:37 am

#508235

Nonenone!! Thanks a lot!!!

Raja Abubaker-455153 SSC Veteran Points: 214 More actions · Answer 7

Raja Abubaker-455153

SSC Veteran

Points: 214

August 2, 2007 at 12:23 am

#724071

take the datbase offline and then drop it.

raja

JeremyE SSCoach Points: 16196 More actions · Answer 8

Set Category for the database to 0 in master.dbo.sysdatabases. Then you can delete it.

casia SSC Enthusiast Points: 141 More actions · Answer 9

Hi

I have the same problem

I tried the following

update sysdatabases

set Category = 0

where dbid = 13

but I get the following error

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

Jerrin SSC Veteran Points: 254 More actions · Answer 10

write this and then try to update the system table

sp_configure 'allow updates',1

reconfigure with override

Then after the update execute this

sp_configure 'allow updates',0

reconfigure with override

ryang9639@yahoo.com Valued Member Points: 60 More actions · Answer 11

Hi,

Try to setup Server Seeting-> Server behaiver->allow modifications to be made directly to system catalogs

Richard Yang

shah797 SSC Enthusiast Points: 164 More actions · Answer 12

This will let you update. This is dangerous so make sure to run allow updates 0

sp_configure 'allow updates',1

GO

RECONFIGURE WITH OVERRIDE

GO

update sysdatabases set category=0 where name = 'xxx'

sp_configure 'allow updates',0

GO

RECONFIGURE WITH OVERRIDE

GO

GrahamO-735217 SSC Enthusiast Points: 173 More actions · Answer 13

Is this any help?

http://www.dbforums.com/showthread.php?t=708918

Graham Okely B App Sc
Senior Database Administrator

achir.lui Valued Member Points: 63 More actions · Answer 14

I got the same problem.I tried to solved it.Then I checked "Replication" item,found that I set up the Publications rule for the detached database.So you can check the Publication and Subscriptions.