Important Commands and Script of Replication in SQL Server

--Note-

--It should be noted that the data available in

--MSrepl_commands,

--MSrepl_transactions,

--sp_browsereplcmds

--is purged periodically based on the schedule of the distribution clean up job and the distribution retention period.

 select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time

   from MSrepl_commands rc, MSrepl_transactions rt

    where rc.xact_seqno =  rt.xact_seqno

-- It will show the latency in Sec

Select object_name, counter_name, instance_name, round(cntr_value/1000,0) as latency_sec 
from sys.dm_os_performance_counters 
where object_name like '%Replica%' and counter_name like '%Logreader:%latency%'

 union

Select object_name, counter_name, instance_name, round(cntr_value/1000,0) as latency_sec 
from sys.dm_os_performance_counters 
where object_name like '%Replica%' and counter_name like '%Dist%latency%'

-- To get Distribution Agent performance and history its status and time and latency and many important details

USE distribution

go

SELECT TOP 100 time,

Cast(comments AS XML) AS comments,

runstatus,

duration,

xact_seqno,

delivered_commands,

average_commands,

current_delivery_rate,

delivered_transactions,

error_id,

delivery_latency

FROM msdistribution_history WITH (nolock)

ORDER BY time DESC

-- To get log reader agent history its status and time and latency and many important details

USE distribution

GO

SELECT time,

CAST(comments AS XML) AS comments,

runstatus,

duration,

xact_seqno,

delivered_transactions,

delivered_commands,

average_commands,

delivery_time,

delivery_rate,

delivery_latency / ( 1000 * 60 ) AS delivery_latency_Min

FROM mslogreader_history WITH (nolock)

WHERE time > '2014-10-28 16:00:00.130'

ORDER BY time DESC

-- It will give the info of table and it size in distribution database

--If you see high rowcount (like 1 or 2 million) this means there is some problem in replication. 

Clean-up job (this is in distribution server) is not running

Its taking lot of time to deliver the commands to subscriber

There may be blocking in distribution server due to clean-up job

USE distribution

GO

SELECT Getdate() AS CaptureTime,

Object_name(t.object_id) AS TableName,

st.row_count,

s.NAME

FROM sys.dm_db_partition_stats st WITH (nolock)

INNER JOIN sys.tables t WITH (nolock)

ON st.object_id = t.object_id

INNER JOIN sys.schemas s WITH (nolock)

ON t.schema_id = s.schema_id

WHERE index_id < 2

AND Object_name(t.object_id)

IN ('MSsubscriptions', --contains one row for each published article in a subscription

'MSdistribution_history', --contains history rows for the Distribution Agents associated with the local Distributor

'MSrepl_commands', --contains rows of replicated commands

'MSrepl_transactions') --contains one row for each replicated transaction

ORDER BY st.row_count DESC

-- Use the below query to identify what is going on currently in the distribution server. (You can use the same query in any server for the same purpose)

SELECT r.session_id,

s.program_name,

s.login_name,

r.start_time,

r.status,

r.command,

Object_name(sqltxt.objectid, sqltxt.dbid) AS ObjectName,

Substring(sqltxt.text, ( r.statement_start_offset / 2 ) + 1, ( (

CASE r.statement_end_offset

WHEN -1 THEN

datalength(sqltxt.text)

ELSE r.statement_end_offset

END

- r.statement_start_offset ) / 2 ) + 1) AS active_statement,

r.percent_complete,

Db_name(r.database_id) AS DatabaseName,

r.blocking_session_id,

r.wait_time,

r.wait_type,

r.wait_resource,

r.open_transaction_count,

r.cpu_time,-- in milli sec

r.reads,

r.writes,

r.logical_reads,

r.row_count,

r.prev_error,

r.granted_query_memory,

Cast(sqlplan.query_plan AS XML) AS QueryPlan,

CASE r.transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncomitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

END AS Issolation_Level,

r.sql_handle,

r.plan_handle

FROM sys.dm_exec_requests r WITH (nolock)

INNER JOIN sys.dm_exec_sessions s WITH (nolock)

ON r.session_id = s.session_id

CROSS apply sys.Dm_exec_sql_text(r.sql_handle) sqltxt

CROSS apply

sys.Dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) sqlplan

WHERE r.status <> 'background'

ORDER BY r.session_id

go

-- To get Error details of Replication

Use Distribution

go

select * from dbo.MSrepl_errors where error_code in ('2601','2627','25098')

-- To get Articles details

select publisher_id,

publisher_db,

publication_id,     

article,     

article_id    ,

destination_object, 

source_owner  ,

source_object,      

description   ,

destination_owner  from distribution.dbo.MSarticles

--Both query provides, Sequence number and command id and other replicated commands details

Select 

publisher_database_id,

xact_seqno,

type,

article_id,

originator_id,

command_id,

partial_command,

command,                                                                                                                                  hashkey,    

originator_lsn from  distribution.dbo.MSrepl_commands

select publisher_database_id,     xact_id,      xact_seqno,   entry_time  from distribution.dbo.MSrepl_transactions

--To get undistributed commands (Pending cound and estimated process time)

sp_replmonitorsubscriptionpendingcmds 

 @publisher ='INHYIZPC01014A\SQL_INSTANCE3',

 @publisher_db = 'Replication_DB',

 @publication ='Enter name of publication', 

 @subscriber ='INHYIZPC01014A\SQL_INSTANCE2',

 @subscriber_db ='Replication_DB',

 @subscription_type ='0' --0 for push and 1 for pull

 -- using following SP you will get publication name --EXEC sp_helppublication;

 --************START -- To find the the issue **************--

 -- Copy "Sequence Number" form the error and use in below query.

SELECT  art.publisher_id ,

        art.publisher_db ,

        art.publication_id ,

        art.article ,

        art.article_id  ,

        art.destination_object  ,

        art.source_owner  ,

        art.source_object

FROM    distribution.dbo.MSarticles AS art

        JOIN distribution.dbo.MSrepl_commands AS com

        ON art.Article_id = com.Article_id

WHERE   com.xact_seqno = 0x00000024000002F0000400000000

--The above query will return publisher_database_id and article_id.which we will use in below query-

EXEC distribution.dbo.sp_browsereplcmds

     @xact_seqno_start = '0x00000024000002F0000400000000' ,

     @xact_seqno_end = '0x00000024000002F0000400000000' ,

     @publisher_database_id = 1 ,

     @article_id = 1 ,

     @command_id = 1

--The above query will return Command value, this is the duplicate value query which has entered in the table of subscriber end.

 --************END-- to find the the issue **************--

 --To show subscriber status for transactional publications:

sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 0

--To show subscriber status for snapshot publications:

sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 1

--To show subscriber status for merge publications:

sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 2

--********** Execute at the Distributor in the master database **********-- 

USE master; 

go 

exec sp_helparticle @publication=<publication name>

--Is the current server a Distributor? 

--Is the distribution database installed? 

--Are there other Publishers using this Distributor? 

EXEC sp_get_distributor 

--Is the current server a Distributor? 

SELECT is_distributor FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername; 

--Which databases on the Distributor are distribution databases? 

SELECT name FROM sys.databases WHERE is_distributor = 1 

--What are the Distributor and distribution database properties? 

EXEC sp_helpdistributor; 

EXEC sp_helpdistributiondb; 

EXEC sp_helpdistpublisher; 

--********** Execute at the Publisher in the master database **********-- 

--Which databases are published for replication and what type of replication? 

EXEC sp_helpreplicationdboption; 

--Which databases are published using snapshot replication or transactional replication? 

SELECT name as tran_published_db FROM sys.databases WHERE is_published = 1; 

--Which databases are published using merge replication? 

SELECT name as merge_published_db FROM sys.databases WHERE is_merge_published = 1; 

--What are the properties for Subscribers that subscribe to publications at this Publisher? 

EXEC sp_helpsubscriberinfo; 

--********** Execute at the Publisher in the publication database **********-- 

--What are the snapshot and transactional publications in this database? 

 

EXEC sp_helppublication; 

--What are the articles in snapshot and transactional publications in this database? 

--REMOVE COMMENTS FROM NEXT LINE AND REPLACE <PublicationName> with the name of a publication 

--EXEC sp_helparticle @publication='<PublicationName>'; 

--What are the merge publications in this database?  

EXEC sp_helpmergepublication; 

--What are the articles in merge publications in this database? 

EXEC sp_helpmergearticle; 

-- to return information on articles for a single publication, specify @publication='<PublicationName>' 

--Which objects in the database are published?

SELECT name AS published_object, schema_id, is_published AS is_tran_published, is_merge_published, is_schema_published 

FROM sys.tables WHERE is_published = 1 or is_merge_published = 1 or is_schema_published = 1 

UNION 

SELECT name AS published_object, schema_id, 0, 0, is_schema_published 

FROM sys.procedures WHERE is_schema_published = 1 

UNION 

SELECT name AS published_object, schema_id, 0, 0, is_schema_published 

FROM sys.views WHERE is_schema_published = 1; 

--Which columns are published in snapshot or transactional publications in this database? 

SELECT object_name(object_id) AS tran_published_table, name AS published_column FROM sys.columns WHERE is_replicated = 1; 

--Which columns are published in merge publications in this database? 

SELECT object_name(object_id) AS merge_published_table, name AS published_column FROM sys.columns WHERE is_merge_published = 1; 

--If we done changes on Agent profile option (Replication Monitor --> right click Agent profile ---> for Distribution agent)

--To STOP the Distribution Agent:

sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db

--To START the Distribution Agent:

sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db