The flag affects the following code:
if @has_immediate_sync = 0 delete TOP(5000) MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions with (INDEX(ucMSrepl_transactions)) wherepublisher_database_id = @publisher_database_id andxact_seqno <= @max_xact_seqno andxact_seqno <> @last_xact_seqno andxact_seqno <> @last_log_xact_seqno andxact_seqno <> @second_largest_log_xact_seqno --ensure at least two log entries are left, when there existed more than two log entriesOPTION (MAXDOP 1)elsedelete TOP(5000) MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions with (INDEX(ucMSrepl_transactions)) wherepublisher_database_id = @publisher_database_id andxact_seqno <= @max_xact_seqno andxact_seqno <> @last_xact_seqno andxact_seqno
xact_seqno <> @last_log_xact_seqno and <> @second_largest_log_xact_seqno and --ensure at least two log entries are left, when there existed more than two log entries-- use nolock to avoid deadlocknot exists (select * from MSrepl_commands c with (nolock) wherec.publisher_database_id = @publisher_database_id andc
c.xact_seqno = MSrepl_transactions.xact_seqno and .xact_seqno <= @max_xact_seqno)
IN [dbo].[sp_MSdelete_dodelete] and [dbo].[sp_MSdelete_dodelete], if @has_immediate_sync = 0 then it will check the table MSrepl_commands. The table has 160MM records, so the performance will be very bad.OPTION (MAXDOP 1)
To check whether the flag is set to true:
select immediate_sync,* from distribution.dbo.MSpublications
Find one publication in a DB has the flag = true, and this affects all publication in same database.To set the flag to false by: EXEC sp_changepublication @publication='Publication_name', @property='immediate_sync',@value='FALSE'
If the flag is true, the cleanup the job only remove the records specified by@max_distretention = 72.
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72. So these two tables will keep data up to 72 hours.
This caused the table MSrepl_commands had 160MM records. The size of distribution DB is 70GB.
After setting the flag to false, the cleanup job removed replicated transactions and commands in distribution DB.
The "Distribution clean up: distribution" job takes from 1 hour to 20 seconds.
The records number in MSrepl_commands goes down to 2.6MM.The size of distribution DB goes down to 2GB.
No comments:
Post a Comment