Check Default profile in use

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

 

sp_help_agent_profile

 

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

Value	Description
1	Snapshot Agent
2	Log Reader Agent
3	Distribution Agent
4	Merge Agent
9	Queue Reader Agent

use <DistirbutorDatabaseName>

GO

sp_help_agent_profile @agent_type=2

 

Article wise Pending Transaction (Transactional Replication)

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

 

Script

select
count(*) as [# of commands] ,
f.publication,
c.publisher_database_id,
d.publisher_db,
c.article_id,
e.article,
entry_time
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 d.id = 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   
c.publisher_database_id,
f.publication,
d.publisher_db,
c.article_id,
e.article,
entry_time
order by [# of commands] desc

 

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

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, mss.subscription_time 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

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

CREATE TRIGGER LogReaderStatus
ON MSlogreader_history
AFTER INSERT
AS
BEGIN
                 DECLARE @LogReaderStatus int
                 DECLARE @LogReaderComments varchar(255)
                 DECLARE @LogReader            varchar(255)
                 DECLARE @@Status            varchar(255)SELECT @LogReaderComments=lrh.comments,@LogReaderStatus=lrh.runstatus,
@LogReaderComments=lra.name
FROM distribution.dbo.MSlogreader_history lrh INNER JOIN MSlogreader_agents lra
ON lrh.agent_id=lra.id and lrh.timestamp=(SELECT
MAX(i.timestamp) from INSERTED i)
                          IF @LogReaderStatus=5
                                 SET @@Status=‘Log Reader Agent : ‘+ @LogReader+‘ is retrying…..’
                                      BEGIN
                                          EXEC xp_logevent 60000, @@Status, informational
                                       END
                         IF @LogReaderStatus=6
                                 SET @@Status=‘Log Reader Agent : ‘+ @LogReader+‘ Failed…….’
                                       BEGIN
                                           EXEC xp_logevent 60000,@@Status, informational
                                        END
END
GO

 

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

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.

select
*
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)

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)

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’ GO 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 GO — 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’                   GO 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’ GO

 

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]

GO

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           @Publication=BPrompt_Prod_Pub,      @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’ GO

 

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)

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.
http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/06dbde58-457c-4638-9b13-b40b75ea6264

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.

Replicate Indexed Views as Table (Transactional Replication)

In this blog we will talk about why to create an indexed view, how to create an index view and replicate the index view as a table to existing or new Transactional Replication.

While working with one of my client running PeopleSoft Application having database on SQL Server 2005 wanted to segregate the reporting part. To do so we proposed Transactional Replication and use the subscriber database as Reporting Database.

The problem came up was number of tables doesn’t have any primary key and they were not ready to create any type of Keys on the table. So, the antonym is how to add those tables as article in the publication.

Searching the BOL we came across about Indexed view. Now the question is how we can use it.

We took one of the tables which doesn’t have a primary key and created an indexed view with SCHEMA BINDING referencing table.

SCHEMA BINDING: Binds the view to the schema of the underlying base tables.

Creating Indexed View with Schema Binding

USE <Database Name>
CREATE  VIEW Vw_Emp_SalTrans_rep WITH SCHEMABINDING
AS
SELECT EmloyeeID, SalDay, LastSalMon, SalYrs
FROM dbo.Emp_SalTrans

Once the Indexed View is created,Create Unique Clustered Index on the same

CREATE UNIQUE CLUSTERED INDEX Vw_Emp_SalTrans_rep_CluInd ON
Vw_Emp_SalTrans_rep (EmloyeeID, SalDay, LastSalMon, SalYrs)

 

Confirm that table on which view has been created exists on both the publisher and subscriber.
Doing so is a mandatory step since indexed view works on the principle of a log based operation i.e whatever DMLs are being committed on the production table gets replicated to the subscriber through the indexed view.

 

Add the indexed view as an article in existing transactional Replication

To added index view in the exiting transactional publication we will use sp_addartical stored procedure as GUI doesn’t much option to do so.

 

Generate the snasphot of the publication with re-initialize all subscription.

Once is snapshot is generated the same get applied to the subscription database.

Stored Procedure sp_addartical has lot of more option , so to explore more search for sp_addartical in BOL.

 

 

 

Generate Snapshot for New Articles added to Publication

There is always this question how to generate snapshot for the articles which are newly added to the Publication.

Run the below command on the Publisher Server

EXEC sp_changepublication
@publication = ‘PublicationName’,
@property = N’allow_anonymous’,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘PublicationName’,
@property = N’immediate_sync’,
@value = ‘false’
GO

Now add new article to existing publication for which above command was executed and generate the snapshot.Snapshot for the newly addedartical will get generated. To check the same , check the snapshot Folder.