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

Question

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 ?

Answer 1

Try this:

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

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

Answer 2

I used the following scripts:

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

and found that the session_id of current session (which contains the distribution configuration script) doesn't allow to disable distribution so i suggest this script to kill running spid to drop distribution:

use [master]
declare @spid varchar(10)
select @spid=spid from sys.sysprocesses where dbid = DB_ID('distribution')

while @@ROWCOUNTS <> 0
    exec ('KILL ' + @spid)

exec sp_dropdistributor @no_checks = 1

Answer 3

My guess would be that the distribution cleanup job is causing the problem. But, to check, prepare to execute the sp_dropdistributor in one window in SSMS and note the session_id of the window. In a second, prepare to run select session_id from sys.dm_os_waiting_tasks where blocked_session_id = <spid from window 1>. Back in window 1, run the proc and then switch back to window 2 and run the select. it'll tell you the session_ids of the sessions blocking the drop of the database.