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

sql-server sql-server-2008 replication distribution database-replication
Mohammad Sheykholeslam picture Mohammad Sheykholeslam · Apr 17, 2012 · Viewed 17.3k times · Source

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

Brandon Williams picture Brandon Williams · Apr 18, 2012

Try this:

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

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

Related questions

How do I check SQL replication status via T-SQL?

I want to be able to check the status of a publication and subscription in SQL Server 2008 T-SQL. I want to be able to determine if its okay, when was the last successful, sync, etc.. Is this possible?

Read More
Guide for Replication in MS SQL Server 2008

I wonder if anyone know/have a good guide for Replication in MS SQL Server 2008? I want to set up a Replication between two different computers that run MS SQL Server 2008, the master runs on Windows Vista, and the slave …

Read More
Pause SQL server replication temporarily

We have a transactional replication setup using 3 SQL Servers, 1st as publisher, 2nd as distributor, and 3rd as subscriber. We have an activity to change the location of the replicated DB (subscriber) using de-attach and attach method. During this activity, …

Read More