Distribution Data File Growth

Recently rebuilt Windows 2003 OS, SQL Server 2000 sp3, 3 publishers of
various shapes and sizes.
I am almost certain that I re-created the distribution database using the
file properties before everything was moved to the new OS. I ended up having
to drop and recreate replication because I didn't back up the publishers
with the keep_replication switch. So, I dropped the distribution database
and created a new one. But, for some reason the data file seems to be
growing and growing. This behavior is unexpected. How can I determine the
cause of this growth? The subscribers are certainly receiving the
transactions. We have other sql servers with multiple publishers and a
single distribution database but the data file for it stays small.
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)
|||run a dbcc opentran in your distributon database to see what happens.
The keep replication switch was designed to be enable disaster recovery
of your transactional replication solution. You can use it to restore
publications on a server but only so you can script out the
publications or view them. Don't expect to use the keep_replication
swithc on a new server and have everything work. You need to restore
the distribution, master, and msdb databases as well.
|||Thanks. I had all of the pieces (msdb, distribution, master, etc.). All
databases were restored but since I didn't back up the publishers with the
keep_replication switch, I couldn't get replication 'kicked off' again. I
re-marked them after the restore for replication and all of the jobs were
succeeding. However, the log reader was not finding any transactions.
No open transactions in distribution. I'll see where Paul's suggestion leads
me...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113509425.813891.157720@.z14g2000cwz.googlegr oups.com...
> run a dbcc opentran in your distributon database to see what happens.
> The keep replication switch was designed to be enable disaster recovery
> of your transactional replication solution. You can use it to restore
> publications on a server but only so you can script out the
> publications or view them. Don't expect to use the keep_replication
> swithc on a new server and have everything work. You need to restore
> the distribution, master, and msdb databases as well.
>
|||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@.Pygmalion.Com> wrote in message
news:uWKIq2SQFHA.248@.TK2MSFTNGP15.phx.gbl...
> 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)
>
|||Well over a million records is quite a lot, but I'd be surprised if this
amounts to 1GB. Running, sp_spaceused will give the exact ratio of empty to
used space in the database. If the subscriber(s) have synchronized and are
up to date, you could reduce the retention period and run the cleanup agent
to remove a big chunk of this data but this will only work if anonymous
subscribers aren't enabled.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)