Shulou(Shulou.com)06/01 Report--

Update a big table in Publisher database, the number of data rows is more than 340 million. Since the clustered Index key is not updated, only more than 340 million Update Commands and 1 Transaction are generated, and the amount of data is still very large. During the process of Log reader inserting Commands into distribution.dbo.MSrepl_commands, almost all Distribution Agent throws out Performance Critical's Warning,Log Reader and inserts Commands very slowly, and it is initially predicted that only the time of inserting Update Commands into MSrepl_commands will require 12hours. In order not to affect the synchronization of other data, I intend to delete the Publication and Subscription of the table, and then synchronize the data manually.

Scenario1:

In Subscriber, the Subscription was deleted successfully. Link to Publisher, and when deleting Publication, SSMS starts with NO Responding and then reports an error. Look at the Session that Subscriber is running and find that the sessions of Distribution Agents is all block. The reason why Publication cannot be deleted is that Log Reader is reading Commands. This operation cannot be terminated abnormally. To avoid corrupting other data, you have to wait for Log Reader to insert Update commands into distribution. Leader only gives an one-day buffer, and the problem must be resolved tomorrow.

Scenario2:

After Log Reader inserts all the commands of the Publisher into the distribution.dbo.MSrepl_commands, because the Subscription has been deleted in Scenario1, the Update Commands is not synchronized to the Target table, but it is not deleted and is still stored in the MSrepl_commands. If you run Distribution clean up job, reducing the time of Commands Retition will definitely affect the synchronization of other data.

EXEC dbo.sp_MSdistribution_cleanup @ min_distretention = 0, @ max_distretention = 120

Therefore, the corresponding commans must be manually removed from the MSrepl_commands and the corresponding Transaction must be removed from the distribution.dbo.MSrepl_transactions.

According to the publisher_database_id and entry_time in MSrepl_transactions, filter out the corresponding xact_seqno (the transaction ID used by Replication to synchronize Commands), and view the number of commands in MSrepl_commands according to publisher_database_id and xact_seqno to use the xact_seqno of verify transaction.

Select count (0) from distribution.dbo.MSrepl_commands with (nolock) where xact_seqno=0x000055A8000069610001 and publisher_database_id=19

You can also use sp_browsereplcmds to view the SQL statement in msrepl_commands, finally determine the xact_seqno of the transaction, and remove commands and transaction from distribution based on publisher_database_id and xact_seqno.

Deletefrom distribution.dbo.MSrepl_commands where xact_seqno=0x000055A8000069610001 and publisher_database_id=19deletefrom distribution.dbo.MSrepl_transactionswhere xact_seqno=0x000055A8000069610001 and publisher_database_id=19

It took 3 hours to finally delete commands and transaction, and Replication returned to normal.

Mark: when updating Big Table, it is best to turn off SQL Server Replication, manually update in Publisher and Subscriber, and then rebuild Replication after the update is complete.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.