Under rare circumstances, due to the heavy load, OpsMgr may be unable to process dataset data in time. This, in turn, may create a congestion in dataset staging tables on the SQL server.
To resolve the issue, make sure that the SQL server has enough resources to process all incoming OpsMgr data. These errors are most likely to self-resolve once the OpsMgr completes the operation when the load is decreased.
If you still get timeouts and SQL-related errors, you will need to manually clean up staging tables on the SQL server. To do that, disable the standard built-in maintenance procedure and run it manually:
- Use the Instance name field of the 31552 event description to locate the dataset that is causing the timeout — that can be either VeeamMP or VeeamBackupMP.
- Configure an override to disable the maintenance procedure for the dataset:
- Open the OpsMgr console using the account with administrative rights. Go to the Authoring section.
- Navigate to Rules.
- On the console toolbar, double-click the Scope button.
In the Scope Management Pack Objects window, search and add Standard Data Set.
- Right-click the Standard Data Warehouse Data Set maintenance rule and select Overrides > Override the Rule > For a specific object of class: Standard Data Set.
In the Select Object window, search and select the dataset that you located at step 1.
In the Override Properties window, select the check box next to the Enabled parameter, change the Override Value to False, and click Apply to save changes.
This will prevent dataset maintenance from running automatically for the given dataset type.
- Restart the System Center Management service on the RMS to disable all running maintenance procedures.
- Wait 10 minutes, then connect to the SQL server that hosts the OpsMgr DW DB and open SQL Server Management Studio.
Run the following query using the dataset name obtained at step 1:
DECLARE @DataSet uniqueidentifier
SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'dataset_name')
EXEC StandardDatasetMaintenance @DataSet
The query could take several hours to complete depending on how much data has been flooded to the warehouse. Do not stop the query until it completes.
- Once the query completes, delete the override configured at step 2.
OpsMgr DB DW keeps data for a specified length of time, depending on the data and aggregation type. By default, the database is set up to delete older data — this process is called ‘grooming’.
You can configure grooming settings for different types of data using Microsoft SQL Server Management Studio. Please note that Veeam MP is designed to use raw data aggregation type only, so hourly and daily aggregation settings must be ignored. To learn how to configure grooming settings for the OpsMgr Reporting Data Warehouse Database, see this Microsoft article.