How to stop Log Reader Agent from SQL/Command Line

Question

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?

Answer 1

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).

Answer 2

select TOP 1 job_id 
from msdb.dbo.sysjobs 
WHERE category_id IN (SELECT category_id 
                      FROM msdb.dbo.syscategories 
                      WHERE name = 'REPL-LogReader')