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.
Available Disk Forecasting Methods
- 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
Step 1. Requirements & Configuration
|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:
- 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
How do I know if the Advanced Analytics Extensions feature is installed?
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.
How do I install R/ML Services in Database (Advanced Analytics Extensions)?
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
How do I verify that external scripts enabled is turned on in SQL Server?
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.
Note: The config_value displays what the run_value will be the next time that SQL Server restarts. If it is set to 0, then the feature will be disabled after a restart.
How do enable external scripts enabled in SQL Server?
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.
Do I need to restart SQL Server to complete the process?
Yes, restart the SQL Server Instance to complete setting the run_value.
Additional Information: If needed, follow the instructions from Microsoft Docs to restart the database engine.
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'
How do I verify that SQL Server Launchpad is running?
Open the Services window to verify that MSSQLLaunchpad / SQL Server Launchpad (MSSQLSERVER) is running:
Additional Information: If needed, see the Microsoft article for more details on verifying and testing a successful installation.
Step 2. Monitoring Service Settings
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 iconto 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.
Deleting a Forecast Schedule
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.
Note: A Disk Forecast runs automatically each day even if there is no schedule selected. A Forecast Schedule is applied to better control when the forecast runs.
Disk Space Analysis View
Note: Disk space data is not generated until 24 hours after a Disk Forecast runs.
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.
Note: The cell under Forecasted Exhaustion will be highlighted orange if the Forecasted Exhaustion date is within 180 days, and it will be red if it is within 90 days.
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:
For more information about this report, see the Reports Menus article.