I write several blog  about replication and mansion commands and stored procedure related to transaction replication, but I would like to dedicate one blog (series of blogs) which should have all these information.

>>Some useful commands and stored procedures

How it works:

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

SELECT * FROM distribution.dbo.MSrepl_Commands  --- commands for all transactions for replication for seqno or all
   WHERE   xact_seqno = 0x00001C030000F4540018

SELECT * FROM distribution.dbo.MSrepl_transactions  -- -- transactions for particular seqno or all
   WHERE  xact_seqno= 0x00001C030000F4540018

and sql script stores into sp_browsereplcmds stored procedure.

sp_browsereplcmds @xact_seqno_start =  '0x00001C030000F4540018',  -- returns all/seqno info (sql query) on replication
    @xact_seqno_end  =  '0x00001C030000F4540018'

http://technet.microsoft.com/en-us/library/ms176109.aspx

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

------------------

Informative Commands:

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

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

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

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

Also transaction replication takes article up to 255 columns limit.

To get the information about publisher from subscriber:

exec sp_MSenumsubscriptions 'both'
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 their in this table.

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

To remove the unwanted Replication follow the below link:

http://support.microsoft.com/kb/324401

I mostly use the following commands for the same:

sp_removedbreplication ‘publisherDB’

exec master..sp_dropdistributor @no_checks = 1

(added 11132009)

Today I was working on Transaction replication on Sql server 2000 sp3a... found very wierd thing happend I removed replication with above commands no replication onto the server but I was still 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, I also tried to detach the db and attached on different server but no luck.

finaly found that "replinfo" column from "sysobjects" table

update sysobjects set replinfo=0 where replinfo=1

Thanx to Vyas for the great article.

http://vyaskn.tripod.com/repl_ans3.htm

I will blog more about useful commands related to replication.

Thanx.

Vinay

Twitter @thakurvinay

Reference:

http://www.replicationanswers.com/default.asp

http://technet.microsoft.com/en-us/library/ms151198.aspx