Data Warehouse logging improvements in OpsMgr 2012 R2 UR5 that help troubleshoot SQL time-out issues

Applies to: System Center 2012 R2System Center 2012 R2 Operations Manager

This article contains information about the logging improvements that have been made in System Center 2012 R2 Operations Manager Data Warehouse where SQL time-out exceptions are concerned. This information can be used to troubleshoot SQL time-out scenarios.

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

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

After Update Rollup 5

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 procedureStored_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

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
After Update Rollup 5

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 procedureStored_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 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:
  1. Check the Operations Manager log for events 31551 and 31552, as mentioned in the "Symptoms" section.
  2. 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.
  3. Click Start, and then click Run.
  4. In the Open box, type regedit, and then press ENTER.
  5. 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.
  6. Add a new DWORD (32-bit) value and name it Command Timeout Seconds or Bulk Insert Command Timeout Seconds, depending on your circumstances.
  7. 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.