Sql-Server-Query-World: 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.

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

Sql-Server-Query-World

Fix the Retention days for Each Publication

Step 1:

Step 2:

You can in addition also do the below steps:

Followers

Popular Posts

Labels

About Me