When Microsoft states that the OperationsManager database is self-maintaining, it can be confusing to DBAs because there are no SQL agent jobs present on the SQL 2005 instance hosting the Operational database. I thought it would be interesting to share why.
There is a logical explanation for the absence of SQL jobs: Agent jobs have been moved into rules in Operations Manager. That’s right. In this version of Operations Manager, maintenance tasks for the OperationsManager database are actually rules that execute various stored procedures on a scheduled basis. These jobs are stored in the Microsoft System Center Internal management pack (Microsoft.SystemCenter.Internal.mp).
There are pros and cons to this shift to be sure, but I think the most noticable improvement over MOM 2005 is now nobody calls on me for scripts to recreate the SQL agent jobs they accidentally deleted!? Along those lines, I think this also minimizes tinkering that leads to problems in job execution. You can use the execution schedule of these as cues for when you should perform database backups. Performing database backups at a time that does not coincide with maintenance processes is a good thing. Below are listed some of the more interesting rules responsible for various database maintenance?and cleanup processes in the OperationsManager database.
WARNING: These are listed here only for educational purposes. Do NOT touch or modify these rules, and never edit OperationsManager database tables, stored procedures or other structures without the express recommendation of Microsoft.
Rule: Optimize Indexes
- Description: Optimizes indexes in the Operational DB
- Stored Procedure: p_OptimizeIndexes
- Schedule: Daily at 2:30 am
Rule: Partitioning and Grooming
- Description: System rule that runs a workflow to parition and groom the operational database.
- Stored Procedure: p_PartitioningAndGrooming
- Schedule: Daily at 12 midnight
Rule: Alert Auto Resolve Execute All
- Description: System rule that auto resolves alerts.
- Stored Procedure: p_AlertAutoResolveExecuteAll
- Schedule: Daily at 4:00 am
Explanation: In looking at this stored procedure, it has comments denoting SQL statements to auto-resolve green alerts (alerts generated by monitors where the health state has improved), as well as old alerts, which would mean alerts past the grooming theshold in global settings (30 days by default for unhealthy monitors, 7 days for healthy monitors). It actually does this by calling another stored procedure, p_AlertAutoResolve.
Rule: Detect and Fix Object Space Inconsistencies
- Description: System rule that detects and fixes object space inconsistencies in the operational database.
- Stored Procedure: p_DetectAndFixInstanceSpaceInconsistencies
- Schedule: Every 30 minutes
Rule: Discovery Data Grooming
- Description: System rule that grooms discovery data.
- Stored Procedure: p_DataPurging
- Schedule: Daily at 2:00 am
Rule: Maintenance Mode
- Description: System rule that updates maintenance mode data.
- Stored Procedure: p_ScheduledJobsEveryFiveMinutes
- Schedule: Every 5 minutes
Explanation: This is reminiscent of the maintenance mode stored procedure in MOM 2005, and executes every 5 minutes to update maintenance mode status of monitored objects.