Manually Deleting Merge Meta Data tables

Scenario: SQL 2000 1 publisher (Merge and Trans), two subscribers.
Problem: Subscription expiration for Merge Publication set to 'never expire'
hence MSMerge_contents = 44million rows. MSmerge_genhistory = 9 million rows

When pushing out to new subscriber this is causing a)snapshot to fail b)in
the rare instance when snapshot works it is causing Merge push to fail as
Subscriber tran log blowing up or timeouts.

Resolution: Need to delete all data upto last 7 days in the metadata tables

Question: how?

What i have been attempting is by gradully reducing the Subscription in
order for the automatic metadatretentioncleanup SP to do its thing (merge
agent set to run every minute). Only problem is that I am currently at day
710 and at this rate I will be hear until 2010. If I decrease by only 5 days
the whole thing takes ages casuing locking and other issues to end users who
directly hit the publisher.

I have investigted this option

http://talatnauman.blogspot.com/2008/06/purging-merge-replication-history.html

to manully delete the data in the two table bit by bit. Any opnions on this,
could it mess up replication?

Many thanks for your answers

I've had to do this from time to time.

I normally do this

set rowcount 5000
GO
while (1=1)
begin
delete MSmerge_contents from MSmerge_contents a, MSmerge_genhistory b where
a.generation = b.generation and a.tablenick=b.art_nick and b.coldate <
@coldate and a.generation > 0
if @@rowcount<5000
break
delete MSmerge_genhistory where coldate < @coldate and pubid is not null
end
GO

On some servers you might want to bump or down up the rowcount

"Schwizzla" wrote in message

> Scenario: SQL 2000 1 publisher (Merge and Trans), two subscribers.
> Problem: Subscription expiration for Merge Publication set to 'never
> expire'
> hence MSMerge_contents = 44million rows. MSmerge_genhistory = 9 million
> rows
>
> When pushing out to new subscriber this is causing a)snapshot to fail b)in
> the rare instance when snapshot works it is causing Merge push to fail as
> Subscriber tran log blowing up or timeouts.
>
> Resolution: Need to delete all data upto last 7 days in the metadata
> tables
>
> Question: how?
>
> What i have been attempting is by gradully reducing the Subscription in
> order for the automatic metadatretentioncleanup SP to do its thing (merge
> agent set to run every minute). Only problem is that I am currently at day
> 710 and at this rate I will be hear until 2010. If I decrease by only 5
> days
> the whole thing takes ages casuing locking and other issues to end users
> who
> directly hit the publisher.
>
> I have investigted this option
>
> http://talatnauman.blogspot.com/2008/06/purging-merge-replication-history.html
>
> to manully delete the data in the two table bit by bit. Any opnions on
> this,
> could it mess up replication?
>
> Many thanks for your answers
>
>
>