My Experience Handling Event ID: 31552 issue

My Experience Handling Event ID: 31552 issue

We have observed below two flavors of Event ID’s. I have worked with PSS support team around 3 months to fix this issue.  Belo w are the Event Details

Event Type:           Error

Event Source:       Health Service Modules

Event Category:   Data Warehouse

Event ID:                31552

Date:                      6/28/2011

Time:                      9:16:46 AM

User:                      N/A

Computer:             IND-APP142

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: {0AD76E64-3116-AE30-BCF0-740C26A1863C}

Management group: MG_IND

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Event Type:           Error

Event Source:       Health Service Modules

Event Category:   Data Warehouse

Event ID:                31552

Date:                      6/24/2011

Time:                      5:37:48 AM

User:                      N/A

Computer:             IND-APP142

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

Instance name: tac-app142.prod.ds.russell.com

Instance ID: {024F88C7-F71B-9221-D650-8D988DB4901B}

Management group: MG_IND

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

The first event (Instance name: State data set) we have fixed with the below steps.

Query to display the Dirty Ind’s

Declare @DataSet as uniqueidentifier

Set @DataSet = (Select datasetid from standarddataset where schemaname = 'State')

Select Count(*) from StandardDataSetAggregationHistory

where DataSetId = @DataSet

And DirtyInd = 1

----------------------------------------------------------------------------------------------------------------------------------

Query to clear the Dirty Ind’s

declare @i int

set @i=0

DECLARE @DataSet uniqueidentifier

SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'State')

while(@i<=500)

begin

EXEC standarddatasetmaintenance @DataSet

set @i=@i+1

Waitfor delay '00:00:05'

end

---------------------------------------------------------------------------------------------------------------------------

Second 31552 event was due to we are using SQL 2005 standard version and Online Indexing was failing every hour. This which captured using SQL profiler tool.