Disk Forecasting uses advanced predictive analytics and machine learning technology to show you when you'll run out of storage space and predict how much space you'll need going forward.
For more information about storage forecasting in SQL Sentry, please see the follow blog posts:
- Introducing Storage Forecasting with SQL Server Machine Learning Services provides details around the forecasting methods as well as accessing and customizing features.
- Storage Forecasting and Machine Learning with SQL Sentry discusses the conceptual details of the design and development behind SQL Sentry storage forecasting.
- Standard forecasts use an enhanced linear regression algorithm. It's able to analyze your disk’s usage history, identify manual interventions such as someone truncating or deleting a large file to free space, and ensures those events don’t skew your forecast.
- Advanced forecasts learn from the workload on your storage volumes and use R/ML Services (In-Database) on the SQL Server hosting the SQL Sentry database.
Follow these steps to configure your SQL Server (hosting the SQL Sentry database) and SQL Sentry client to use storage forecasting:
- Verify SQL Server requirements and configure environment as needed
- Configure SQL Sentry monitoring service settings
Once your environment has been configured, you'll need to wait at least 24 hours to have disk forecasting data available. Then you'll be able to use the following features with disk forecasting:
- Disk Space Analysis view
- Advisory conditions for disk forecasting
- The Windows Disk Forecasting report
|Disk Forecasting Mode||Additional Requirements|
|Standard||There are no additional requirements for Standard Disk Forecasting. You can proceed to Step 2 to configure the monitoring service settings.|
|Advanced||The Advanced Disk Forecasting feature requires:
If you are configuring Advanced Disk Forecasting on SQL Server 2019, you may run into installation errors during package installation due to changes in how SQL Server 2019 handles permissions. To configure Advanced Disk Forecasting on SQL Server 2019, complete the following steps on your SQL Sentry database server:
- Create C:\ProgramData\Sentryone and C:\Users\Public\SentryOne folders.
- Give the ALL APPLICATION PACKAGES user full control over the folders created in step 1.
- Continue the package installation.
SQL Server Feature: R/ML Services
If you're unsure about the installation status of the R/ML Services in-Database feature for your existing SQL Server installation, you'll know for certain when you attempt to turn on the external scripts enabled option and the following error appears:
Msg 39020, Level 16, State 1, Procedure sp_configure, Line 166 [Batch Start Line 0] Feature 'Advanced Analytics Extensions' is not installed. Please consult Books Online for more information on this feature.
Follow the instructions provided from the Install SQL Server 2016 R Services article on Microsoft Docs to add this feature to your existing SQL Server installation.
SQL Server Configuration: External Scripts Enabled
Check external scripts enabled by running:
EXECUTE sp_configure 'external scripts enabled'
In this example, the run_value is set to 0, meaning that external scripts enabled is false/turned off. If your run_value is set to 1, then this feature is already enabled.
Execute the following to turn on external scripts enabled and reconfigure the SQL Server option:
sp_configure 'external scripts enabled', 1; RECONFIGURE WITH OVERRIDE;
This is the success message:
Configuration option 'external scripts enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
At this point, the config_value is changed, but a restart is required to set the run_value.
Yes, restart the SQL Server Instance to complete setting the run_value.
Verify that the config_value and run_value are both set to 1. Check external scripts enabled by running:
EXECUTE sp_configure 'external scripts enabled'
Open the Services window to verify that MSSQLLaunchpad / SQL Server Launchpad (MSSQLSERVER) is running:
You have the option to select either Advanced or Standard mode.
Adding a Forecast Schedule
1. Select Global Settings in the Navigator pane, and then select Forecasts.
2. Select either Standard or Advanced from the Disk Forecasting drop-down menu.
3. Select the ... to open the Select a Schedule window, and then select New to add a new Forecast Schedule.
4. Select the desired parameters from the Schedule Properties window, and then select OK to save your schedule.
5. Select the number of days desired for the Days Forward to Forecast. The default is 180 days.
6. Select the Save icon to save the Disk Forecast.
Editing or Deleting a Forecast Schedule
Edit a Forecast Schedule by completing the following steps:
1. Open the Monitoring Service Settings (Navigator > Global Settings) and select the Forecasts tab.
2. Select the ... to open the Select a Schedule window, and then select Edit to change the Schedule Properties parameters.
3. Select OK to close the save the changes, select the Save icon , and then select Regenerate Global Forecasts to refresh the Disk Forecast.
To delete a Forecast Schedule, complete the following steps:
1. Open the Monitoring Service Settings (Navigator > Global Settings) and select the Forecasts tab. Select the ... to open the Select a Schedule window. Select the desired Forecast Schedule from the list, and then select Delete.
2. Select Yes to remove the Forecast Schedule from the list, or select No to keep the Forecast Schedule in the list. Select OK in the Select a Schedule window to save your changes.
Select your desired target from the Navigator pane, and then select the Disk Space tab. Select the desired Logical Drive, and the Disk Forecasting chart appears at the bottom of the screen. On the Disk Space chart a tool tip displays with the Free Space and Forecasted Exhaustion Date.
The Free Space chart displays the following parameters:
|Free Space Chart Parameters||Description|
|Disk Free Space||Amount of available (free) hard drive space on a given disk.|
|Prediction Interval||An estimated interval that represents the range of values that a future observation will lie within at a specific point in time based on the provided probability. The forecast is generated using a 70% prediction interval, so there is a 70% probability that the actual free disk space on a given date will fall within the forecasted prediction interval.|
|Forecasted Free Space||Estimated amount of Disk Free Space that will be available for a given disk and date.|
- The forecast displayed is out 180 days, but it can be scheduled for up to 3 years.
- The length of the Disk Forecasting will impact the storage and accuracy of the data.
There are advisory conditions available (on GitHub) to work with storage forecasting:
See the Windows Disk Forecast report available under Reports > Performance Analysis > Disk/File Space: