Archive for the ‘SQL Server Replication’ Category

Article wise Pending Transaction (Transactional Replication)

November 25, 2014 Leave a comment

While troubleshooting transactional replication issues , the very common question people ask is for which tables/articles transaction are waiting to be replicated and how much?


The script below will answer the question



count(*) as [# of commands] ,
from MSrepl_commands  c with (NOLOCK)
inner join MSrepl_transactions  t  with (NOLOCK) on c.publisher_database_id = t.publisher_database_id
and c.xact_seqno = t.xact_seqno
inner join MSpublisher_databases d  with (NOLOCK) on = c.publisher_database_id
inner join MSarticles e  with (NOLOCK) on e.publisher_db = d.publisher_db and e.article_id = c.article_id
inner join MSpublications f  with (NOLOCK) on f.publisher_db = e.publisher_db and e.publication_id = f.publication_id
–where f.publication = ‘(publication_name)’
group by   
order by [# of commands] desc



Transactional Replication Information (Publisher–Publication-Articles-Subscriber–Articles)

October 3, 2011 1 comment

Written a script to pull list of each table in a subscription and when the last time the table replicated.

use [distribution]

select sss.srvname PublisherServer,mss.publication_id,mss.publisher_db,
msa.article Publisher_Articals,
sss1.srvname SubscriberServer,mss.subscriber_id,mss.subscriber_db,mss.article_id,
msa.destination_object Subscriber_Articals,
from MSsubscriptions mss join
sys.sysservers sss on mss.publisher_id=sss.srvid join sys.sysservers sss1
on mss.subscriber_id=sss1.srvid
join MSarticles msa on mss.publisher_id=msa.publisher_id and mss.article_id=msa.article_id


Log Reader Agent Status Notification

February 6, 2011 1 comment

While setting up critical Transactional Replication I came across this scenario of alerting the L3 DBA support team with Log Reader Agent Status and writing it to SQL Server Error Log.

When Log Reader Agent is Retrying to read the Database Log and sending it to Distribution Database and when it fails.

To achieve this I have written an after insert trigger on table
MSlogreader_history in Distribution Database.

To get the last status of the Log Reader Agent execute the below code on distributor database.

SELECT comments,runstatus FROM distribution.dbo.MSlogreader_history
where timestamp=(SELECT
MAX(timestamp) from MSlogreader_history)

—Trigger Code

ON MSlogreader_history
                 DECLARE @LogReaderStatus int
DECLARE @LogReaderComments varchar(255)
                 DECLARE @LogReader            varchar(255)
                 DECLARE @@Status            varchar(255)SELECT @LogReaderComments=lrh.comments,@LogReaderStatus=lrh.runstatus,

FROM distribution.dbo.MSlogreader_history lrh INNER JOIN MSlogreader_agents lra

and lrh.timestamp=(SELECT
MAX(i.timestamp) from INSERTED i)
                          IF @LogReaderStatus=5
SET @@Status=‘Log Reader Agent : ‘+ @LogReader+‘ is retrying…..’
EXEC xp_logevent 60000, @@Status, informational
IF @LogReaderStatus=6
SET @@Status=‘Log Reader Agent : ‘+ @LogReader+‘ Failed…….’
EXEC xp_logevent 60000,@@Status, informational


Transactions waiting to be replicated to Subscription Database (Transactional Replication)

February 2, 2011 5 comments

While troubleshooting Replication issues it’s always a question till what point transaction has been replicated to subscription and from which point transaction are in distribution database waiting to be replicated to subscription database.

Replication Architecture

Let’s say distributor agent failed to replicate transaction from distributor to subscriber due to some reason and now after restarting the distributor agent it’s still not replicating the transactions.
There could be n number of reason due to which distributor agent may fail.

In this post we will focus on how to get last transaction replicated to subscriber database and what all transaction are waiting in the distributor database to be replicated.

Every time distributor agent replicates the transaction from distributor to subscriber, it does an entry in a system table dbo.MSreplication_subscriptions which exists in subscriber database.
The MSreplication_subscriptions table contains one row of replication information for each Distribution Agent.

Code: SELECT publisher,publisher_db,publication,transaction_timestamp
FROM dbo.MSreplication_subscriptions

Transaction_timestamp value is same as xact_seqno and till this sequence number all the transaction are replicated to subscription database.

In case u does not have access to the subscription database and to find out the last replicated xact_seqno to subscriber database from distributor database

sp_MSget_last_transaction Publicationid,Publication_database
sp_MSget_last_transaction 2,TestDB

Connect to the Distributor

Code: select * from MSrepl_commands Where xact_seqno>Transaction_timestamp value

This will give you those records which are in distributor and not been replicated to subscriber database after the last replicated xact_seqno
in subscriber database (dbo.MSreplication_subscriptions)

sp_browsereplcmds : @xact_seqno_start, @xact_seqno_end

The stored procedure will use to view pending commands in the distributor database.|
It will display the commands in the readable format.


Replication monitor will also give the xact_seqno at which distributor agent failed


You can also check the error on browsing MSrepl_errors table in distribution database.

From MSrepl_errors


In this case error is :”The row was not found at the Subscriber when applying the replicated command:

To resolve the same I have xact_seqno and command id , I simple deleted the record from the MSrepl_commands at the distributor.Once done distributor agent will start replicating pending transaction to subscriber.

DELETE from MSrepl_commands where command_id=1295 and xact_seqno=0x000260AD000000100021

Transactional Replication Information (Publisher–Publication-Articals–LogReader)

January 18, 2011 8 comments

I have created a script  to get all replication information about Publisher,Publication,Articals and LogReader in a treeview Structure .Like How many publisher withsnapshot location,how  many publication are generated from each of the publisher,How many articals are associated with each publication ,Log Reader Agents associated with each publication with LodReader Agent Job information. I will write more about Replication Type,SyncType,Subscription Type etc in my next blog post or in the same one.

Script: Download the script and run the Script at Distribution Database

Run this script if u have SQL Server 2005 or above.

Download : Replication_Info_Script – V3

Download :Replication_Info_Script – V2

Download : Replication_Info_Script



Update  :  Added Subscription Information

Update : Added Distributor Information

How to: Initialize a Transactional Subscription from Backup (Transactional Replication)

September 4, 2010 1 comment

How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)

It’s really a gigantic task to setup a transactional replication on database of size 1.2 TB.

As a new bee we will just go and setup transactional replication on any database and generate the snapshot without putting a thought that , how much time snapshot will take and amount of disk for snapshot folder .On the top of this DBA don’t put much brain in thinking what will happen to the network.

The stress-free way of setting up T-Replication for Gigantic Database is to initialize the subscription with Backup.

Here I will demonstrate the steps how we can setup T-Replication and initialize the Subscription with Backup.

1.Configure the distribution on the server as per you subscription. In my case I setup the distribution on the same server where I have my production Database.

2.Right Click the Replication Folder in Object Explorer and Select Publisher Properties. Select the Database from the list and check the Type of Replication to be used for replication.


3.Let’s setup the publication for the database. One way is to setup the replication using GUI with default settings. Add the articles which need to be added.

Below is the sample default script which gets generated using GUI Option.

Use [Bprompt_Partition_Prod]

exec sp_replicationdboption

       @dbname = N’BPrompt_Partition_Prod’,

       @optname = N’publish’,

       @value = N’true’


use [BPrompt_Partition_Prod]

exec [BPrompt_Partition_Prod].sys.sp_addlogreader_agent

      @job_login = N’Provide a Domain user’,

      @job_password = ‘@Jon_Login Password’,

      @publisher_security_mode = 0,

      @publisher_login = N’SQLLogin or Impersonating the Process Account’,

      @publisher_password = N’ ‘,

      @job_name = null


— Adding the transactional publication

use [BPrompt_Partition_Prod]

exec sp_addpublication

                  @publication = N’BPrompt_Prod_Pub’,

                  @description = N’Transactional publication of database ”BPrompt_Partition_Prod” from Publisher ”NL03-DF271\DELL”.’,

                  @sync_method = N’concurrent’,

                  @retention = 0, @allow_push = N’true’,

                  @allow_pull = N’true’,

                  @allow_anonymous = N’false’,

                  @enabled_for_internet = N’false’,

                  @snapshot_in_defaultfolder = N’true’,

                  @compress_snapshot = N’false’,

                  @ftp_port = 21,

                  @ftp_login = N’anonymous’,

                  @allow_subscription_copy = N’false’,

                  @add_to_active_directory = N’false’,

                  @repl_freq = N’continuous’,

                  @status = N’active’,

                  @independent_agent = N’true’,

                  @immediate_sync = N’false’,

                  @allow_sync_tran = N’false’,

                  @autogen_sync_procs = N’false’,

                  @allow_queued_tran = N’false’,

                  @allow_dts = N’false’,

                  @replicate_ddl = 1,

                  @allow_initialize_from_backup = N’false’,

                  @enabled_for_p2p = N’false’,

                  @enabled_for_het_sub = N’false’


use [BPrompt_Partition_Prod]

exec sp_addarticle

       @publication = N’BPrompt_Prod_Pub’,

       @article = N’xyz’,

       @source_owner = N’dbo’,

       @source_object = N’xyz’,

       @type = N’logbased’,

       @description = null,

       @creation_script = null,

       @pre_creation_cmd = N’drop’,

       @schema_option = 0x000000000803509F,

       @identityrangemanagementoption = N’manual’,

       @destination_table = N’xyz’,

       @destination_owner = N’dbo’,

       @vertical_partition = N’false’,

       @ins_cmd = N’CALL sp_MSins_dboxyz’,

       @del_cmd = N’CALL sp_MSdel_dboxyz’,

       @upd_cmd = N’SCALL sp_MSupd_dboxyz’



4.For an existing publication, ensure that the publication supports the ability to initialize from backup by executing sp_helppublication (Transact-SQL) at the Publisher on the publication database. Note the value of allow_initialize_from_backup in the result set.

use [BPrompt_Partition_Prod]


EXEC sp_helppublication @publication = BPrompt_Prod_Pub
By default the value of allow_initialize_from_backup is set to 0.

To set the publication to support the ability to initialize from backup

EXEC sp_changepublication     


     @property = N’allow_initialize_from_backup’,   

     @value = True

This can also be achieved at the time of configuring the publication using the script.

  1. Now Backup the Publisher Database
  2. Restore the Database Backup at the Subscriber Server.
  3. Now Create the subscription using @sync_type = N’initialize with backup’
use [BPrompt_Partition_Prod]

exec sp_addsubscription

                   @publication = N’BPrompt_Prod_Pub’,

                   @subscriber = N’Subscriber Server’,

                   @destination_db = N’BPrompt_Partition_Prod_Rep’,

                   @subscription_type = N’Push’,

                   @sync_type = N’initialize with backup’,

                   @article = N’all’,

                   @update_mode = N’read only’,

                   @subscriber_type = 0,

                   @backupdevicetype =’disk’,

                   @backupdevicename =’\\SharedDrive\j$\BPromptSubscriberDB\BPrompt_Partition_Prod.BAK’

–Location of Backup file–              

exec sp_addpushsubscription_agent

                  @publication = N’BPrompt_Prod_Pub’,

                  @subscriber = N’SubscriberServer’,  —Name of the Subscribing Server

                  @subscriber_db = N’BPrompt_Partition_Prod_Rep’,

                  @job_login = N’Provide a Domain user’,

                  @job_password = Domain User Password’,

                  @subscriber_security_mode = 0,

                  @subscriber_login = N’SQLLogin or Impersonating the Process Account’,

                  @subscriber_password = ‘Password’,

                  @frequency_type = 64,

                  @frequency_interval = 0,

                  @frequency_relative_interval = 0,

                  @frequency_recurrence_factor = 0,

                  @frequency_subday = 0,

                  @frequency_subday_interval = 0,

                  @active_start_time_of_day = 0,

                  @active_end_time_of_day = 235959,

                  @active_start_date = 20100903,

                  @active_end_date = 99991231,

                  @enabled_for_syncmgr = N’False’,

                  @dts_package_location = N’Distributor’



Once Subscription is configured, Open the Replication Monitor and double click the Running Subscription.

You will see the Distributor agent will start doing the check sum validation for all the included article.

Get undistributed Transaction List (Transactional Replication)

April 23, 2010 Leave a comment

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.


Get every new post delivered to your Inbox.