asked
How to housekeep msdb..sysreplicationalerts in SQL Server 7.0?
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.
Wow. Best of luck on having to maintain a SQL Server 7 database. I honestly couldn't help you at all. I do know someone who maintains these. I'll see if I can get him to take a look at your question.
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??!!
I support @Fatherjack's suggestion of testing, which is probably not really possible in this situation. However, the table you are talking about stores information that should be deletable. I suggest you look to see if there is any relation that could be broken by cleaning up. If not, it can't really hurt. Try cleaning up some alerts from 2002 only and see if it cries - I seriously doubt it though as it is an informational table rather than a "driving" table for replication.
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.
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.
just a thought - can you virtualise the server? you could then do your worst and simply rollback to an image that is known to be good.
I feel its a thing with forum answers - noone will come along and say "yeah, go for it, it always works with no errors and the unicorns put on a show for you afterwards". Something this far back is beyond others trying it for you on their test rig. Unless you can find reference to it being done somewhere then its a case of assessing the risk vs benefits and whether to wait for upgrade to make it irrelevant
I feel its a thing with forum answers - noone will come along and say "yeah, go for it, it always works with no errors and the unicorns put on a show for you afterwards". Something this far back is beyond others trying it for you on their test rig. Unless you can find reference to it being done somewhere then its a case of assessing the risk vs benefits and whether to wait for upgrade to make it irrelevant
Nice thought. Virtualisation, is currently a possible option (although nothing concrete has been agreed/progressed in that direction). The SQL Server is installed on a resilient, clustered Windows NT Server platform, which in itself will probably have compatibility issues to overcome in a virtual environment! Re- forum answer expectations, yes this is a blind chance attempt to see if there is anyone out there who "just might have done this in the past"! Thanks again.
