Figuring out (monitoring) unused SQL databases using SCOM was one of the common requests I got from DBAs of enterprise customers. I authored a management pack (SQL MP Extensions – download available) to  address this feature and also took this an opportunity to provide a tutorial for MP authoring with a real life scenario.

I hopefully will be updating this MP with new features and publish the new versions monthly within this post. The MP Authoring tutorial will be a series of different posts explaining the elements of the MP starting with this one.

MP Features (7.0.2.1)

  • Monitors / Alerts / Reports Unused Databases based on Index Usages (only for 2008 databases currently , next versionSmile)

image

  • Scripts in this MP support CookDown (only one script per SQL instance)
  • Uses existing SQL MP Profile (SQL Server Monitoring Account) so no extra security configuration required (if there’s already one)
  • Well documentation via <KnowledgeArticles> (just check the Product Knowledge of the Rule / Monitor / Reports etc. Everything needed is there).

image

  • Noise is pre-filtered (only necessary objects are enabled)

image

  • Ready to Run Reports included (objects are pre-filtered)

image

 

Support Statement

  • There is no support for this MP, test and use under own risk
  • I only will test this mp against Operations Manager 2012 test environment therefore the schema is 2.0, for 2007 users I reccomend to move to 2012

 

Important Notes

The “Database Usage” Monitor is based on Index usages (kept on masterdb of every instance) and these counts are reset every time the SQL service starts. So this monitor will resetting to green on every server/service restart but if the database is unused again for the same days (default 7) the state will go back to RED. This is not a big deal and does not break figuring out Unused Databases but only worth to notice to inform about the behavior.

 

Requirements

 

How to Use the MP

  • Before importing the MP Ensure that System Center Monitoring Pack for SQL server is installed and configured
  • Please create a new Management Pack for Overrides and save the Overrides in this New Overrides Management Pack
  • Import the MP using any method desired
  • Change the thresholds / Alerting / Intervals as required after checking “Workflows in This MP” table
  • Please check Product Knowledges, the behavior of the workflows/reports are explained in detail.

 

Workflows in This MP

Following is the table for the Workflows included in this MP and their defaults

Workflow Type Name Data Source Threshold Intervals Enabled By Default Alerting Knowledge Comment
Monitor Database Usage Script Based 7 Days 14400 Yes Yes Yes Enabled for Non-System Databases Group(for details please check knowledge of the monitor). Health is reset if SQL service is restarted.
Rule Collection Rule For Database Usage Script Based N/A 14400 No No Yes Enable if a graph is really required (for details please check knowledge of the rule)