複寫監控 Monitor Replication

  • Article

複寫監控 Monitor Replication

Measure Latency and Validate Connections for Transactional Replication
使用Tracer Token監控複寫延遲

保留延遲紀錄作法請參考我這篇

 

Programmatically Monitor Replication--Programmatically Monitor Replication

 USE [distribution]
GO
sp_replmonitorhelppublisher
GO

sp_replmonitorhelppublisher (Transact-SQL)

 USE [distribution]
GO
sp_replmonitorhelppublication @publisher = N'SQLDEV1'
GO

sp_replmonitorhelppublication (Transact-SQL)

 USE [distribution]
GO
sp_replmonitorhelpsubscription @publisher = N'SQLDEV1', @publication_type = 0
GO

sp_replmonitorhelpsubscription (Transact-SQL)

 USE [distribution]
 GO
 sp_replmonitorsubscriptionpendingcmds
 @publisher = N'SQLDEV1',
 @publisher_db = 'AdventureWorks2008R2',
 @publication = 'Orders',
 @subscriber = 'SQLDEVSUB1',
 @subscriber_db = 'AdventureWorks2008R2',
 @subscription_type = 0
 GO

Result Sets
Column name Data type Description
pendingcmdcount int The number of commands that are pending for the subscription.
estimatedprocesstime int Estimate of the number of seconds required to deliver all of the pending commands to the Subscriber.

sp_replmonitorsubscriptionpendingcmds (Transact-SQL)

 

Monitoring Replication with System Monitor (複寫相關效能計數器Performance Counter)
/en-us/sql/relational-databases/replication/monitor/monitoring-replication-with-system-monitor

Agent Performance object Counter Description
All agents Microsoft SQL Server: Replication Agents Running The number of replication agents currently running.
Snapshot Agent SQL Server: Replication Snapshot Snapshot: Delivered Cmds/sec The number of commands per second delivered to the Distributor.
Snapshot Agent SQL Server: Replication Snapshot Snapshot: Delivered Trans/sec The number of transactions per second delivered to the Distributor.
Log Reader Agent SQL Server: Replication Logreader Logreader: Delivered Cmds/sec The number of commands per second delivered to the Distributor.
Log Reader Agent SQL Server: Replication Logreader Logreader: Delivered Trans/sec The number of transactions per second delivered to the Distributor.
Log Reader Agent SQL Server: Replication Logreader Logreader: Delivery Latency The current amount of time, in milliseconds, elapsed from when transactions are applied at the Publisher to when they are delivered to the Distributor.
Distribution Agent SQL Server: Replication Dist. Dist: Delivered Cmds/sec The number of commands per second delivered to the Subscriber.
Distribution Agent SQL Server: Replication Dist. Dist: Delivered Trans/sec The number of transactions per second delivered to the Subscriber.
Distribution Agent SQL Server: Replication Dist. Dist: Delivery Latency The current amount of time, in milliseconds, elapsed from when transactions are delivered to the Distributor to when they are applied at the Subscriber.
Merge Agent SQL Server: Replication Merge Conflicts/sec The number of conflicts per second occurring during the merge process.
Merge Agent SQL Server: Replication Merge Downloaded Changes/sec The number of rows per second replicated from the Publisher to the Subscriber.
Merge Agent SQL Server: Replication Merge Uploaded Changes/sec The number of rows per second replicated from the Subscriber to the Publisher.