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:
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
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
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
Post a Comment