SQL Server Performance Forum – Threads Archive
delete replication db
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
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
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
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
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
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
Thanks, I’ll try that. I used Transactional Replication
Master–>sysdatabases, Check whether there any db’s value of "category" is ‘1’(Publication) Try to set it to ‘0’.
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
]]>