SQL Server uses sp_repldone to identify all non-distributed transactions as distributed

Original: SQL Server uses sp_repldone to identify all non-distributed transactions as distributed

A large number of data operations on the publication database will cause the log to be scanned and read too much, causing the distribution to clog for a long time. There are also some workarounds, refer to SQL Server replication to divide large transactions into small transaction distribution, "SQL Server update caused by a large number of synchronization chain delay problem." It is also possible to make this data non-distributable, especially for new tables, where the data has not yet been used by the user and can be skipped in the log reader to scan for transactions identified as "replicated".

Note: The following simulation operations are performed in the publication database!

1. Remove the Queue Reader Agent-continuous so that the log reader does not continuously scan the transaction log
2. Updating data
3. Enable the log reader, the data is properly synchronized to the subscription
4. Update the data again to do the following

To remove the Queue Reader Agent-continuous, do the following:

The procedure script is as follows, without detailed description:

--Because the log reader is stopped, the sub-publication does not have the record just updated select * from Distribution.dbo.MSrepl_commandsSELECT * from Distribution.dbo.MSrepl_ transactions--but the log in the transaction log that is identified as replicated (REPLICATE) increases the SELECT COUNT (*) From:: Fn_dblog (NULL, NULL) WHERE description= ' REPLICATE '--view the oldest distributed and non-distributed replication transactions--Https://msdn.microsoft.com/zh-cn/library/ms182792.aspxDBCC Opentran () with Tableresults; REPL_DIST_OLD_LSN (535:23:10) repl_nondist_old_lsn (535:26:1)--Convert the above integer to 16 binary select cast (CAST (535 as int) as binary (4 ) + CAST (cast (as int) as binary (4)) + CAST (cast (as int) as binary (2)) SELECT cast (CAST (535 as int) as binary (4)) + C AST (CAST (as int) as binary (4)) + CAST (CAST (1 as int) as binary (2)) (534:2536:18) >>> 0x00000217:00000017:000a (5 34:2552:1) >>> 0x00000217:0000001a:0001--record in the transaction log SELECT [Current Lsn],[operation],[transaction id],left ([ description],20) from::fn_dblog (' 0x00000217:00000017:000a ', ' 0x00000217:0000001a:0001 ')--mark all records in the transaction log as replicated as distributed, The Log reader will not scan these records after that-(only if Xactid and Xact_seqno are NULL, rESET only works. )--https://msdn.microsoft.com/zh-cn/library/ms173775.aspxexec sp_repldone @xactid = null, @xact_segno = NULL, @ Numtrans = 0, @time = 0, @reset = 1@reset = 1 All replicated transactions in the log are marked as distributed, @reset = 0 The transaction log is reset to the first replicated transaction, the transaction is re-read and published;--view transactions that are not currently distributed Left in the transaction log for a transaction that has not yet been sent to the distributor)--https://msdn.microsoft.com/zh-cn/library/ms175114.aspxexec sp_replshowcmds--again to view open transactions, The transaction also does not exist in DBCC OPENTRAN () with tableresults; --Refresh the project cache (the project definition is stored in the cache for increased efficiency)--https://msdn.microsoft.com/zh-cn/library/ms174992 (v=sql.120). Aspxexec sp_ replflush--the other row data is updated again, enabling the Log Reader Agent to synchronize the row data to the subscription, which is not synchronized.

Finally add the-continuous to the Log reader! Complete! (or you can stop the Log Reader Agent)

Reference: Using sp_repldone to mark all pending transactions as have been replicated

SQL Server uses sp_repldone to identify all non-distributed transactions as distributed