How to shrink a distribution database in peer-to-peer replication environment??

[HSAustralia]

Hi

I got Peer-to-Peer Replication configured and running on SQL Server 2005 EE + SP2 on Windows 2003 R2 + SP1 platform.

Total number of DB servers replicated 4.

My question is how to I shrink the distribution DB once it starts to grow about 3GB per day? (Current distribution DB size is ~40GB)

I’ve tried the DBCC shrinkdatabase, DBCC shrinkfile commands. But had no luck there.

As a last restore I truncated the sys table ‘MSrepl_commands’ as it was the largest table in the distribution DB. Prior to this I made sure that P2P replication was stopped and there was no activity on the DB servers.

But the above truncation caused replication errors once replication was enabled. It was rolling back transactions, as it could not find the data in the subscriber.

The standards SQL 2005 distribution db maintenance jobs are created and enabled.

Any ideas how to shrink the distribution DB are welcome

Thanking in advance

HS

[rmiao]

You can't truncate that table manually since it holds commands to replicat. Ensure distribution db clean up job works in schedule, and there are no replication error in each agent.