Get undistributed Transaction List (Transactional Replication)

Thursday i was too happy that weekend started as I took a leave on friday, as i stepped out from my technology room i got an unexpected call from the technology datacenter hearing what he said said my dream of weekend shattered.

 The problem was one of the SQL Server on Cluster failed .On the server we have a huge OLTP database over 1.2 TB which was configured with transactional replication on a subscriber db on different server.

 The issues where will the transaction get replicated from distribution when the server will be up and running or will there be any problem doing so.

When the server came up the replication was messed up, Transaction where getting replicated from publisher to distributor but failed to replicate the same transaction from distributor to subscriber.

I spent enough time to know what all transaction failed to replicate and what the way to replicate the same at subscriber seamlessly.

Later hopefully I managed to resolve this with one transaction loss (identified one).

How to know total number of transaction which is not replicated to subscriber?

select * From msdistribution_status

article_id agent_id UndelivCmdsInDistDB DelivCmdsInDistDB
1 1                0                   1


How to know details of undelivered transactions?

—–Code has been copied and modified from Paul’s Blog————–

declare @xact_seqno varbinary(16)
select @xact_seqno = max(xact_seqno) from MSsubscriptions
inner join MSpublications
on MSpublications.publication_id = MSsubscriptions.publication_id
inner join MSdistribution_history
on MSdistribution_history.agent_id = MSsubscriptions.agent_id
Where subscriber_db = ‘reptest_test’
AND Publication = ‘Pub_test’

Print @xact_seqno

declare @str varchar(255)
set @str = master.dbo.fn_varbintohexstr (@xact_seqno)
set @str = left(@str, len(@str) – 8)
–if exists(select object_id(‘tempdb..#trancommands’)) drop table #trancommands
create table #trancommands
(xact_seqno varbinary(16) null,
originator_srvname sysname null,
originator_db sysname null,
article_id int null,
type int null,
partial_command bit null,
hashkey int null,
originator_publication_id int null,
originator_db_version int null,
originator_lsn varbinary(16) null,
command nvarchar(1024) null,
command_id int) 

insert into #trancommands
exec sp_browsereplcmds @xact_seqno_start = @str
select * from #trancommands where xact_seqno > @xact_seqno
drop table #trancommands

Here we got lot of transactions which were in the waiting list (waiting to be replicated), Now we have to identify those transaction which need to be marked as replicated or deleted.

I put lot of efforts to identify those transactions and lucky we found one such transaction.

Now the question is how to mark that transaction as replicated or delete it from the distribution list.

I posted the question on MSDN SQL Server Forum and in a day time I got my answer.

We can delete the transaction using the below code:

DELETE FROM  distribution.dbo.msrepl_commands  where xact_seqno=put the xact_seqno which need to be deleted.

Doing so my transaction replication started without pain and we lost nothing as I have deleted the same transaction from publisher also.

I wana thank though this BLOG to Wissam and Hilary on helping me on this.

Leave a Reply

Your email address will not be published.