• RML51, the transaction retention period is used by the distribution cleanup agent to mark subscriptions as inactive if they are further behind than the retention period and to remove transactions that are older than the specified period. How long commands and transactions are retained after they've been delivered to all subscribers depends on the immediate_sync setting for the publication. If TRUE, transactions will be retained until they are older than the retention period; if FALSE they will be removed by the distribution cleanup agent the next time it runs after the transaction has been delivered to all subscribers.

    Toby, I agree with you - David did a great job explaining the how but not the why. The way I see it, you're doing the same amount of IO whether or not you've got one distribution DB or ten. By setting up multiple distribution DBs you may even be adding IO overhead by making your disk heads jump around from one DB to another to read\write transactions and commands. Maybe I can see the logic in creating multiple distribution DBs if you are putting them on separate physical drives...but if you've got those drives available I'd just keep on distribution DB and spread it across as many spindles as you can to begin with.

    When it comes to replication the idea is to keep the distribution database lean and mean. If you've got distribution DB growth issues I'd look at identifying the root cause first. Make sure immedite_sync is set to FALSE and that your distribution cleanup agent is running.

    Chris Skorlinski from the Microsoft SQL Server Replication Support Team has two great articles about distribution DB growth that I recommend reading before considering the more invasive approach of creating multiple distribution DBs:

    How to resolve when Distribution Database is growing huge (+25gig)

    How Replication setting Immediate_sync may cause Transactional Replication Distribution database growth

    FWIW I asked Chris about the author's suggestion and he indicated that he's never had a case where he found it necessary to create multiple distribution DBs to alleviate performance issues.

    Kendal Van Dyke
    http://kendalvandyke.blogspot.com/[/url]