Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Cannot drop the database XXX because it is being used for replication. Expand / Collapse
Author
Message
Posted Tuesday, May 11, 2004 4:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 7:09 AM
Points: 12, Visits: 189

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

 




Post #115333
Posted Tuesday, May 11, 2004 6:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 4:43 PM
Points: 1,581, Visits: 6

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.




Post #115349
Posted Tuesday, May 11, 2004 9:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 7:09 AM
Points: 12, Visits: 189

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

 

 




Post #115403
Posted Tuesday, May 11, 2004 9:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 19, 2013 7:40 AM
Points: 150, Visits: 221

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

 




Post #115406
Posted Wednesday, May 12, 2004 4:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 11, 2005 11:48 AM
Points: 5, Visits: 1

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

Maj.




Post #115538
Posted Friday, May 28, 2004 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 28, 2004 6:54 AM
Points: 1, Visits: 1

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.

Post #118232
Posted Friday, May 28, 2004 9:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 7:09 AM
Points: 12, Visits: 189
Nonenone!! Thanks a lot!!!


Post #118298
Posted Thursday, August 2, 2007 12:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 14, 2013 1:14 AM
Points: 2, Visits: 28

take the datbase offline and then drop it.

 

raja

Post #387255
Posted Friday, August 3, 2007 5:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 2,893, Visits: 3,100
Set Category for the database to 0 in master.dbo.sysdatabases. Then you can delete it.
Post #387960
Posted Thursday, February 7, 2008 3:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 5, 2009 10:50 PM
Points: 1, Visits: 5
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.
Post #452621
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse