Queries to build START and STOP commands for PUSH Distribution Agents

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.

Chris Skorlinski, Microsoft SQL Server Escalation Services