SQL Server Performance

delete replication db

Discussion in 'Performance Tuning for SQL Server Replication' started by boutwater, Nov 5, 2003.

  1. boutwater Member

    Hi,
    I have a db that I used to use for replication. I don't use replication anymore, and I don't see any publishers or subscriptions, but when I try to delete this db, it says, "can't delete, this db is being used for replication". Does anyone know what I need to do to be able to delete this db. Thanks for your help,

    Ben
  2. satya Moderator

    Use sp_removedbreplication to remove the replication which is executed at the Publisher on the publication database or at the Subscriber, on the subscription database.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. boutwater Member

    The db "distribution" is the one I can't delete. I tried sp_removedbreplication distribution in query analyzer, and it completed successfully, but it still won't let me delete the db. Is there anything else I need to do? Thanks for your help,
    Ben
  4. satya Moderator

    That should work and reported successful and try also with sp_dropdistributiondb.
    After running run SP_HELPDB to see the list of databases.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. boutwater Member

    This is the error that I received this time. Server: Msg 14120, Level 16, State 1, Procedure sp_dropdistributiondb, Line 49
    Could not drop the distribution database 'distribution'. This distributor database is associated with a Publisher. Thanks,
    ben
  6. satya Moderator

    This is getting rigid, and you may need to go unconventional method by restoring any user database abckup on the same name as the distributor and then drop the dist.database.
    (It worked for me earlier)

    BTW what kind of replication you'd used on these databases?


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. boutwater Member

    Thanks, I'll try that. I used Transactional Replication
  8. ufobox New Member


    Master-->sysdatabases,

    Check whether there any db's value of "category" is '1'(Publication)

    Try to set it to '0'.
  9. satya Moderator

    I suggest to refrain updating system databases/tables directly when you've chance of using supplied SPs to deal with the issue. Its not supported and risk of crashing the whole system.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page