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.
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.
Following is the table for the Workflows included in this MP and their defaults