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.
Before you can configure and run the report, you need to have the following:
- You need to import the SQL Management pack.
- Configure the SQL Server Run as accounts which is having the permissions as described below article.
Please check if we have the following store procedure in Data warehouse database or not.
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.
- MSSQL on Windows: Discover SQL Server DB Files
- 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.
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.