Sql-server – How to restart the distributor agent of transactional replication

replicationsql servertransactional-replication

After troubleshooting query time out error I would like to restart my distributor agent.

So I go to my distributor server, in my distributor DB (called distribution_SA) I run the following query:

select s.name as [servername],
       d.name,
       d.publication
      ,s.*
   --d.publication, d.local_job, d.publisher_db, d.job_id, s.name
  from distribution_SA.dbo.MSdistribution_agents d
      inner join master.sys.servers s on d.subscriber_id = s.server_id
 --where d.publication = @publisher
order by d.publication, s.name

this gives me the following result (I had to leave the server names on, apologies):
What I want to achieve is the list of jobs that I need to restart in order for the changes that I did on my distributor agent profile to take place.

enter image description here

The problem is that I haven't been able to find these jobs. Where are them?

a) on the distributor

b) on the publisher

c) on the subscriber

Is there any better way of restarting the distributor agent?

I would prefer a T-SQL solution, when possible.

Best Answer

I found a simple solution here.

Distribution Agent - Start /Stop in T-SQL
-- To STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,
 @subscriber, @subscriber_db

--To START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication,
 @subscriber, @subscriber_db

My complete implementation, working on my test environment is below. I have left everything there, select queries and procedures, to remind myself, where to go to get the data to be used as parameters to the sp_MSstartdistribution_agent and sp_MSstopdistribution_agent stored procedures.

--==================================================================================
-- script to start-stop the distribution agent job for a publication
-- marcelo miorelli
-- 12-mar-2015
--==================================================================================




--http://www.msqlserver.net/2013/01/distribution-agent-start-stop-in-t-sql.html

--==================================================================================
--Script to run on Publisher database
--==================================================================================

PRINT @@SERVERNAME
--SQLBOCSSLON1 -- test 


use Bocss2
GO

-- get the distributor
sp_helpDistributor
--SQLDISTLON1 -- distributor server

--==================================================================================
-- Script to run on Distribution database
--==================================================================================

PRINT @@SERVERNAME
--SQLDISTLON1 -- test 

-- distributor database in this instance is called "distribution"
USE Distribution 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

select * from dbo.MSrepl_errors 
ORDER BY [TIME] DESC



-- Get the publication name based on article 
SELECT DISTINCT  
srv.srvname publication_server  
, a.publisher_db 
, p.publication publication_name 
, a.article 
, a.destination_object 
, ss.srvname subscription_server 
, s.subscriber_db 
, da.name AS distribution_agent_job_name 
FROM MSArticles a  
JOIN MSpublications p ON a.publication_id = p.publication_id 
JOIN MSsubscriptions s ON p.publication_id = s.publication_id 
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid 
JOIN master..sysservers srv ON srv.srvid = p.publisher_id 
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id  
     AND da.subscriber_id = s.subscriber_id 
--WHERE da.name = 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'
ORDER BY 1,2,3  

--==================================================================================
--Script to run on Publisher database
--==================================================================================

PRINT @@SERVERNAME
--SQLBOCSSLON1 -- test 


-- Run from Publisher Database  
-- Get information for all databases 
DECLARE @Detail CHAR(1) 
SET @Detail = 'Y' 

BEGIN TRY
DROP TABLE #tmp_replcationInfo
END TRY
BEGIN CATCH
END CATCH


CREATE TABLE #tmp_replcationInfo ( 
PublisherDB VARCHAR(128),  
PublisherName VARCHAR(128), 
TableName VARCHAR(128), 
SubscriberServerName VARCHAR(128), 
) 
EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 
insert into #tmp_replcationInfo 
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
' 
IF @Detail = 'Y' 
   SELECT * FROM #tmp_replcationInfo 
ELSE 
SELECT DISTINCT  
PublisherDB 
,PublisherName 
,SubscriberServerName  
FROM #tmp_replcationInfo 

-------------------------------------------------------------------------     
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
,SP.*
,SA.*
,S.*
,SRV.*

from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
-------------------------------------------------------------------------



--==================================================================================


--Msg 21482, Level 16, State 1, Procedure sp_MSstopdistribution_agent, Line 22
--sp_MSstopdistribution_agent can only be executed in the "distribution" database.

--==================================================================================

PRINT @@SERVERNAME
--SQLDISTLON1 -- test 

 --Distribution Agent - Start /Stop in T-SQL
    -- To STOP the Distribution Agent:
    sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,
     @subscriber, @subscriber_db



--Msg 21846, Level 16, State 1, Procedure sp_MSdistributionagentjobcontrol, Line 60
--Cannot find a distribution agent job for the specified transactional or snapshot push subscription.

use distribution
go

sp_helpdistpublisher

select msda.*
       ,sjv.*
       ,sjs.*
      from dbo.MSdistribution_agents as msda
inner join msdb.dbo.sysjobs_view as sjv
        on msda.job_id = sjv.job_id
inner join msdb.dbo.sysjobsteps as sjs
        on sjv.job_id = sjs.job_id


    -- To STOP the Distribution Agent:
    sp_MSstopdistribution_agent 'SQLBOCSSLON1', 'Bocss2Archive', 'Bocss2ArchiveMergedAccounts',
     'REPLON1', 'Bocss2Archive'

    -- To check whether the Distribution Agent is running:
    sp_runningJobs 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'          

    --To START the Distribution Agent:
    sp_MSstartdistribution_agent @publisher = 'SQLBOCSSLON1',
                                 @publisher_db = 'Bocss2Archive', 
                                 @publication = 'Bocss2ArchiveMergedAccounts',
                                 @subscriber = 'REPLON1', 
                                 @subscriber_db = 'Bocss2Archive'


    -- To check whether the Distribution Agent is running:
    sp_runningJobs 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'
Related Question