SQLServerCentral / SQL Server 7,2000 / Replication / Cannot drop the database XXX because it is being used for replication. / Latest Posts

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

arbcoll — Sat, 12 Jan 2013 10:49:15 GMT

Worked like a charm with "Offline" mode!I had a interesting situation: my database was in Warm Standby mode, and I had a [b].tuf [/b]file deleted. The releiving thing was that I didn't needed this database anymore, but I couldn't delete it because of remained publications from primary log shipped database. The publication couldn't be deleted, of course, because the DB was read only. The DB could not be recovered because of missing tuf file.The only solution that worked was putting the DB to offline mode, and then deleting manually the data and log files. Also the publication disappeared.Thanks!

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

Rodrigo Moraes-286573 — Tue, 29 Mar 2011 15:09:48 GMT

Hello allI just find another simplest way to workaround it.Expand the server in SSMS;Right-click in Replication folder;Click "Disable Publishing and Distribution"...This action should delete all files, jobs and distribution database.It worked for me.Hope this helpsRegards

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

rfonseca — Mon, 19 Apr 2010 11:35:54 GMT

Taking it offline worked. Thanks for the post!

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

SA-1 — Tue, 15 Sep 2009 07:13:16 GMT

Go back and drop the data / log files since dropping an offline database doesn't delete these files.

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

rafael.algora — Fri, 28 Aug 2009 02:06:36 GMT

You must use the next: use masterGOsp_configure 'allow updates', 1GORECONFIGURE WITH OVERRIDEGOto allow updates over system databases When you finish the operation, yo must run the next:use masterGOsp_configure 'allow updates', 0GORECONFIGURE WITH OVERRIDEGO

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

lyhe — Thu, 09 Apr 2009 11:54:57 GMT

Taking the database offline works like a charm. Thanks raja

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

phillip_latham — Wed, 25 Feb 2009 07:03:16 GMT

Hello, you need to run the following:sp_configure "allow updates", 1goReconfigure with overridegoAfter you execute the failing command:sp_configure "allow updates", 0 goReconfigure with overridegoHope this helps. Todd

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

achir.lui — Thu, 08 Jan 2009 03:28:16 GMT

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.

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

GrahamO-735217 — Sun, 27 Jul 2008 20:34:44 GMT

Is this any help?[url]http://www.dbforums.com/showthread.php?t=708918[/url]

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

shah797 — Thu, 17 Jul 2008 14:11:03 GMT

This will let you update. This is dangerous so make sure to run allow updates 0sp_configure 'allow updates',1GORECONFIGURE WITH OVERRIDEGOupdate sysdatabases set category=0 where name = 'xxx'sp_configure 'allow updates',0GORECONFIGURE WITH OVERRIDEGO

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

ryang9639@yahoo.com — Fri, 27 Jun 2008 14:41:57 GMT

Hi,Try to setup Server Seeting-> Server behaiver->allow modifications to be made directly to system catalogsRichard Yang

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

Jerrin — Sun, 10 Feb 2008 22:24:26 GMT

write this and then try to update the system tablesp_configure 'allow updates',1reconfigure with overrideThen after the update execute thissp_configure 'allow updates',0reconfigure with override

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

casia — Thu, 07 Feb 2008 03:08:05 GMT

HiI have the same problem I tried the following update sysdatabases set Category = 0 where dbid = 13but I get the following errorMsg 259, Level 16, State 1, Line 1Ad hoc updates to system catalogs are not allowed.

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

JeremyE — Fri, 03 Aug 2007 17:01:00 GMT

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

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

Raja Abubaker-455153 — Thu, 02 Aug 2007 00:23:00 GMT

take the datbase offline and then drop it.

raja

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

e.francone — Fri, 28 May 2004 09:37:00 GMT

Nonenone!! Thanks a lot!!!

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

Nonenone-147172 — Fri, 28 May 2004 07:00:00 GMT

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.

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

majbou — Wed, 12 May 2004 04:41:00 GMT

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

Maj.

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

Christopher Klein — Tue, 11 May 2004 09:33:00 GMT

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 subscriptionsexec sp_dropmergesubscription @publication = N'<publication>''

'Drop the publicationexec sp_dropmergepublication @publication = N'<publication>'

'Set the DB to not be for replicationexe 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

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

e.francone — Tue, 11 May 2004 09:23:00 GMT

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

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

Rodrigo Acosta — Tue, 11 May 2004 06:43:00 GMT

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.

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

e.francone — Tue, 11 May 2004 04:46:00 GMT

When I try to drop the XXX database SQLServer returns me this error message:

Server: Msg 3724, Level 16, State 3, Line 1Cannot 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'goexec sp_dboption 'XXX','published',falsegoexec sp_dboption 'XXX','merge publish',falsego

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