Caveman's Blog

My commitment to learning.

Start/Stop SQL Server Replication Agent using TSQL

leave a comment »


On a recently client engagement we ran into an issue with a nightly SQL Server job. The job was unable to generate data for a certain warehouse database. As it tuns out to be via process of elimination, I was able to figure out that there were some SQL distribution agents that were interfering with the nightly SQL job that caused the issue. I have stopped the distribution agents and ran the job manually to able to successfully populate the data, followed by re-starting the agents. The process of stopping and starting was done using the SQL Server Management studio.

Now that I knew how to populate the data, I had to automate the process of stopping and starting the distribution agents before and after running the nightly SQL job. Here are the stored procedures that can be used to achieve the same:

--STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db

--START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db</pre>
  • @publisher is the name of the Server that is publishing
  • @publisher_db is the database of the publisher server
  • @publication is the name of the table/object that is getting published
  • @subscriber is the name of the subscriber server
  • @subscriber_db is the name of the subscriber database

The above commands have to be executed in the publisher server against the 'distribution' database. Here is an example:


exec distribution.dbo.sp_MSstartdistribution_agent @publisher  = 'PUBLISHERSERVER',
@publisher_db   = 'PUBLISHERDB',
@publication    = 'TABLE_EMPLOYEE',
@subscriber     = 'SUBSCRIBERSERVER',
@subscriber_db  = 'WAREHOUSEDB'

About these ads

Written by cavemansblog

March 12, 2012 at 11:21 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: