cant drop publication with sp_droppublication takes hours ..

Hi, i want to drop a publication of one SQL 2005 SP2 server, and the
distribution database is above 40gb (becasue of an sp2 bug), when i
try to run
sp_droppublication it stays running for more than 7 hours with 100% of
disk usage, until i kill the proccess because of production times of
the server.

is there anyway to ignore this step and get the publication droped or
the database in a non publicated state?
can i run sp_replicationdboption if i havent drop the publication
first?

thanks in advance


DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'AdventureWorks';
SET @publication = N'AdvWorksProductTran';

-- Remove a transactional publication.
USE [AdventureWorks]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption
@dbname = @publicationDB,
@optname = N'publish',
@value = N'false';
GO

Yes you can run sp_replicationdboption 'AdventureWorks','publish','false'

However I think you would be best off trying to figure out where the
blocking is occurring and stopping the process which is doing the blocking.

" " wrote:

> Hi, i want to drop a publication of one SQL 2005 SP2 server, and the
> distribution database is above 40gb (becasue of an sp2 bug), when i
> try to run
> sp_droppublication it stays running for more than 7 hours with 100% of
> disk usage, until i kill the proccess because of production times of
> the server.
>
> is there anyway to ignore this step and get the publication droped or
> the database in a non publicated state?
> can i run sp_replicationdboption if i havent drop the publication
> first?
>
> thanks in advance
>
>
> DECLARE @publicationDB AS sysname;
> DECLARE @publication AS sysname;
> SET @publicationDB = N'AdventureWorks';
> SET @publication = N'AdvWorksProductTran';
>
> -- Remove a transactional publication.
> USE [AdventureWorks]
> EXEC sp_droppublication @publication = @publication;
>
> -- Remove replication objects from the database.
> USE [master]
> EXEC sp_replicationdboption
> @dbname = @publicationDB,
> @optname = N'publish',
> @value = N'false';
> GO
>
>