Distribution database causing high disk IO, many reads / sec.

Playing around with VMWare performance data, i found a server with unusual high disk IO, and by that I mean registering around 300 MB/sec all the time.
This server was a SQL Server 2008 R2 with some tables being replicated to a second server. Looking closely I noticed that the distribution database was responsible for this, with all 300 MB /sec being reads and no writes.
The distribution db was quite large as well, around 30 GB. I guessed the replication job had some problem with cleaning out old data, and having to constantly going through those 30 GB over and over again.

Looking at the Replication Monitor and the settings for each of the publications.

The cause for this wasn’t the “Subscriptions never expire, but they can be deactivated until they reinitialized” setting, but instead the “Snapshot always available” and “Allow anonymous subscriptions” setting. You can see the settings when choosing properties on your replications.

Both were set to true, and that’s why the distribution database had so many rows in its tables. They were never deleted and the SQL Agent jobs kept going through the records over and over again.

Changing them both to false, and letting the “Distribution clean up: distribution” SQL Agent job do it’s work cleaned out the distribution database. After that the disk IO went back to normal.

USE <my replicated database>
EXEC sp_changepublication @publication = '<my publication name>', 
@property = 'immediate_sync', @value = 'false'
EXEC sp_changepublication @publication = '<my publication name>', 
@property = 'allow_anonymous', @value = 'false'

The solution to my problem was found here

Don’t forget to shrink your distribution database afterwards!

This entry was posted in DBA, SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *