From the Field- insiders look to MOM 2005 grooming

One of the most important daily tasks for MOM 2005 is the grooming operation, from my experience MOM database can grow very large in a Mid-size network (between 50-75) servers. Many people are stucked in a situation were MOM database was growing so fast and filling the free disk space.
The solution was to add more disk space or free some, the free some phrase means grooming; however some MOM admins found that grooming the MOM database is a simple task usually, however one it comes to small free space it becomes so tricky task.let us take a look to how MOM grooming is processed.
MOM grooming is pure SQL task, scheduled to run in certain times to remove obsolete data, and move the information in the MOM database to MOM reporting database. The default MOM grooming tasks are:
Grooming Job
Definition
MOMX Partitioning And Grooming
Scheduled to run daily at 12 A.M. This job updates the partitioning information and grooms appropriate partitions.
OnePoint-Check Integrity
Scheduled to run once per week, on Saturday at 10 P.M. This job validates the integrity of everything in the MOM database. For example, it checks that the index and data pages are correctly linked and that indexes are in their proper sort order.
OnePoint-Computer Maintenance
Scheduled to run every five minutes. This job checks for computers that should come out of maintenance mode.
OnePoint-Reindex
Scheduled to run once per week, on Sunday at 3 A.M. This job rebuilds the MOM table indexes to improve performance. The OnePoint-Reindex job requires approximately 40 percent of free space within the database to successfully complete. For example, a 10-GB database needs 14 GB of database space to successfully rebuild the indexes. If the jobs fail due to a lack of disk space, the failure will not adversely affect your MOM implementation.
OnePoint-TodayStatisticsUpdateComputersAndAlerts
Scheduled to run every five minutes. This job updates the OnePoint Operations Manager Today window.
OnePoint-TodayStatisticsUpdateEvents
Scheduled to run every 30 minutes. This job updates the OnePoint Operations Manager Today window.
OnePoint-TodayStatisticsUpdatePerfmonRulesKB
Scheduled to run every hour. This job updates the performance, rules and Management Pack knowledgebase.
OnePoint-Update Database
Scheduled to run every hour. This job grooms automatically resolved alerts based on the information specified in the Global Settings dialog box.
OnePoint-Update Statistics
Scheduled to run once per day at 1 A.M. This job updates information about key value distribution in the database to improve performance.

the table above illustrates every scheduled task in the SQL server to maintain the MOM database, the most important tasks is the first and latest ones because they work in conjunction with MOM transfer job that transfers the MOM data from the MOM database to the reporting database. It is important to update the MOM database because you should auto resolve and update computer status and alerts based on the resolution state and monitoring data. The tricky part here is that you have to successfully transfer the Data from MOM database to reporting database or the grooming will not run.
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 you don’t have the enough free disk space the transfer job will not be able to transfer the data from the MOM database, then the grooming will not occur, then you will run out of disk space!!!!.
How to come over this?!
Well, if you have additional Hard disk then you can the hard disk and configure a second data file and log file for the MOM database on that hard and run the process again, or detach and move the database and logs to the new hard disk as following:
Start (SQL) Enterprise Manager
· Expand SQL Server SQL Server Group Local Databases
· Expand “Onepoint” database
· Right-click on Database Onepoint
· Select all tasks
· Select detach database
· Select Clear to clear connections
· Select OK to message regarding transaction
· Select Yes to notify connection users
· Select OK to detach file
· Select OK to message “successfully detached”
· Close SQL Enterprise Manager
· Use Cut/Paste to move the file “old location”:\MOMdata\onepoint.mdf and LDF file to “new location”:\momdata\onepoint.mdf and LDF location
· From Query Analyzer
· Click eraser to clear query window statements; type Use onepoint Go Exec sp_attach_db ‘onepoint’, ‘“new location”:\momdata\onepoint.mdf’, ‘“new location”:\momdata\onepoint.ldf’ Go
· Click green arrow to execute
· Click OK on Error (code 911) – No entry found

But some companies don’t have the ability to afford a new hard disk specially if you run the MOM on a servers (HP, IBM..Etc). So what is the solution?
Usually I suggest staring from the Reporting database, why?, the answer is simple because the MOM reporting database is configured to save the data for 18 month, so most of the companies don’t need reports that belongs to 18 month ago, and if you will run out of disk space you will definitely need to fine tune this setting.
You can configure the reporting database to keep the data for example 6 month and groom the database, this will give you a great amount of free space. But you need to watch out again because grooming will cause the some logs which again requires disk space, so you will have to groom the reporting database in steps (30 days in each step for example) until you reach the required time limit.
Follow the below steps to groom the reporting database:
to configure the grooming date for the reporting database:

Declare @Groomdays int--Retain data for 300 daysSelect @Groomdays=300exec p_updateGroomDays 'SC_SampledNumericDataFact_Table', @Groomdaysexec p_updateGroomDays 'SC_AlertFact_Table', @Groomdaysexec p_updateGroomDays 'SC_EventParameterFact_Table', @Groomdaysexec p_updateGroomDays 'SC_AlertToEventFact_Table', @Groomdaysexec p_updateGroomDays 'SC_EventFact_Table', @Groomdaysexec p_updateGroomDays 'SC_AlertHistoryFact_Table', @Groomdays
in the query analyzer:

dbcc shrinkfile (“reporting DB file name”, 500 ) --use what ever size you need.
go
dbcc shrinkfile (“reporting DB log name”, 500 ) --use what ever size you need.
go
Repeat the above 2 tasks until you gain the desired free space to run the transfer job. Once you have the required free space, run the transfer job from the scheduled tasks in the control panel.
Once you have transferred the data from the MOM database you can groom them using the MOMX Partitioning And Grooming scheduled job from he SQL Enterprise Manager.
Mitigation:
Well the first step to mitigate this issue, is the successful sizing of the MOM server disks, add disks with adequate space to ease your life later.
Then monitor the SQL tasks and their failure cause, and don’t allow the logs and database to grow automatically, this will kill your hard disk free space.
to count the alerts that is waiting to be transferred:

SELECT Count(*) as alerts FROM dbo.Alert WHERE (ResolutionState=255)
It is easy to aid this issue in the beginning but it will be so hard to fix it later.