SCOM does not properly groom all MT*_Log tables

UPDATE: This issue has been resolved in SCOM 2012 R2 UR7.

---

I was asked to look at a very large OperationsManager database. One of the MT*_Log tables had over 53 million rows. This lead me to a bug in one of SCOM's grooming stored procedures.

Background

Whenever you discover a class instance in SCOM it is added to the appropriate MT_ table, along with the discovered properties. It is also added to the matching MT_Log table with all of the Pre columns set to NULL. If the properties of a discovered class instance change the MT_ table is updated to reflect the current values and the MT_Log table for that class is updated with the Pre and Post columns for each property set to the old and new values.

When there are class instances with properties that do not change, or very rarely change, these MT*_Log tables are small, and tie back to the EntityChangeLog table. Once an EntityChangeLogId is deleted from the EntityChangeLog table, any rows with the now deleted EntityChangeLogId may be deleted from the MT*_Log tables.

There is a stored procedure called [dbo].[p_GroomTypeSpecificLogTables] that loops through the MT*_Log tables and grooms them.

Problem

The [dbo].[p_GroomTypeSpecificLogTables] has a mistake. The ASCII value for the "$" character is 36. The ASCII value for the "_" character is 95. This simple bit of TSQL will illustrate the problem.

IF ('A$B$C' > 'A$B_Log')
  PRINT 'Yes'
ELSE
  PRINT 'No'

IF ('A$D$C' > 'A$B_Log')
  PRINT 'Yes'
ELSE
  PRINT 'No'

I ran into this problem with the SQL Server 2012 Analysis Services Management Pack. My MT_Microsoft$SQLServer$2012$AnalysisServices$MultidimensionalPartition_Log table is over 1.3 million rows.

If you run this query you see the MT_ tables for that MP

SELECT ManagedTypeTableName FROM ManagedType where ManagedTypeTableName LIKE 'MT_Microsoft$SQLServer$2012$Analysis%'

MT_Microsoft$SQLServer$2012$AnalysisServices
MT_Microsoft$SQLServer$2012$AnalysisServices$MultidimensionalPartition
MT_Microsoft$SQLServer$2012$AnalysisServices$PowerPivotInstance
MT_Microsoft$SQLServer$2012$AnalysisServices$TabularInstance
MT_Microsoft$SQLServer$2012$AnalysisServices$TabularDatabase
MT_Microsoft$SQLServer$2012$AnalysisServices$InstanceGroup
MT_Microsoft$SQLServer$2012$AnalysisServices$Seed
MT_Microsoft$SQLServer$2012$AnalysisServices$MultidimensionalInstance
MT_Microsoft$SQLServer$2012$AnalysisServices$MultidimensionalDatabase

But if you use the grooming stored procedures logic, after you groom the first log, you skip the rest of the managed type logs. Try this:

SELECT * FROM ManagedType where ManagedTypeTableName LIKE 'MT_Microsoft$SQLServer$2012$Analysis%'
AND ManagedTypeTableName > 'MT_Microsoft$SQLServer$2012$AnalysisServices_Log'

Workaround

WARNING: Editing the SCOM DB by hand is not supported. Do this at your own risk. Microsoft does not officially suggest doing anything like this. You get the idea...

If you script [dbo].[p_GroomTypeSpecificLogTables] as "Alter To" look at lines 73-78. It should look like this:

-- Get next table to groom.
SELECT TOP 1 @TableName = MT.ManagedTypeTableName
FROM ManagedType MT
WHERE MT.ManagedTypeTableName IS NOT NULL
AND MT.ManagedTypeTableName > @LogTableName
ORDER BY MT.ManagedTypeTableName

The problem is @LogTableName ends with "_Log" and the "_" character has an ASCII value higher than "$" and also higher than any capital letter.

If you alter line 77 of the stored procedure to use @TableName instead of @LogTableName, the MT_Log tables that were not groomed before will groom during the next scheduled run.

-- Get next table to groom.
SELECT TOP 1 @TableName = MT.ManagedTypeTableName
FROM ManagedType MT
WHERE MT.ManagedTypeTableName IS NOT NULL
AND MT.ManagedTypeTableName > @TableName
ORDER BY MT.ManagedTypeTableName