1

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?

3

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

  • but, how the problem will be resolved after we stop the job? – Rajesh Ranjan May 4 '17 at 13:58
  • This will help you find it: select TOP 1 job_id , name from msdb.dbo.sysjobs WHERE category_id IN (SELECT category_id FROM msdb.dbo.syscategories WHERE name = 'REPL-LogReader') – Shellz Sep 23 '17 at 21:06
1
select TOP 1 job_id 
from msdb.dbo.sysjobs 
WHERE category_id IN (SELECT category_id 
                      FROM msdb.dbo.syscategories 
                      WHERE name = 'REPL-LogReader')
  • 1
    Does that actually stop the agent? – mustaccio Mar 3 '17 at 16:16
  • This gets info about the job. Changing line 1 to select TOP 1 * instead of select TOP 1 job_id gives the job name to use in answer from @Nic. – SushiGuy Mar 18 '18 at 20:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.