複寫疑難排解 Replication Troubleshooting

  • Article

複寫疑難排解 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

https://blogs.msdn.microsoft.com/chrissk/2009/05/25/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/