HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.replication » MSrepl_commands HUGE! Cause expiration? Clean up does not clean up
Topic: MSrepl_commands HUGE! Cause expiration? Clean up does not clean up
History:
I have trans replication from SVR1 via DIST1 to SUB1 and SUB2 ,its
production on about a 500 GB database, 7 separate publications.
Latency is excellent, rows show up immediately. Transaction Retention
3 - 6 days, History retention 11 days
1) MSrepl_commands have about 57 million rows and growing
MSrepl_transaction has about 3.5 million rows and growing
2) EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 30,
@max_distretention = 60, runs in SQL Agent on the distributor with the
command UPDATE showing in Activity Monitor. Been running for days now
with same command showing. As I said, MSrepl_ tables are not being
affected, just growing.
3) MSrepl_commands never gets smaller
5) Transaction are indeed replicating - row counts match just fine
6) I've never done a validate subscription
7) Servers are Quad XEONs with 128GB RAM (not kidding) x64 SQL SP 2
5 days ago I had to resnapshot everything because subscriptions were
marked inactive/expired, which I have no idea why since all
Publications constantly have changes and subscribers are always
available. Out of paranoia, set the retention periods to something
insane like 15 days, however I since changed retention times as shown
above.
PROBLEM | CONCERN
1) MS_replCommands and Transactions having so much data
2) The cleanup job just sitting on the UPDATE command in Activity
Monitor and not doing its job
2) Subscriptions expiring again ( I actually disabled the PRIOR to
that incident Expiration Agent job ), last time I started at 9PM and
got to sleep at 5AM before it all completed.
3) I've seen posting about SP2 breaking that replication sp's for
clean up.. any validity to this?
Thank to all for your help, I'm not a happy dba today.
Erik
Replies below ↓
Replies
Re: MSrepl_commands HUGE! Cause expiration? Clean up does not clean up
I am not sure if this is something to worry about in your topology. It all
depends on how much data you are pushing through your system and if the
hardware is adequate for it.
For example on some systems I maintain I only turn the clean up agent on at
night. You need to evaluate how your agents are running - for example your
log reader agent and your distirbution agent. With proper indexing and
updated statistics they should be running optimially no matter how much data
is in your distirbution database.
However why are you running with min dist retention of 30? Most people set
this to 0.
The most common reasons why databases expire is because the history
retention does not match the command retention.
IIRC most of the reports about using the cleanup scripts for Sp1 are related
to p2p. You might want to modify them to use 5000 rows instead of the
default of 2000 IIRC.
<info@onlyhd.tv> wrote in message
news:1f61dd96-02d5-4c21-9ff4-bbffe3a84e80@b5g2000pri.googlegroups.com...
> History:
>
> I have trans replication from SVR1 via DIST1 to SUB1 and SUB2 ,its
> production on about a 500 GB database, 7 separate publications.
> Latency is excellent, rows show up immediately. Transaction Retention
> 3 - 6 days, History retention 11 days
>
> 1) MSrepl_commands have about 57 million rows and growing
> MSrepl_transaction has about 3.5 million rows and growing
>
> 2) EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 30,
> @max_distretention = 60, runs in SQL Agent on the distributor with the
> command UPDATE showing in Activity Monitor. Been running for days now
> with same command showing. As I said, MSrepl_ tables are not being
> affected, just growing.
>
> 3) MSrepl_commands never gets smaller
>
> 5) Transaction are indeed replicating - row counts match just fine
>
> 6) I've never done a validate subscription
>
> 7) Servers are Quad XEONs with 128GB RAM (not kidding) x64 SQL SP 2
>
> 5 days ago I had to resnapshot everything because subscriptions were
> marked inactive/expired, which I have no idea why since all
> Publications constantly have changes and subscribers are always
> available. Out of paranoia, set the retention periods to something
> insane like 15 days, however I since changed retention times as shown
> above.
>
> PROBLEM | CONCERN
> 1) MS_replCommands and Transactions having so much data
> 2) The cleanup job just sitting on the UPDATE command in Activity
> Monitor and not doing its job
> 2) Subscriptions expiring again ( I actually disabled the PRIOR to
> that incident Expiration Agent job ), last time I started at 9PM and
> got to sleep at 5AM before it all completed.
> 3) I've seen posting about SP2 breaking that replication sp's for
> clean up.. any validity to this?
>
> Thank to all for your help, I'm not a happy dba today.
>
> Erik
>
>
>