Archive for the ‘SQL Server Replication’ Category

Check Default profile in use

June 8, 2015 Leave a comment

Displays the profile of a specified agent. This stored procedure is executed at the Distributor on any database.




sp_help_agent_profile [ [ @agent_type = ] agent_type ] 
    [ , [ @profile_id = ] profile_id ]




Snapshot Agent


Log Reader Agent


Distribution Agent


Merge Agent


Queue Reader Agent

use <DistirbutorDatabaseName>


sp_help_agent_profile @agent_type=2



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.