Queries to build START and STOP commands for PUSH Distribution Agents


Chris Skorlinski, Microsoft SQL Server Escalation Services

I had need other day to STOP and START all Distribution Push Agent.  Using the syssubscriptions and syspublications I can easily build list of SQL commands to either stop or start all the Distribution Push Agents.  With output to text, I can copy and execute from SQL Agent Job, or directly in SQL Management Studio Query.

 

-- query to build "STOP AGENT" commands for all Push Distribution Agents
-- set "output to text", then copy and past results in query window to execute

select distinct 'exec sys.sp_stoppushsubscription_agent @publication = N''' + pub.name + ''', @subscriber = N''' + sub.srvname + ''', @subscriber_db = N''' + sub.dest_db + ''''
from syssubscriptions sub inner join sysarticles a on sub.artid = a.artid inner join syspublications pub on pub.pubid = a.pubid
where sub.dest_db not like 'virtual'

 

sample output
--------------------------------------------------------------------------------------------------------------------------------------
exec sys.sp_stoppushsubscription_agent @publication = N'TranSales', @subscriber = N'SQL2016', @subscriber_db = N'TranSales_Subscriber'

 

-- query to build "START AGENT" commands for all Push Distribution Agents
-- set "output to text", then copy and past results in query window to execute

select distinct 'exec sys.sp_startpushsubscription_agent @publication = N''' + pub.name + ''', @subscriber = N''' + sub.srvname + ''', @subscriber_db = N''' + sub.dest_db + ''''
from syssubscriptions sub inner join sysarticles a on sub.artid = a.artid inner join syspublications pub on pub.pubid = a.pubid
where sub.dest_db not like 'virtual'

 

sample output
--------------------------------------------------------------------------------------------------------------------------------------
exec sys.sp_startpushsubscription_agent @publication = N'TranSales', @subscriber = N'SQL2016', @subscriber_db = N'TranSales_Subscriber'

Job 'SQL2016-AdventureWorksLT-TranSales-SQL2016_Reporting-3' started successfully.

Comments (0)

Skip to main content