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

Question

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

hs.australia

SSC Enthusiast

Points: 127
More actions
October 5, 2007 at 8:31 am

#198776

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 ~58GB)
I’ve tried the DBCC shrinkdatabase, DBCC shrinkfile commands. But had no luck with it..
As a last restore I truncated the sys table ‘MSrepl_commands’ as it was the largest table (55 million rows) 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. (2 hrs. transaction retention and 3 hrs history retention) job runs every 10 min.
Any ideas how to shrink the distribution DB are welcome
Thanking in advance
HS

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Apparently distribution cleanup is not working for you. Distribution clean up is what does the Job you want. You should check the logs for that job and verify the the "undistributed" transactions ar "0" on your entire topology at some point. If the topology never synched up you have to wait until that happens. All servers must be in synch at some point.

* Noel

hs.australia SSC Enthusiast Points: 127 More actions · Answer 2

Tnx for the feedback Noel,

let me explain our replication setup a bit,

We got 4 sql 2005 servers Peer-to-Peer replicated. 2 DB server at each data centre.

only 1 database is replicated across all 4 servers. each server has its own distribution db on the server.

i check the distribution clean up job on the server that i have a ~58 GB distribution database. the job got executed successfully every 10 min. No errors at all.

With regards undistributed commands, there were zero undistributed commands in the distribution DB.

any ideas y the clean up job didn't the MSrepl_commands tables?? its grown by the hr.

Tnx

Regards

HS

hs.australia SSC Enthusiast Points: 127 More actions · Answer 3

Hi

the distribution DB clean up job runs successfully on all 4 servers.

but on the primary server, which was used to initiate P2P replication, the distribution clean up job, doesn't actually delete the transactions from the MSrepl_commands tables.

i tried many suggestion found on the net. But had no luck.. :unsure:

ie: stop the log reader, distribution agent and run the distribution clean up job.

There are no pending transactions to be distributed.

All 4 servers were built and configured with P2P replication at the same time.

The clean up jobs run with the same credentials, and frequency. (ie: every 10 min

With 0 hrs min and 2 hrs max retention)

What am I missing here ??

Any help would be appreciated.

Tnx

HS

noeld SSC Guru Points: 96590 More actions · Answer 4

Something does not sound right. Can you change distribution not to keep rows "temporarily" on the distribution database and try re-running the cleanup job ?

* Noel