This article contains information about the logging improvements that have been made in System Center 2012 R2 Operations Manager data warehouse where SQL Server time-out exceptions are concerned. The information can be used to troubleshoot SQL Server time-out scenarios.
Original product version: System Center 2012 R2 Operations Manager
Original KB number: 3029227
Symptoms
The following Health Service Module errors are logged in the Operations Manager log:
Log Name - Operations Manager
Source - Health Service Modules
EventID - 31551
Level - Error
User - N/A
Task Category - Data Warehouse
Keywords - Classic
Details -
| Before Update Rollup 5 | After Update Rollup 5 |
|---|---|
| Failed to store data in the Data Warehouse. The operation will be retried.Exception "SqlException": Time-out expired. The time-out period elapsed before completion of the operation, or the server is not responding.One or more of the following workflows were affected by this:Workflow name: Workflow_name Instance name: Instance_name Instance ID: Instance_ID Management group: Management_group_name | Failed to store data in the Data Warehouse. The operation will be retried.Exception 'SqlTimeoutException': Timeout expired. The timeout period elapsed prior to completion of the operation, or the server is not responding.Possible error messages: Message 1 Timeout occurred while trying to bulk copy data to Table_name table. Message 2 Timed-out stored procedure: Stored_procedure_name Current time-out value: Current_time-out_value_in_seconds This time-out can be increased by adding a registry key (type: dword 32 bit, value: revised time-out in seconds) named: Registry_name at HKLM\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Data WarehouseOne or more of the following workflows were affected by this:Workflow name: Workflow_name Instance name: Instance_name Instance ID: Instance_ID Management group: Management_group_name |
Log Name - Operations Manager
Source - Health Service Modules
EventID - 31552
Level- Error
User - N/A
Task Category - Data Warehouse
Keywords - Classic
Details -
| Before Update Rollup 5 | After Update Rollup 5 |
|---|---|
| Failed to store data in the Data Warehouse.Exception "SqlException": Time-out expired. The time-out period elapsed before completion of the operation, or the server is not responding.One or more of the following workflows were affected by this:Workflow name: Workflow_name Instance name: Instance_name Instance ID: Instance_ID Management group: Management_group_name | Failed to store data in the Data Warehouse. Exception 'SqlTimeoutException': Timeout expired. The timeout period elapsed prior to completion of the operation, or the server is not responding.Possible error messages: Message 1 Timeout occurred while trying to bulk copy data to Table_name table. Message 2 Timed-out stored procedure: Stored_procedure_name Current time-out value: Current_time-out_value_in_seconds This time-out can be increased by adding a registry key (type: dword 32 bit, value: revised time-out in seconds) named: Registry_name at HKLM\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Data WarehouseOne or more of the following workflows were affected by this:Workflow name: Workflow_name Instance name: Instance_name Instance ID: Instance_ID Management group: Management_group_name |
Resolution
SQL Server time-outs may occur for various reasons. In some cases, increasing the value of the time-out interval may be helpful in reducing or eliminating time-out events. To increase the value of the time-out interval, follow these steps:
-
Check the Operations Manager log for events 31551 and 31552, as mentioned in the Symptoms section.
-
In the description, check for the registry_name. This should be one of the following:
- Command Timeout Seconds - Time-out value that's used by the data warehouse maintenance commands.
- Bulk Insert Command Timeout Seconds - Time-out value that's used when copying bulk data to the data warehouse.
-
Click Start > Run.
-
In the Open box, type
regedit, and then press ENTER. -
Navigate to the following location in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0Add the Data Warehouse subkey if it doesn't already exist.
-
Add a new DWORD (32-bit) value and name it Command Timeout Seconds or Bulk Insert Command Timeout Seconds, depending on your circumstances.
-
Set this value to the time-out interval that you want, in seconds. For example, the value should be set to 40 for a 40-second time-out interval.
Note: We recommend that you incrementally increase the value of the time-out, because a very high value could lead to other issues. If setting a substantial value for the time-out interval doesn't resolve the problem, the root cause may differ from the scenarios that are described here.
Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.
