Monday, September 15, 2008

Issues dropping distribution database

From time to time I came accross when using EM will not completely disable replication leaving distribution database. When you try to drop this database you will get the following error.
Cannot drop the distribution database 'distribution' because it is currently in use. The way to get around is to follow the following steps


USE master

GO
EXEC sp_configure 'allow updates', '1'
RECONFIGURE with override
GO
update master.dbo.sysdatabases
set category = 0
where dbid =

go
EXEC sp_configure 'allow updates', '0'
RECONFIGURE with override

GO
drop database


3 comments:

granadaCoder said...

I still received a
"Ad hoc updates to system catalogs are not allowed"
error with the code above.

I have Sql Server 2005 (Developer Edition is what I'm on now).
Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)

I see the logic of your process clearly, but I still got the error, fyi.

Here is my slightly modified code to fill in the gaps.
It assumes the db name is (of course) "distribution".

-----START TSQL

USE master

GO
EXEC sp_configure 'allow updates', '1'
RECONFIGURE with override
GO


DECLARE @db_id int;
SET @db_id = DB_ID(N'distribution');
print @db_id


update master.dbo.sysdatabases
set category = 0
where dbid = @db_id

go
EXEC sp_configure 'allow updates', '0'
RECONFIGURE with override

GO
drop database distribution

granadaCoder said...

I was able to drop the distribution database by stopping/restarting the SQLSERVER Service.

I used the code below:
Note, it assumes the dbname is 'distribution' (of course)...

USE master
-- Delete the distribution database.
EXEC sp_dropdistributiondb 'distribution';
GO






USE master
-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO

Unknown said...

The previous code was purely used for SQL server 2000. Your code does work on SQL server 2005 and 2008. Replication have been revamped in both SQL server 2005 and 2008 as compared to previous edition. All the issues I used to have in SQL server 2000 doesn't seem to appear in both 2005 and 2008