Stopping SQL Replication Agents

[NickCorlett]

Is anyone aware of a way to stop the replication agents using T/SQL. Obviously this can be done, by right-clicking the relevant agent in Enterprise Manager and selecting "stop sychronizing". I am hoping there is an equivalent way to do this through code?

Cheers

 

[gavinhuet]

Stop Replication:
You will need to replace the [LogReaderName] and [DistributionName] with the correct string values for your environment.

EXEC msdb.dbo.sp_stop_job @job_name = [LogReaderName]
EXEC msdb.dbo.sp_stop_job @job_name = [DistributionName]

 

[NickCorlett]

Gavin,

Thanks for the reply, however this code will stop the agents if they are running, however I wish to stop the agents from running in the first place. Effectively I want to reproduce the effect of right-clicking on the agent in the under the replication monitor and selecting 'Stop synchronizing'.

Cheers

 

[JayKusch]

OK ... how about ...

EXEC msdb.dbo.sp_update_job @job_name = [LogReaderName], @Enable=0
EXEC msdb.dbo.sp_update_job @job_name = [DistributionName], @Enable=0

Thanks

J. Kusch

 

[NickCorlett]

Thanks chaps,

I have tried the second option and whilst this disables the Agent, when viewed through the SQL Server Agent, which is related to the relevant replication function it doesn't actually stop the replication from working, great eh!

I identified the agent job by looking at the agent properties on the log reader replication task, I ran the sp_update_job command with the LogReaderName and the enable set to 0, the T/SQL executed beautifully and the log reader agent showed as being disabled through the SQL Server Agent, however the log reader task viewed through the replication monitor still showed as active, when I updated the data it was replicated to destination database???

In an effort to find a solution, I have attempted to identify which table is changing when I stop the synchronization through the replication monitor but as yet have not come across an answer.

Stumped!

 

[snag56]

You probably need to do the sp_stop_job,
as well as the sp_update_job.