How To Solve Error: ‘Partitioning And Grooming Has Not Completed Recently’

Bumped into this error in  an OM12 SP1 environment. The error message Partitioning and grooming has not completed recently kept popping up.

The best way to go about it is to run the Stored Procedure, p_PartitioningAndGrooming, run by the Rule “Partitioning and Grooming” manually against the OpsMgr database. Changes are it will go wrong as well but it will show additional information about the reasons as well.

1. Go to the SQL Server. log on with an account with sufficient permissions and start SQL Server Management Studio;
2. Log on to the SQL DB Engine with an account which has sufficient permissions to run the Stored Procedure against the OpsMgr database;
3. Right-click on the OpsMgr database, select the option New Query. Copy the Stored Procedure p_PartitioningAndGrooming and hit F5
   
4. Within a few minutes the results will come in, reported under the header Messages:
   

In this case as you can see the SP fails because of a transaction log which is loaded. After running a backup of this database the SP ran just fine:

After solving this error an investigation was started why this database wasn’t backed up successfully for a long time…

Recap
Whenever you get the error message Partitioning and grooming has not completed, use this procedure in order to get more detailed error message, enabling you to troubleshoot the issue in a neat manner and avoiding a goose chase.