O R G A N I C / F E R T I L I Z E R: problems with MOMma

May 3, 2005

problems with MOMma

update: this information has been posted to an article on myitforum.com. Since implementation, it seems like the database has done nothing but grow, grow, grow. I've blamed the Exchange guys relentlessly for having a very noisy environment. No matter how many times I ran the MOMX Partitioning and Grooming job, the database would not free up any space. It turns out there are some mechanisms tied directly into grooming if you have a MOM warehouse enabled.
Here's the details. If you want to know the last time your DTS job completed successfully, you can comb through the event log on the reporting server or you can issue this command to your OnePoint database:
select * from ReportingSettings
The first column labeled TimeDTSLastRan indicates the last successful marker. Turns out if this isn't current, your grooming jobs aren't doing anything. Mine was set to the end of February. Hmmm. That'd explain the obscene growth pattern. I've run the job 5 times using the latency switch. The time stamp hasn't moved.
By the way, the job is scheduled on the reporting server. It's executed as something like this:

MOM.Datawarehousing.DTSPackageGenerator.exe /latency:20 /srcserver:OnePointDBServer /srcdb:OnePoint /dwserver:WarehouseServer /dwdb:SystemCenterReporting /product:"Microsoft Operations Manager”.

It's in the %ProgramFiles%\Microsoft System Center Reporting directory.
If you notice, there's a /latency switch. This let's you specifies what items to transfer to the warehouse. For example, 20 means anything older than 20 days old. This is useful if your DTS job is timing out because of an exorbitantly large amount of data being transferred - potentially overwhelming the transaction log, etc. Also, there's a /silent switch that you're supposed to use when issued as a scheduled task. I pulled it out to see what this job was doing exactly. In the event of a successful execution, you should see an event message like this:

The execution of the following DTS Package succeeded: Package Name: SC_Inner_DTS_Package Package Description: This package transfers data from datafoo\foo.OnePoint to foo.SystemCenterReporting Package ID: {481AA51A-8C84-42E3-9879-D228290895D0} Package Version: {24A473AA-4C8A-486B-9ED4-970D35A70047} Package Execution Lineage: {55B111CE-72ED-4231-821B-AAE321763EC5}

Well, after going through many latency switches and kicking off the groom jobs (MOMX Partitioning and Grooming), I was able to get the 15 GB DB back down to 5 GB. Interesting though, the time stamp still hasn't changed. Hmmm...