Edit

Data warehouse logging improvements that help troubleshoot SQL Server time-out issues

  • Article

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:

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