Mom 2005 Database Migration Migraine
Prior to MMS 2008 we migrated our production MOM 2005 environment to a new SQL 2005 cluster. A week and a half after the migration we noticed that our nightly maintenance mode scripts were starting to fail. After a little investigation we noticed that our onepoint db had grown in size to over 30 gigs. We try and keep it around 10 to 12 gigs. A quick glance at the SQL report Disk Usage by top tables it was easy to see that the perf data was not getting groomed out of the onepoint database.
I checked the Scheduled task log and notice that the majority of the entries were marked as successful. I checked the event log for event id 80 and reviewed the complete dts job listing which showed everything as functioning properly too. Well now time to check the OnePoint DB ReportSettings table for the TimeDTSLastRan value. It was showing a date from when the db was still on the old cluster. We manually changed the date time value for TimeDTSLastRan, and then manually kicked off MOMX Partitioning and Grooming, which did its job of pruning out the old perf tables from the onepoint db.
The question remained, why isn’t the TimeDTSLastRan value getting updated? The stored procedure that is run to update the time date stamp on the TimeDTSLastRan field of the ReportSettings table in the onepoint db is:
p_UpdateDTSLastRunTime 'May 11 2008 3:13:30:284AM', 'SQLHOSTNAME\INSTANCE#'
The value 'SQLHOSTNAME\INSTANCE#' needs to match the same value in the onepoint db reportingsettings table column datawarehouseDataSource. For some reason our datawarehouseDataSource value was still the name of the old SQL server cluster. After updating this value to the current SQLHOSTNAME\INSTNACE# everything started working as expected.
Specail shout out to Steve on this one!