Replication Troubleshooting 複寫疑難排解

Replication Troubleshooting
複寫疑難排解

從TLog讀取

sp_replcounters (Transact-SQL)
Returns replication statistics about latency, throughput, and transaction count for each published database. This stored procedure is executed at the Publisher on any database.

sp_replcounters

sp_repltrans (Transact-SQL)

Returns a result set of all the transactions in the publication database transaction log that are marked for replication but have not been marked as distributed. This stored procedure is executed at the Publisher on a publication database.
只回傳交易序號

sp_replcmds (Transact-SQL)
Returns the commands for transactions marked for replication. This stored procedure is executed at the Publisher on the publication database.
sp_replcmds is used by the log reader process in transactional replication.

將交易轉成SQL命令回傳

sp_replcmds @maxtrans = 500
GO
sp_replflush
GO

sp_replshowcmds (Transact-SQL)
Returns the commands for transactions marked for replication in readable format. sp_replshowcmds can be run only when client connections (including the current connection) are not reading replicated transactions from the log. This stored procedure is executed at the Publisher on the publication database.

將交易轉成易讀的SQL命令回傳

sp_replshowcmds @maxtrans = 500
GO
sp_replflush
GO

sp_replcmds @maxtrans = 500 -- retrieve 500 pending transactions
GO
SELECT dbid, begin_lsn, commit_lsn, rows, cmds_in_tran
FROM sys.dm_repl_traninfo
GO

SELECT count(*) FROM ::fn_dblog(NULL, NULL)
GO
-- Records marked for REPLICATION
SELECT count(*) FROM ::fn_dblog(NULL, NULL) WHERE Description='REPLICATE'
GO

從散發資料庫讀取
[Distributor]
sp_browsereplcmds (Transact-SQL)
Returns a result set in a readable version of the replicated commands stored in the distribution database, and is used as a diagnostic tool. This stored procedure is executed at the Distributor on the distribution database.

從散發資料庫讀取暫存的交易SQL命令

[Reference]

Transactional Replication Conversations

Transactional Replication Conversations

Microsoft SQL Server Replication Web Sites you may want to visit
https://blogs.msdn.microsoft.com/chrissk/2010/03/14/microsoft-sql-server-replication-web-sites-you-may-want-to-visit/

Monitor Replication 複寫監控

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)
https://docs.microsoft.com/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.

SQL Server 2008 R2 Management Studio – Darker Shades of Blue Theme 套用Visual Studio色彩佈景主題環境設定檔

SQL Server 2008 R2 Management Studio – Darker Shades of Blue Theme

SSMS 2008 R2 套用Visual Studio色彩佈景主題環境設定檔

---

SQL 2012 SSMS以上才支援匯出與匯入環境設定檔

SQL 2008 R2 SSMS需自行調整字型與色彩，以下方法可以直接套用已經設定好的色彩佈景主題

SQL Server 2008 R2 Management Studio – Darker Shades of Blue Theme 套用Visual Studio色彩佈景主題環境設定檔