Force Drop Publisher from Distributor

The nuclear option for replication cleanup. When a publisher can no longer be unconfigured through the normal means - usually because the publisher server is gone or unreachable - this script removes the leftover configuration directly from the distributor's msdb. It assumes all publications and subscriptions have already been dropped, and you will still need to manually delete any SQL Agent jobs associated with the publication. The destructive statements (offline/drop of the distribution database, deletes from MSdistributiondbs and MSdistpublishers) are written with spaced-out letters so they are not accidentally executed before you have verified the two SELECTs.

USE msdb
GO

/*
	This script is designed to drop a publisher and it's distribution database from
		a SQL Server 2008r2 Rqplication Distributor as a last resort when
		the publisher can't be "unconfigured" by other means. 

	This script assumes that all publications and subscriptions have already been dropped.

	You will need to manually delete any SQL Agent jobs associated with
		the publication and its subscribers.
*/

--a-l-t-e-r database <distribution_database_name> set offline
--d-r-o-p database <distribution_database_name>

select * from dbo.MSdistributiondbs where name = '<distribution_database_name>'
select * from dbo.MSdistpublishers where name = '<publisher_name>'

--d-e-l-e-t-e from dbo.MSdistributiondbs where name = '<distribution_database_name>'
--d-e-l-e-t-e from dbo.MSdistpublishers where name = '<publisher_name>'