Sql-server – How to stop Log Reader Agent from SQL/Command Line

replicationsql-server-2012

Say I've got replication on a SQL Server 2012 box set up. Everything is working as expected, and the Log Reader Agent is running.

Now, say that I need to remove all traces of this publication/subscription from the database programmatically. Previously, I had this working with SQL 2008R2.

The steps I was taking are –

  1. Drop subscription at source database (sp_dropsubscription)
  2. Drop subscription at target database (sp_droppullsubscription)
  3. Drop publisher (sp_droppublication)
  4. Remove everything else (sp_replicationdboption)
  5. Remove databases

This used to work perfectly, but since upgrading to SQL 2012, when I try to drop the publisher, I get the following error –

System.Data.SqlClient.SqlException (0x80131904): 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.

Now, I think I understand what's going on here, but I don't know how to prevent it.

What's the best way of stopping the Log Reader Agent, either via command line, or via SQL?

Best Answer

The Log Reader Agent is typically run via a SQL Agent job (unless you have some funky process that kicks it off from the command line). Given this you should be able to just query msdb.sysjobs for the job, and execute msdb.dbo.sp_stop_job @job_name = (your agent job).

Related Question