ReplTip — Only one Log Reader can connect message when dropping a Publication

ReplTip — Only one Log Reader can connect message when dropping a Publication
by Taiyeb Zakir

Sometimes when you try to drop a Transactional Publication using the GUI, you get the following error:

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. 

This happens because the Log reader agent Job was not stopped before the Publication is dropped. When we drop the Publication, we internally call sp_replcmds and there can be only session running that command against the Published database.  To avoid this message, STOP the publication’s Log Reader Agent, via SQL Server Agent jobs, Replication Monitor, or by right-clicking the Publication, select "View Log Reader Agent Status", then "Stop",