Data warehouse logging improvements that help troubleshoot SQL Server time-out issues
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.0
Add 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.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for