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

  • e.francone

    SSChasing Mays

    Points: 650

    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

     

  • Rodrigo Acosta

    SSC-Insane

    Points: 21261

    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

    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: 2588

    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

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

    Maj.

  • Nonenone-147172

    SSC Enthusiast

    Points: 127

    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

    Nonenone!! Thanks a lot!!!

  • Raja Abubaker-455153

    SSC Enthusiast

    Points: 184

    take the datbase offline and then drop it.

     

    raja

  • JeremyE

    SSCoach

    Points: 15119

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

  • casia

    SSC Enthusiast

    Points: 141

    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

    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

    Hi,

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

    Richard Yang

  • shah797

    SSC Enthusiast

    Points: 164

    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

    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

    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.

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

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