MOM 2005 ReliabilityAnalysisReporting task taking a long time to complete?

If you have installed the MOM 2005 Reliability Analysis Reporting pack and found over time the task has taken longer and longer to complete, then it may be time to groom some of the older data from the database.  The pack does have a grooming stored procedure built in, but it does not run automatically.  This stored procedure is MRAS_pcDataDeleteByDate and it takes a single parameter which is a string representing the cut-off point you wish the data to groomed up to.  So if you are going to remove this data then you first need to identify how old the data is, as it is also a good idea to remove the data in relatively small chunks and to monitor the impact this has on the DB.

So first get a value for the oldest data collection by running the following SQL.

USE SystemCenterReporting
select min(dtEnd) as OldestMRASData
from MRAS_Collection mc join mras_sojourn ms
on mc.uidCollectionPeriod = ms.CollectionPeriod

Then run the grooming SP, adding a month to the date you have found, and monitor the impact on the system in terms of performance, disk usage, tempdb growth, etc, and note how long it takes to complete.  You can then decide to adjust the interval you delete with successive runs of the procedure, increasing or decreasing the interval accordingly.  For example, if the oldest datetime returned from the previous query was '2007-02-13 00:00:53.000' then you should run the stored procedure as follows:

EXEC MRAS_pcDataDeleteByDate '2007-03-13 00:00:53.000'

If this goes through quickly with no big impact on the server performance, you may decide the next run will remove 2 months’ worth and run:

EXEC MRAS_pcDataDeleteByDate '2007-05-13 00:00:53.000'

Again you should monitor and increase/decrease the grooming interval as desired, re-running the procedure until you have groomed back the data to the retention period you desire.  Once you have groomed the MRAS data back to an acceptable date you may wish to automate this process in future to save you from continual manual intervention.  You can do this by creating a SQL Agent job to run once a day/week/month, at a time when it won’t clash with other SQL maintenance jobs to run this automatically.  You will need to automatically calculate the datetime for the SQL job and I would recommend something like the following T-SQL:

DECLARE @CutOffDate datetime
Set @CutOffDate = DATEADD(year,-1,GETUTCDATE())
EXEC MRAS_pcDataDeleteByDate @CutOffDate

This line Set @CutOffDate = DATEADD(year,-1,GETUTCDATE()) sets the cut off date to be one year, but if you wished to make this shorter longer then you could do something like this:

Set @CutOffDate = DATEADD(month,-6,GETUTCDATE()) would make it 6 months

or

Set @CutOffDate = DATEADD(month,-18,GETUTCDATE()) would make it 18 months, etc....

Brian McDermott | Escalation Engineer