SQL SERVER – FIX – ERROR : Cannot drop the database because it is being used for replication. (Microsoft SQL Server, Error: 3724)

I have set up replication at many different organization. One error I quite commonly face is after I have removed replication I can not remove database. When I try to remove the database it gives me following error.

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

Fix/Workaround/Solution:

The solution is very simple. Create the empty database with the same name on another server/instance first. Take full back of the same and forced restore over this database.

Do let me know if you have any better idea or suggestion.

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

23 thoughts on “SQL SERVER – FIX – ERROR : Cannot drop the database because it is being used for replication. (Microsoft SQL Server, Error: 3724)

  1. Believe I had this problem a year ago and the fix was to manually remove the subscriptions. There seemed to be entries left after the SSMS style deletion. Used sp_droppullsubscription on the subscriber and then to follow up cleaning on the publication sp_dropsubscription

    I’ll test that out maybe later. This is coming from poor old guy memory ;-)

  2. Hello!

    This is a nice, new way to trick the SQL Server into “forgetting” that a database is marked for replication – and is different than the one I am used to using; which has aroused my curiosity.

    The databases in the system that I sustain are typically configured in a replicating configuration with one publisher/distributor (our product does not support isolated publisher/distributor configuration) and n number of subscribers (highest count in production is 25). The high-level sequence that we typically follow during a server move and/or server rebuild is:

    1. Unregister the subscribers one by one
    2. Uninstall the distributor
    3. Remove the distribution DB
    4. Uninstall publisher
    5. Finally run sp_removedbreplication on all servers to remove any meta-data that the server might have remembered
    6. Now, backup/remove the databases

    My question now is: what is the difference (internally) between sp_removedbreplication and the method described in this article?

    Have a great week-end and an exciting week ahead!

    Thanks,
    Nakul.

  3. Hi pinal,
    You can try running this sp after removing replication, once the proc is run, try dropping the database.
    EXEC sp_replicationdboption @dbname = N’Test_db’, @optname = N’publish’, @value = N’FALSE’ — Remove the replication bit before detaching
    GO

    Thank you

  4. For remove all objects created by any replication…….

    DECLARE @name nvarchar(150), @Tabla Varchar(100), @Instruccion Varchar(250)
    DECLARE Objs_cursor CURSOR FOR
    SELECT T.Name, O.Name
    FROM sys.sysobjects O JOIN sys.tables T On T.object_id=O.parent_obj
    WHERE SubString(O.Name,1,7)=’MSmerge’ or SubString(O.Name,1,7)=’repl_id’
    order by O.name
    OPEN Objs_cursor

    FETCH NEXT FROM Objs_cursor
    INTO @Tabla, @name

    WHILE @@FETCH_STATUS = 0
    BEGIN

    Set @Instruccion=’ALTER TABLE ['+@Tabla+'] DROP CONSTRAINT ‘+ rtrim(@Name)
    Print @Instruccion
    Print ‘GO’

    FETCH NEXT FROM Objs_cursor
    INTO @Tabla, @name
    END
    CLOSE Objs_cursor
    DEALLOCATE Objs_cursor

    DECLARE @name nvarchar(150), @Tabla Varchar(100), @Instruccion Varchar(250)
    DECLARE Objs_cursor CURSOR FOR
    SELECT T.Name, O.Name
    FROM sys.sysindexes O JOIN sys.tables T On T.object_id=O.Id
    WHERE SubString(O.Name,1,7)=’MSmerge’
    order by T.Name, O.name

    OPEN Objs_cursor

    FETCH NEXT FROM Objs_cursor
    INTO @Tabla, @name

    WHILE @@FETCH_STATUS = 0
    BEGIN

    Set @Instruccion=’DROP INDEX ['+ rtrim(@Name)+'] ON [dbo].['+rtrim(@Tabla)+'] WITH ( ONLINE = OFF )’
    Print @Instruccion
    Print ‘GO’

    FETCH NEXT FROM Objs_cursor
    INTO @Tabla, @name
    END
    CLOSE Objs_cursor
    DEALLOCATE Objs_cursor

    DECLARE @name nvarchar(150), @Tabla Varchar(100), @Instruccion Varchar(250)
    DECLARE Objs_cursor CURSOR FOR
    SELECT Name B, Name A
    FROM sys.sysobjects
    WHERE SubString(Name,1,7)=’MSmerge’
    order by name

    OPEN Objs_cursor

    FETCH NEXT FROM Objs_cursor
    INTO @Tabla, @name

    WHILE @@FETCH_STATUS = 0
    BEGIN

    Set @Instruccion=’Drop View ['+ rtrim(@Name)+']‘
    Print @Instruccion
    Print ‘GO’

    FETCH NEXT FROM Objs_cursor
    INTO @Tabla, @name
    END
    CLOSE Objs_cursor
    DEALLOCATE Objs_cursor

    DECLARE @name nvarchar(150), @Tabla Varchar(100), @Instruccion Varchar(250)
    DECLARE Objs_cursor CURSOR FOR

    SELECT Name
    FROM sys.tables
    order by name

    OPEN Objs_cursor

    FETCH NEXT FROM Objs_cursor
    INTO @name

    WHILE @@FETCH_STATUS = 0
    BEGIN

    Set @Instruccion=’ALTER TABLE['+ rtrim(@Name)+'] DROP COLUMN rowguid’
    Print @Instruccion
    Print ‘GO’

    FETCH NEXT FROM Objs_cursor
    INTO @name
    END
    CLOSE Objs_cursor
    DEALLOCATE Objs_cursor

    And for remove replication tables:

    SET NOCOUNT ON
    DECLARE @tablename NVARCHAR(128)
    DECLARE @RC INT
    DECLARE curTable CURSOR FOR
    SELECT [name] AS tbl
    FROM sys.objects
    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

    Finaly for permanently remove replication:

    exec sp_removedbreplication ‘DB’

    Send your comments…. [email removed]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s