0

Whenever I have to drop a publication I follow the steps below, ending by a call to sp_droppublication when I get the error below two times.

The publications seems to have been dropped successfully but I am intrigued with the two times.

Would I be better off stopping any log-reading job for this publication before dropping it?

-- Dropping the transactional subscriptions
use [AuPair_DW]
exec sp_dropsubscription 
 @publication = N'AuPair_DW_Repl_Pub', 
 @subscriber = N'SB1', 
 @destination_db = N'ctdatawarehouse', 
 @article = N'all'
GO

-- Dropping the transactional articles
use [AuPair_DW]
exec sp_dropsubscription 
 @publication = N'AuPair_DW_Repl_Pub', 
 @article = N'rpt_Application_BreakDown', 
 @subscriber = N'all', 
 @destination_db = N'all'
GO
use [AuPair_DW]
exec sp_droparticle 
 @publication = N'AuPair_DW_Repl_Pub', 
 @article = N'rpt_Application_BreakDown', 
 @force_invalidate_snapshot = 1
GO

-- Dropping the transactional publication
use [AuPair_DW]
exec sp_droppublication 
 @publication = N'AuPair_DW_Repl_Pub'
GO

--Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1 [Batch Start Line 24]
--Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.

--Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1 [Batch Start Line 24]
--Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.