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.
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> GO EXEC sp_changepublication @publication = '<my publication name>', @property = 'immediate_sync', @value = 'false' GO 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!