Remove SQL DB Files, FileGroups, and Log files from SCOM

When using the “Version Agnostic” Microsoft SQL Server Management Pack, one of the changes made was in how we monitor Database and Log free space.  In the older version specific management packs, we were required to discover the DB Filegroups, DB files, and DB log files, and apply a complex free-space rollup design.  This has changed and now all monitoring targets the SQL Database objects.

However, we still *discovered* all the DB files, DB FileGroups, and DB log files in the early Agnostic SQL MP’s.  In version 7.0.32.0 and later, these discoveries are now disabled.

If you build a brand new SCOM management group and start with 7.0.32.0 or later, you can ignore this article.  Those discoveries are disabled by default starting with that version.

However, if you ever had an older version of the SQL MP imported, you likely still have lots of these instances hanging around doing nothing, even after upgrading to the latest SQL MP.  This creates a large number of discovered objects that consume precious instance space, and consume resources on both agents on the management servers.  In a recent customer review, I saw we had many thousands of these unused objects, actually consuming a very large percentage of the total objects in SCOM:

Almost 50,000 objects that have NO monitoring in SCOM!  We need to get rid of these.

They show up in Discovered Inventory like below, in a “Not Monitored” status because there are no monitors enabled targeting these:

There is absolutely no reason to discover something in SCOM, if we do not require it for monitoring.  Monitoring MP’s should be designed to be much simpler than they are, which makes them work better and become simpler to understand and support.

To correct this issue, and reduce the instance space in SCOM – we simply need to disable two discoveries.  In the SCOM console, navigate to Authoring > Object Discoveries.  Either scope for “MSSQL on Windows: DB Filegroup” and “MSSQL on Windows: DB log File” or search for the two discoveries by name:   “MSSQL on Windows: Discover SQL Server DB Filegroups” and “MSSQL on Windows: Discover SQL Server Transaction Log File”.  Create overrides disabling these two discoveries:

Once you save these overrides – we will no longer discover these objects in the future.  HOWEVER – we need to run a command to clean up/delete the previous objects.  That’s where we will run Remove-SCOMDisabledClassInstance which I recently wrote about:  Demystifying Remove-SCOMDisabledClassInstance – Kevin Holman’s Blog

If this is trying to delete thousands of objects, it will likely fail to complete the first time.  See the article above on how to run it in a loop, or just keep running it.

You can check out your discovered inventory and see these are all gone now.

On a regular basis you should examine top discovered objects in SCOM.  I like to run this SQL query in all my customers to evaluate what are their top discovered objects by count.  Bad management packs can discover massive numbers of objects and it is good to evaluate this.  I use the following SQL query:

--Start Query
--Instances By Type
SELECT mt.TypeName, COUNT(*) AS NumEntitiesByType 
FROM BaseManagedEntity bme WITH(NOLOCK) 
LEFT JOIN ManagedType mt WITH(NOLOCK) ON mt.ManagedTypeID = bme.BaseManagedTypeID 
WHERE bme.IsDeleted = 0 
GROUP BY mt.TypeName 
ORDER BY COUNT(*) DESC
--End Query

A typical healthy environment will show SQL databases, Windows Disks, Network Adapters, Computers, Operating Systems.  If yours has a LOT of something, you might want to better understand why so many objects are being discovered.  The more objects, the more instance space, the more state calculations, etc.  This can have a very large load impact and slow SCOM down.  In the customer example above, they had 49,000 discovered SQL objects, while their entire management group was 242,000 objects.  This was consuming 20% of their instance space and providing ZERO monitoring benefit.

Keep it simple, MP Authors!!!