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.