SQL query to find out if your SCOM 2012 R2 Grooming jobs are succeeding

SQL query to find out if your SCOM 2012 R2 Grooming jobs are succeeding:

• Grooming jobs are very important in SCOM.
• If grooming fails then your old data is not removed as per your grooming settings.
• This will cause your database to fillup and also cause slowness.
• You need to regularly monitor the Grooming jobs are will face a outage.
• Unfortunately, SCOM only raise alert if the grooming fails for atleast 6 days.
• So by this time depending on your environment there could be lot of issues  and grooming could never be completd
• So i came up with SQL query which you can use to daily check after 6:00 AM server time
• "SELECT * FROM INTERNAJOBHISTORY ORDER BY INTERALJOBHISTORYID DESC"
• Run the above query and check if the grooming jobs succeeds. You should do this daily to avoid our OperationsManager Database gets filled.

SCOM 2012 R2 giving error Operations Manager Database Grooming not compelted Succesfully

SCOM 2012 R2 giving error Operations Manager Database Grooming not compelted Succesfully:

 

• You might get this error in your SCOM console and not sure what to do, lets understand what this alert means and work from there fixing this.
• So when you get this alert, first go and check your grooming settings to see how old alerts can be in SCOM operatoins Database, default is 7 days.
• Then create a view to filter alerts more than 7 days in closed state
• If you see more than 10000 alerts then your grooming is not working.
• Ok now how to fix it.Lets login to to the Operations Manager Database
• Open the query Window and run SELECT * FROM interaljobhistory order by interaljobhistoryid DESC.
• You can see 4 jobs with daily date updated, it will have a 0 or 1 net to it. If its 1 then its success.If its 0 then its failed.
• Now if the failed are more than1 then open the query windows and the run the Grooming Job manually EXEC p_groomingandPartitioning
• It will take long time some time close to 8 hours. If it succeeds then run the SELECT * FROM interaljobhistory order by interaljobhistoryid DESC and see if it succeded.
• If it fails the erro will tell you what the issue is. If its transaction log full error, then take a backup of your transaction logs and then run the above job.
• If the error is temb db full , then increase the space in temp db and try running the query again.
• If the error is LOCK error, then stop all the SCOM services on the managment server and then run the query and wait till it successeds.
• You also try changing the grooming settings .increase the number of days from 7 to 30 for all config in grooming settings and then run the job. If it succeds, the start reducing the days 1 day at a time and keep doing till it reaches optimum level.
• Another technique is to run just the failed jobs that you seel when you run the select query and see if it succeds.