Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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 Warehouse 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 |
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 Warehouse 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 |
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.