Cannot drop database because of replication

Wow it’s been some time since I blogged. Mainly because of being very busy at work and three weeks of holidays. Next week I’ll be offline again because Pascal, Alex and me will be preparing for the .NET 3.5 Summer Class in September. If you still don’t know what it is, have a look at it and be sure to come. We’ll teach you almost everything that’s .NET 3.5 and you’ll go home knowing what a great addition it is and how to build projects using it. This won’t be a standard training, you will know how to use this stuff in your next projects.

Anyway, I was having some problems with dropping a database. It was the mirror of another one, mirrored by transaction log shipping. For some reason during my holidays and after some tweaking done by my client on more then one database, it failed. After having a look, some properties changed and not for the good. I decided to set it up again from scratch.

After removing transaction log shipping from the root database, I wanted to drop the database on the mirror server. Unfortunately I got the following error:

Cannot drop the database ‘xxx’ because it is being used for replication. (Microsoft SQL Server, Error: 3724)

The jobs were gone, the root database wasn’t attached anymore, but SQL Server 2005 still thought it was being used for replication. I even tried hacking the system tables using the dedicated administrator connection but it just wouldn’t drop, delete, detach or anything.

Solution
The final solution was to create a new database on another server with the same name and create a backup. After copying the backup to the server with problems, I chose to restore the backup. You can’t do this by right-clicking on the database, because it’ll only allow to restore transaction logs. Right-click the server itself and choose to restore it onto the database with problems and select to “overwrite” in the options screen.

It overwrote the database and I could delete it!

Technorati Tags: ,

You may also like...

19 Responses

  1. Rajkishore Gupta says:

    Thanks

    it was very helpful. since few days i was facing this issue.

  2. Richard101 says:

    Worked a treat with SQL2000, I backed-up secondary PUBS to disk then restored, changing the name.

  3. ash says:

    just wasted 3 hours and you helped me with a 2 second fix. Thanks

  4. Lobelt says:

    I faced the problem using SQL2000. Then I took the database offline without problems and I could delete the offline database. The data and log files had to be deleted manualy.

  5. Sosh says:

    This works.. Thanks for your help!

  6. Suresh N says:

    I have used different named database, of the same database having equal db structure worked fine.Hope this helps. You need not have to create a new database!

  7. SQL Administrator says:

    The simple solution for me was to take offline and delete the files. Thanks for the tip.

  8. madhav says:

    right click on local subscriptions and click on subscripton properties in the right pane click on publication databases and unmark all the databases that are being used

  9. WINNT says:

    You can also type:
    use master
    exec sp_removedbreplication YourDatabaseName
    go

  10. Andrew says:

    Thanks I spent 2 hours trying to remove a database that’s replication partner no longer existed for. Your soultion worked. You rock!

  11. Mostafa Darabi says:

    Thanks a lot

  12. Mostafa Darabi says:

    Thanks for your help.

  13. Pranita says:

    Thanx….
    exec sp_removedbreplication works..

  14. Noravia Rodriguez - City Of Miami - Database Manager says:

    Autor : Noravia Rodriguez
    Date : 03/02/2010
    Updates: 03/16/2010
    Purpose: How to unmark a database marked as replicated in SQL Server 2005

    How to unmark a database marked as replicated in SQL Server 2005
    1. Try:
    EXEC sp_removeddbreplication “”DatabaseName”
    GO
    2. Try:
    Take Database OffLine
    Deatch
    Attach Database Back
    3. This tasks need to be done MANUALLY and in the following order:
    i. Drop all existing subscriptions
    ii. Drop all existing pubications
    iii. Create a new publications with the same name
    iv. Drop it again and say “Yes” to delete information on the Distributor (Distribution Database)
    v. Verify Database has been unmark for replication
    1. Go to the Database-Tasks-Detach and ensure that mark has been removed
    OR
    2. Run the following query and ensure “Category”=0 (RECOMMENDED)
    SELECT name,category,* FROM master..sysdatabases

  15. Noravia Rodriguez says:

    SQL Server 2000
    a. Database
    use master
    GO
    exec sp_replicationdboption @dbname = N’dbPOMS’, @optname = N’publish’, @value = N’false’
    GO
    b. Columns
    use master
    GO
    exec sp_replicationdboption @dbname = N’dbPOMS’, @optname = N’publish’, @value = N’false’
    GO

    use [dbPOMS]
    GO
    sp_configure ‘allow updates’, 1
    go
    reconfigure with override
    go
    — For Tables
    UPDATE syscolumns SET colstat = colstat & ~4096 WHERE colstat & 4096 0
    go
    — For columns
    UPDATE sysobjects SET repinfo=0 where repinfo=1
    Go
    sp_configure ‘allow updates’, 0
    go
    reconfigure with override
    go

  16. Mohmmed says:

    Was very helpful, it was a simple solution but no 1 usually tends to think on that side….
    And you did so you are the 1 :)……

    And about above comments removedb replication and all that it wont work if logshipping is broken and morever if ur missing the *.tuf file……

    Please read the post before you all answer and make people go in wrong directions……

    And also its next to impossible at times to update sys tables even with DAC connections…..

    Anyways Nice post ……

    Thanks

  17. Loke says:

    It woked just perfect!!!!

  18. Junaid Siddiqui says:

    Please use this script to remove table value from sys.tables:

    — Removes Replication Flag for all Tables in the Database
    — using sp_MSunmarkreplinfo
    SET NOCOUNT ON
    DECLARE @tablename NVARCHAR(128)
    DECLARE @RC INT
    DECLARE curTable CURSOR FOR
    SELECT [name] AS tbl
    FROM sys.tables
    OPEN curTable
    FETCH NEXT FROM curTable
    INTO @tablename
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXECUTE @RC = dbo.sp_MSunmarkreplinfo @tablename
    FETCH NEXT FROM curTable
    INTO @tablename
    END
    CLOSE curTable
    DEALLOCATE curTable
    GO

  19. Jeremy says:

    it just needs to be online to remove, even with replication confusion, in sql 10.5:

    restore database [database name]

    then drop it. This will also clean up the false reports of replication under Local Publications on your secondary/ies.

Click on a tab to select how you'd like to leave your comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.