A guide for SCOM administrators
disclaimer: In this blog post, I will share a possible solution for a problem that I encountered in my work. However, I want to make it clear that this solution is not officially supported by Microsoft, and it may have some risks or side effects that I am not aware of. Therefore, use this solution at your own discretion and responsibility. (I repeat, this is NOT SUPPORTED !!!)

The Database Files Space Usage Forecast Report is a useful tool for monitoring the disk space consumption of your database files and predicting the future trends based on historical data. It provides the following information about Windows objects:

• Initially consumed file space (GB)
• Finally consumed file space (GB)
• File space consumption forecast (GB)

This report can help you plan and avoid potential issues with insufficient disk space or performance degradation. In this blog post, you will learn how to configure the report parameters and generate the report using SCOM.

Prerequisites

Before you can configure and run the report, you need to have the following:

• You need to import the SQL Management pack.

Features and enhancements in Management Pack for SQL Server | Microsoft Learn

• Configure the SQL Server Run as accounts which is having the permissions as described below article.

SQL Server Run As profiles in Management Pack for SQL Server | Microsoft Learn

Please check if we have the following store procedure in Data warehouse database or not.

Microsoft_SQLServer_2017_Windows_Views_GetDBFilesForecastData

To validate, you can run the following SQL query.

SELECT * FROM sys.objects WHERE [schema_id] = SCHEMA_ID('sdk') AND [type] = 'P' AND [name] = 'Microsoft_SQLServer_2017_Windows_Views_GetDBFilesForecastData'

If the SP is not present, please run the following SQL query against the Data Warehouse database to create the SP.

SQL/Microsoft_SQLServer_2017_Windows_Views_GetDBFilesForecastData.sql at SCOM · somahato/SQL (github.com)

Configuring the report parameters

To configure the report parameters, follow these steps:

Enable the Discoveries

Firstly, please enable the following discoveries as those are disabled by default.

1. MSSQL on Windows: Discover SQL Server DB Files
2. MSSQL on Windows: Discover SQL Server DB Filegroups
   

Exploring the discovered objects

To explore the discovered objects, follow these steps:

– In the Operations console, click on Authoring.

– Under Management Pack Objects, click on Object Discoveries.

– In the Look for box, type SQL Server DB File and click on Find Now.

– Select the discovery rule named Microsoft.SQLServer.Windows.DBFile.Discovery and click on Properties.

– In the Discovery Properties dialog box, click on the Overrides tab.

– Click on “For all objects of class: MSSQL on Windows: DB Filegroup”

– Check the Enabled and select the Override value as True.

– Click on Apply and OK to close the dialog box.

Repeat the same steps for the discovery rule named Microsoft.SQLServer.Windows.DBFilegroup.Discovery

Enable the rules

Please turn on the following Rules. Here is the instructions you can follow.

How to Enable or Disable a Rule or Monitor | Microsoft Learn

MSSQL on Windows: ROWS Data Allocated Free Space (MB)

MSSQL on Windows: ROWS Data Free Space Total (MB)

MSSQL on Windows: ROWS Data Allocated Free Space (%)

MSSQL on Windows: ROWS Data Free Space Total (%)

Generating the report

To generate the report, follow the steps as described here.

SQL Server reporting in Management Pack for SQL Server | Microsoft Learn

Sample Report

Sample test queries to validate if we have data or not

SQL/TestGetDBFilesForecastData.sql at SCOM · somahato/SQL (github.com)