Search code examples
sql-serversql-server-2008replicationdistributiondatabase-replication

Why SQL Server doesn't allow to remove a Distributor exactly after Configuration?

I Configured a distribution in SQL Server 2008 using both Wizard and T-SQL but after it when I want to remove it Using Wizard (right clicking on Replication and choosing 'Disable Publishing and Distribution...') or executing following command with and without its parameters:

exec sp_dropdistributor @no_checks = 1 -- no new results with @ignore_distributor = 1

this Error would be presented:

Msq 21122, Level 16, State 1, Procedure sp_dropdistributiondb Line 124 Cannot drop the distribution database 'lobloblob' because it is currently in use.

I didn't publish any thing, didn't configure any subscription but gave this error what should I do ?

Solution

  • Try this:

    SELECT spid FROM sys.sysprocesses WHERE dbid = db_id('distribution')

    Kill the spid and try again. Now it should work.