[Translation] how to solve the problem when the distribution database grows to 25 GB

Sometimes, the distribution database (DistributionDatabase) will grow very large, so how to solve it? Please refer to the solution of ChrisSkorlinski and MicrosoftSQLServerEscalationServices. Original article address: HowtoresolvewhenDistributionDatabaseisgrowinghuge (+ 25gig)

Sometimes, Distribution Database (Distribution Database) will grow very large, so how to solve it, please refer to the solution of Chris Skorlinski and Microsoft SQL Server Escalation Services. Original article address: How to resolve when Distribution Database is growing huge (+ 25gig), translated by myself

Sometimes, Distribution Database (Distribution Database) will grow very large, so how to solve it, please refer to the solution of Chris Skorlinski and Microsoft SQL Server Escalation Services.

Original article address: How to resolve when Distribution Database is growing huge (+ 25gig). My translation level is limited. If any translation is inappropriate or incorrect, please do not advise!

Yes, of course I know that large databases are relative, but in general, if you see that the distribution database is growing to 25 GB, it means it is difficult for the cleaning process to delete replication transactions, next I will introduce how and why the cleanup process works. But now I want to introduce a frequently used technique for clearing data rows from distribution data. This solution includes modifying the SQL copy stored procedure and adding the number of lines deleted by each transaction. If you are uncomfortable with the code modification, skip to STEP 7.

The first article published a "conservative" approach. Next, I will publish some more "radical" solutions.

1) Save the original script for clearing and deleting the srepl_commands stored procedure of the msrepl_commands object

Sp_helptext sp_MSdelete_publisherdb_trans

2) Change CREATE to ALTER

Alter procedure sp_MSdelete_publisherdb_trans

3) change the DELETE operation from 2000 to 100000.

Delete top (2000) MSrepl_commands...

4) Save msrepl_transaction to clear the original code of sp_MSdelete_dodelete In the Stored Procedure

Sp_helptext sp_MSdelete_dodelete

5) change the CREATE keyword to ALTER.

Alter procedure sp_MSdelete_dodelete

6) change the location where the MSrepl_transactions record is deleted and change 5000 to 100000.

Delete TOP (5000) MSrepl_transactions...

7) Determine oldest day containing transactions decides how many days to keep things

Code Snippet

8) execute SSMS or TSQL jobs to clear previous transactions (24 hours * 5 days = 120), and then continue to execute the reduce @ max_distretention execution.

Code Snippet

Hope you found this helpful,

Hope this is useful to you.

Chris Skorlinski, Microsoft SQL Server Escalation Services

,