Re: Distribution Data File Growth

Results from msrepl_commands table:

publisher db_id count (xact_seqno)
1 20867
2 1002769
3 159454

Ran distribution clean up agent job (has been running successfully, every 10
minutes):

publisher db_id count (xact_seqno)
1 20866
2 996394
3 158467

The counts are all lower. I added an output file to the job which states:
Removed 74 replicated transactions consisting of 245 statements in 30
seconds (10 rows/sec). Retention max looks to be set at 72 hours (default, I
assume - I don't think that we changed this in the old system).

I'll just keep monitoring this for now. Maybe a 1 GB data file for this
distribution database is not out of line and I no longer have access to the
old system to compare anything.

Thanks,

Michelle






"Paul Ibison" <Paul.Ibison@xxxxxxxxxxxxx> wrote in message
news:uWKIq2SQFHA.248@xxxxxxxxxxxxxxxxxxxxxxx
> Michelle,
> have a look at the msrepl_commands table and see if this is the cause of
the
> large size. If it is, it could be that you have a subscriber who hasn't
> synchronized in a while, or the distribution cleanup agent is disabled, or
> you have an anonymous subscriber, so the commands remain until the
retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>


.

Relevant Pages

  • Re: Disaster Recovery in Merge Replication
    ... You will have some success if you have recent backups for the msdb, master, distribution, and publication databases, and you restore them on a sql server with the same name as long as the old server is offline. ... If you enable a new publisher you can create new subscriptions using the no-sync option and then use sp_addtabletocontents to push data from the subscribers to the publisher. ...
    (microsoft.public.sqlserver.replication)
  • Error inserting into MSreplication_subscriptions table
    ... I have created a pull subscription on sql server 2000. ... distribution agent: ?Agent message code 20046. ... Microsoft SQL Server Replication Agent: ...
    (microsoft.public.sqlserver.replication)
  • Re: Moving Distribution mdf/ldf (maintain logical drive/path) ?
    ... you can't do with the distribution database or published databases. ... Looking for a SQL Server replication book? ... respectively are now logical drives on top of a hardware RAID array so ...
    (microsoft.public.sqlserver.replication)
  • Re: Server SQLTEST1 is not prepared to run the offloaded agent
    ... By default the distributor will be on the server which you created it on, ... If you are using a pull subscription you will have to enable the subscriber ... Your publisher by default will use the distribution database it ...
    (microsoft.public.sqlserver.replication)
  • Re: Cannot add publication after applying service pack 1
    ... that points to the distribution database, ... Looking for a SQL Server replication book? ... Under the Object Explorer of SQL Server Management Studio, ...
    (microsoft.public.sqlserver.replication)