Some useful commands and sp for Transaction Replication

This blog is having some useful commands we use for replication and troubleshooting.

>>Some useful commands and stored procedures

How it works:

Log reader agent stores the command and transaction into following tables.

1.      distribution.dbo.MSrepl_Commands 

eg. SELECT * FROM distribution.dbo.MSrepl_Commands 

— commands for all transactions for replication for seqno or all
   WHERE   xact_seqno = 0x00001C030000F4540018

2.      distribution.dbo.MSrepl_Transactions 

eg. SELECT * FROM distribution.dbo.MSrepl_Transactions 

— — transactions for particular seqno or all
   WHERE  xact_seqno= 0x00001C030000F4540018


 3.  sp_browsereplcmds stored procedure


And we can see sql script/text using sp_browsereplcmds stored procedure.

Eg. sp_browsereplcmds @xact_seqno_start =  ‘0x00001C030000F4540018’,  — returns all/seqno info (sql query) on replication
    @xact_seqno_end  =  ‘0x00001C030000F4540018’

It stores all the transactions (sql script) on distribution which will distribute to subscriber. and once these transaction delivers to subscriber. “Distribution cleanup job” will clean these tables.


Informative Commands:

4.      distribution.dbo.MSrepl_errors

select * from distribution.dbo.MSrepl_errors —returns errors for replication.

5.      distribution.dbo.msdistribution_history 

select * from distribution.dbo.msdistribution_history  —- job history

6.      distribution.dbo.MSarticles

select * from distribution.dbo.MSarticles —- give info about replication  articles

7.      publisher.dbo.sysarticles

select * from publisher.dbo.sysarticles  —- give info about replication  articles

Also transaction replication takes article up to 255 columns limit.

Run this at subscriber to get information about replication includes publisher.

8.      sp_MSenumsubscriptions

exec sp_MSenumsubscriptions ‘both’

9.      MSreplication_subscriptions

SELECT * FROM MSreplication_subscriptions

To remove the Replication:

* Once we found on our dev server, the distribution database was not properly deleted and we could not able to work on it.

We wanted to drop the distribution but system was not allowing us… entry was there in this table.

10.  msdistribution_agents 

select * from distribution.dbo.msdistribution_agents  — distributor agent info

To remove the unwanted Replication follow the below link:

I mostly use the following commands for the same:

11.   sp_removedbreplication

sp_removedbreplication ‘publisherDB’

12.  master.dbo.sp_dropdistributor

exec master.dbo.sp_dropdistributor @no_checks = 1

  (added 11132009)

Today I was working on Transaction replication on Sql server 2000 sp3a… found very weird thing, I removed replication with above commands no replication were exists but when tring to drop the table was getting below error:

Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table ‘test1’ because it is used for replication.

I searched a lot but could not find much information, also tried to detach the db and attached on different server but no luckL.

Finally got the solution as, on “sysobjects” table and “replinfo” column was 1

So as sysobjects is a system table (*careful while making any changes at system table) it requires advance sp_confige option =1 for updating system tables.

update sysobjects set replinfo=0 where replinfo=1

Thanx to Vyas for the great article.

I will blog more about useful commands related to replication.




This entry was posted in Replication and tagged . Bookmark the permalink.

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 )

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