Issue with Merge Replication - Purge msmerge_genhistory?

[CFPR]

I am running SQL 2000 SP3a on a Windows 2000 Server Sp4. The publisher and subscriber server are identical. The problem is that the msmerge_genhistory has gotten put of hand on record size. I notice that it also has data from over 9 month when I only need like 3-4 days.
Can I delete the record based on coldate?
any store procedure to do this?
any specialized system tuning?

Thanks to all

MW

 

[JohnBates]

hi MWilliams123,

I have used merge replication. This is what I would do:

1. Stop Merge Agent on the publisher
2. execute a complete db backup job

3. try the DBCC command...
DBCC SHRINKFILE ms_genhistory
4. DBCC SHRINKDB your db name
5. Start Merge Agent on the publisher

6. Run a Snapshot at the publisher - this will get the 2 databases/servers in-sync again.

Some of the syntax may be wrong, but you get the idea.

Just be very careful messing with the system tables.
HTH, John

 

[JohnBates]

... or you might first just do this:
Backup db
then run the SHRINKDB step as above.

Check size of your db. If much smaller, then schedule a weekly shrinkdb job and do nothing else.

John

 

[CFPR]

Hi John. Thansk for the response. My only 'serious' problem is the msmerge_genhistory which has data of over 9 months and over 450k transactions. I have read that 'some' agent is supposed to maintain this table and keep history by default for only 14 days. It is actually based on a field called 'coldate'. Can I just delete all record where coldate <= date()-14?

MW

 

[JohnBates]

MWilliams,

"DBCC SHRINKFILE ms_genhistory" should have said "DBCC SHRINKFILE msmerge_genhistory"


"Can I just delete all record where coldate <= date()-14?"

I would NOT recommend delete records manually from any system table. You could make the db or at best the merge functionality stop working altogether.


let us know how it goes.
John

 

[JohnBates]

MWilliams123,

OK, now that I've had more time to think about it I remember steps I had to take when faced with your situation:

With merge replication, you need to do some things on a weekly basis (my recommendation) as a part of database maintenance:

Reindex the merge-related system tables:

(you can run these in the Query Analyzer window initially of course)

- DBCC DBREINDEX('MSMerge_contents')
- DBCC DBREINDEX('MSMerge_genhistory')
- DBCC DBREINDEX('MSMerge_tombstone')

Follow-up with DBCC SHRINKDB('your db name') when the db is not being used

Additionally, read-up on a system stored procedure called 'sp_mergecleanupmetadata' in Books On Line.
Your db probably needs to have that ran... it too will minimize the size of MSmerge_genhistory.

let us know how it goes, OK?
John