SCOM 2007 R2 : Failed to Store Data in the Data Warehouse. Exception 'SqlException' : Timeout Expired.
Hi All
SCOM 2007 R2 : Failed to store data in the Data Warehouse.
Exception 'SqlException': Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Please Help me to fix this issue
MNYash
May 25th, 2011 2:23am
Hi,
First of all - check your DW DB. Can you run any query against this DB? Any errors on thos SQL instance? Check for a connectivity and a free space on DB disk... http://OpsMgr.ru/
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2011 2:25am
Hi,
I had checked that, DW DB Connectivity is proper, and Its having enough Space also.MNYash
May 25th, 2011 2:34am
What about a DB server performance? Free memory, CPU peaks, database disk queue and latency?http://OpsMgr.ru/
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2011 2:38am
Hi,
DB Server Performance, Free Memory, CPU Peaks, Database Disk queue and latency all are fine, There is no problem with it.
In Operation Manager Logs getting the below event ID: 31552 Data Warehouse.
Failed to store data in the Data Warehouse.
Exception 'SqlException': Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
One or more workflows were affected by this.
Workflow name: Microsoft.SystemCenter.DataWarehouse.StandardDataSetMaintenance
Instance name: State data set
Instance ID: {-----------}
Management group: ABCD
MNYash
May 25th, 2011 2:45am
Microsoft.SystemCenter.DataWarehouse.StandardDataSetMaintenance
Most likely there is some maintenance that just takes too long (and most likely it is your SQL box not having enough resource to complete this in time). Set a higher timeout on your SQL box.
Regards,
Marc Klaver
http://jama00.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2011 7:32am
Hi,
The problem solved, with the help of the below steps, Thanks for the support.
1.Using the instance name section in the 31552 event, find the data set that is causing the problem. (See the highlighted portion in the event below)
Log Name: Operations Manager
Source: Health Service Modules
Event ID: 31552
Task Category: Data Warehouse
Computer: LA-SCOMRMS01.caa.com
Description:
Failed to store data in the Data Warehouse.
Exception 'SqlException': Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
One or more workflows were affected by this.
Workflow name: Microsoft.SystemCenter.DataWarehouse.StandardDataSetMaintenance
Instance name:
State data set
Instance ID: {CA524D86-58D7-9C71-5B71-466EC8AF7F5A}
Management group: CAA-LA
2.Create an override to disable the maintenance procedure for this data set:
a.In the OpsMgr console go to Authoring-> Rules-> Change Scope to “Standard Data Set”
b.Right click the rule “Standard Data Warehouse Data Set maintenance rule” -> Overrides -> Override the rule -> For a specific object of class: Standard Data Set”
c.Select the data set that you found from the event in step 1.
d.Check the box next to Enabled and change the override value to “False”, then apply the changes.
3.Restart the “System Center Management” service on the RMS.
4.Wait approximately 10 minutes and then connect to the SQL server that hosts the OperationsManagerDW database and open SQL Management Studio.
5.Run the query below replacing the highlighted portion with the name of the data set from step 1.
USE [OperationsManagerDW]
DECLARE @DataSet uniqueidentifier
SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Event ')
EXEC StandardDatasetMaintenance @DataSet
6.Once the query finishes follow steps 1-5 again, only this time set the rule to Enabled = True.
7.Monitor the event log for any timeout events.
MNYash
May 26th, 2011 2:02am