more info on mom grooming

don't worry... i plan to move all this to an article at some point in the future. :)

Anyway, going through the Ops Guide, chapter 4 outlines some other things you can do with the SystemCenterReportingDB to help shape the amount of data you want to retain in MOM 2005.

Regarding the Latency switch, look for the title “Moving a Large Amount of Data using DTS Latency” around page 30. BTW, I was given a new table to query for the LastDTSRunTime timestamp. I updated my previous post to reflect it. Anyway, interesting note here that explains it all:

The grooming for the MOM Database uses information in the Reporting DTS job to prevent the grooming from removing data that has not been transferred to the Reporting database. If the DTS job fails, MOM will not groom the MOM Database for the full 60 days, to avoid removing data that has not been transferred to the Reporting database.

So there you go. Now, there's some really interesting stuff on the “Grooming” section. Evidently MOM Warehouse grooms itself but on a time frame of 395 days (or 13 months). Thankfully this can be changed pretty easily by issuing this command:

exec p_updategroomdays 'TableName', DaysToRetainData

 

At any rate, you got to run this against all six tables if you want to modify them all. TableName and DaysToRetainData are variables of course. Here's a SQL script that Clive wrote to help address this:

-- Update the Datawarehouse Groom settings 
Declare @Groomdays int 

-- Retain data for 180 days 
Select @Groomdays=180
 
exec p_updateGroomDays 'SC_SampledNumericDataFact_Table', @Groomdays 
exec p_updateGroomDays 'SC_AlertFact_Table', @Groomdays 
exec p_updateGroomDays 'SC_EventParameterFact_Table', @Groomdays 
exec p_updateGroomDays 'SC_AlertToEventFact_Table', @Groomdays 
exec p_updateGroomDays 'SC_EventFact_Table', @Groomdays 
exec p_updateGroomDays 'SC_AlertHistoryFact_Table', @Groomdays 

Just change the @Groomdays to something other than 180 if you want a different date set. Anyway, I found it on this post: http://www.momcommunity.com/ShowPost.aspx?PostID=83.

---

marcusoh.blogspot.com