--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
