cant drop publication with sp_droppublication takes hours to run

[m***@gmail.com]

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

[Hilary Cotter]

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.

Post by m***@gmail.com
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
-- Remove a transactional publication.
USE [AdventureWorks]
-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption
@dbname = @publicationDB,
@optname = N'publish',
@value = N'false';
GO

[m***@gmail.com]

Thanks Hilary ill try that. however i think that the problem is not a
bloking but the size of the Log and the distribtion database, both
above 40gb because of a bug in SP2
regards!

Post by Hilary Cotter
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.

Hi, i want todropa 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 than7hourswith 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 haventdropthe publication
first?
thanks in advance
-- Remove a transactional publication.
USE [AdventureWorks]
-- Removereplicationobjects from the database.
USE [master]
EXEC sp_replicationdboption
GO

[sql_noob]

Post by m***@gmail.com
Thanks Hilary ill try that. however i think that the problem is not a
bloking but the size of the Log and the distribtion database, both
above 40gb because of  a bug in SP2
regards!

Post by Hilary Cotter
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.

Hi, i want todropa 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 than7hourswith 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 haventdropthe publication
first?
thanks in advance
-- Remove a transactional publication.
USE [AdventureWorks]
-- Removereplicationobjects from the database.
USE [master]
EXEC sp_replicationdboption
GO- Hide quoted text -

- Show quoted text -

do you see blocking anywhere when you do this? had this a few times
and i think every time someone was running something and it blocked my
drop publication