Based on review of the sp_MShistory_cleanup stored procedure from a replicating SQL Server 7.0 database server we recently inherited, it appears that sp_MShistory_cleanup does not housekeep the msdb..sysreplicationalerts table.
As such, it appears that our msdb..sysreplicationalerts table has been growing constantly since 2002, seemingly mandating gradual changes in database file size limits ever since!
We would like to try and limit the effects of this by proactively removing legacy records on a regular basis. With this in mind, can anyone advise where/how housekeeping of msdb..sysreplicationalerts is supposed to take place in SQL Server 7.0 so that we can determine why it has not been occurring?
If there is no out of the box mechanism to do this, would it be appropriate for us to manually delete from this table e.g. delete from msdb..sysreplicationalerts where time <= @cutoff_time (where @cutoff_time would be based on the history_retention setting used by sp_MShistory_cleanup) or is this practice not to be recommended?Thanks in advance.
I never touched replication on SQL 7 so this is all conjecture but if I had to deal with this I would build a dev rig of your replicated environment and then manually clean up the offending content and see if replication keeps going. If it does, and make sure it does for a long time like maybe even a month, then you 'should' be ok to do the same to live. However, if it goes up in a ball of flames then you know not to touch live.Is there any chance to remove and re-apply replication? This would reset everything. you could also shoe-horn an upgrade of the SQL version too??!!
answered Feb 04, 2011 at 05:56 AM
This is a great opportunity to run upgrade adviser against this database (if it will) and look to upgrade. To answer your question I would think you could purge the old history out just like you can backup history.
answered Feb 04, 2011 at 06:32 AM
Sorry, I have not had an opportunity to respond to your valuable contributions to date...
Re- dev rig usage and testing, yes this would be the preferred approach but we simply do not have the facilities to progress this...nor do we have the luxury unfortunately of trialling this online!
Re- upgrade, we are currently looking to upgrade but as a completely parallel activity i.e. new hardware, OS etc, but this is some way off for other reasons. Sadly, we would not be given sanction to upgrade in-situ.
As you can see, our hands as fairly tightly bound on this at the moment, hence the optimistic request for information from this forum.Thanks for your suggestions to date.
answered Feb 16, 2011 at 04:05 AM