Menu Close

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

image

 

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:

image

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:

image

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:

image

image

image

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.

image

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!!!

5 Comments

  1. Andrew T

    There is a monitor that’s targeted at MSSQL on Windows: DB Log file, called DB Log File Free Space Left, that seems to be disabled by default. There’s another monitor targeted at MSSQL on Windows: Database called LOG Free Space Left (enabled by default). For DB file, there’s also ROWS Data Free Space left, also targeted at MSSQL on Windows: Database. Neither of those allow you to change the threshold, so if you want to alert when there’s <5% free (or whatever), should you use the targets that'd get disabled here? Or create a monitor targeted at Database that uses the counter % log free space (counter is SQL Server:Databases\Percent Log Used\DB Name)

    • Kevin Holman

      The disabled monitors targeting the old objects are simply left behind from the old management packs. They allowed a customer to go back and use the “old way” for backwards compatibility.

      There is no need to create a monitor. The two monitors for space are now “ROWS Data Free Space Left” for database space and “LOG Free Space Left” both of which have overridable properties for a threshold.

  2. VDIAS

    Hi,

    Kevin,which MP is the latest and recommended for MSSQL servers… the agnostic one?

    Just found out, that on our environment we have deployed both… 🙁

Leave a Reply

Your email address will not be published. Required fields are marked *