How to shrink distribution database in SQL Server?

/
0 Comments

How to shrink distribution database in SQL Server?

Fix the Retention days for Each Publication

Step 1:

Check for the publication properties subscription expiration setup. If it is set to "never expire" as you can see from the screen capture below.

shrinkDistribution db in sql server


Step 2:

Change the retention days to 120 hours and ran the above job, the distributor database space used was reduced from 30 GB to 300 MB.  After that I shrunk the database to 500 MB and then I re-indexed all tables in the distribution database and ran the clean up job again. Now, it takes less 3 sec to run the clean up job.  Also, replication as a whole is a lot faster.

You can in addition also do the below steps:

  • Setup an Alert to notify if any new publication is created where retention day is set to never expire
  • Implement additional replication monitoring
  • To shrink databases or database files look at DBCC SHRINKDATABASE and DBCC SHRINKFILE


About Post Author

Microsoft Certified SQL Server Developer

You may also like

No comments:

If you have any queries regarding the article or in performing something in Microsoft SQL Server then please let me know.

Followers

Popular Posts