Deleting databases from SQL server

[jschaddock]

Hello,

I'm using SQL server 7 and am currently cleaning up a development server. I'm trying to delete some old test databases that are no longer required but every time I try and delete them I am given the error message (the database here is called cont):

Cannot drop the database cont because it is published for replication

I understand that this database was at one time replicated but the subscription and publication have since been deleted. I thought there might be a rogue entry in a system table that hadn't been deleted and was still marking the database as being replicated so I have deleted all entries from the distribution database tables using the script below:


Use distribution
delete from MSArticles where publisher_db in ('cont')
GO
delete from MSDistribution_Agents where publisher_db in ('cont')
GO
delete from MSLogreader_Agents where publisher_db in ('cont')
GO
delete from MSPublications where publisher_db in ('cont')
GO
delete from MSPublisher_Databases where publisher_db in ('cont')
GO
delete from MSSnapshot_Agents where publisher_db in ('cont')
GO
delete from MSSubscriptions where publisher_db in ('cont')
GO

I have also checked the sysobjects table in the cont database and made sure that the replinfo column is set to 0 for all objects. However, I still can't drop the database.

Is there something I am missing here?

Any help gratefully received

Thanks

JC

 

[tlbroadbent]

Set the category column in the sysdatabases table in the master DB to zero for the cont DB. You may be able to set the value with sp_replicationdboption. However, I don't know the ramifications of manually removing some portions of the replication info before using the system SP. If the stored procedure doesn't work, you could manually update the sysdatabases table. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[babalou]

try deleting the replication partner(s) added at the bottom.