Replication Basics

Some useful queries to help diagnose issues and also interact with replication via t-sql.

-- Shows Details of the Distirbution Server
select * from msdb..MSdistpublishers

-- Shows Details of any publicating databases
select * from distribution..MSpublisher_databases

-- Shows publication details
select * from distribution..MSpublications

-- Shows details of any objects in a publication
select * from distribution..MSarticles

-- Shows details of the relationship between a subscription and publication
select * from distribution..MSsubscriptions

--Returns information about database objects published as articles in a replication topology. Very useful for seeing how articles interact with a subscription
select * from sys.dm_repl_articles

--gives a high level transaction view of data in the distribution database
select * from msrepl_transaction

--Gives a breakdown view per transaction of all commands in the distribution databases
select * from msrepl_commands


If you need to reset replication or need to force replication to start from a certain point in the log, or need to mark transactions as replication, then the following procedure can be used. It should be used with caution as you can invalidate the order and consistency of delivered transactions.

--When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log
use Publisher_DB
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

sp_repldone is used by the log reader process to track which transactions have been distributed.

With sp_repldone, you can manually tell the server that a transaction has been replicated (sent to the Distributor). It also allows you to change the transaction marked as the next one awaiting replication. You can move forward or backward in the list of replicated transactions. (All transactions less than or equal to that transaction are marked as distributed.)

The required parameters xactid and xact_seqno can be obtained by using sp_repltrans or sp_replcmds.

It can be used in emergency situations to allow truncation of the transaction log when transactions pending replication are present


This procedure can be used to enable or disable replication via a boolean value of true or false

–This procedure creates or drops specific replication system tables, security accounts, and so on, depending on the options given. Sets the corresponding category bit in the master.sysdatabases system table and creates the necessary system tables.
EXEC sp_replicationdboption @dbname = N’Publisher_DB’, @optname = N’publish’, @value = N’false’


This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database. The procedure removes all replication objects from the database in which it is executed, but it does not remove objects from other databases, such as the distribution database.

-- Remove replication objects from the subscription database on MYSUB. Can be run for either a publication or a subscription DB
DECLARE @myDB AS sysname
SET @myDB = N'SubscriberDB'
-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @myDB


sp_replrestart is used when the highest log sequence number (LSN) value at the Distributor does match the highest LSN value at the Publisher. Should only be used when restoring a database published in a transactional replication. Run on the publisher DB.



Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: