Our SCOM 2007 R2 Environment is running with CU5 and SQL 2005 Enterprise Edition in Windows 2003 R2 Server. Enough Memory 32GB on RMS and each MS servers running with 8GB of RAM. Processor utilization also less on them. SCOM DB and Date Warehouse Databases and Transaction Logs are running with more free spaces. We are getting below two errors on our SCOM RMS frequently. I have seen many internet blogs related to Event ID 31552, 31553 but i could not find appropriate one for our issue. Kindly help me to fix this issue.

Event Type: Error
Event Source: Health Service Modules
Event Category: Data Warehouse
Event ID: 31553
Date: 5/13/2014
Time: 11:09:48 AM
User: N/A
Computer: xxxxxxxx
Description:
Data was written to the Data Warehouse staging area but processing failed on one of the subsequent operations.
Exception SqlException: Sql execution failed. Error 777971002, Level 16, State 1, Procedure AlertProcessStaging, Line 675, Message: Sql execution failed. Error 535, Level 16, State 0, Procedure -, Line 1,
Message: Difference of two datetime columns caused overflow at runtime.

One or more workflows were affected by this.

Workflow name: Microsoft.SystemCenter.DataWarehouse.CollectAlertData
Instance name: xxxxxxx.com
Instance ID: {D0AA5A9C-0015-F614-C63E-24C53F72F67C}
Management group: xxxxxxxxxx

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: 5/13/2014
Time: 11:14:20 AM
User: N/A
Computer: xxxxxxxxx
Description:
Failed to store data in the Data Warehouse. Exception SqlException: Sql execution failed. Error 777971002, Level 16, State 1,
Procedure AlertProcessStaging, Line 675, Message: Sql execution failed. Error 535, Level 16, State 0, Procedure -, Line 1,
Message: Difference of two datetime columns caused overflow at runtime.

One or more workflows were affected by this.

Workflow name: Microsoft.SystemCenter.DataWarehouse.StandardDataSetMaintenance
Instance name: Alert data set
Instance ID: {8F2D359D-D7AF-977B-213B-3ADB6BBE80D6}
Management group: xxxxxxxxxx

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

Hello !

I thinh you can refer to the Nicholas Li's post :

http://social.technet.microsoft.com/Forums/systemcenter/en-US/c52167df-2bc9-42aa-b595-abeb07e8c498/another-failed-to-store-data-in-the-data-warehouse?forum=operationsmanagergeneral

Hope this helps.

Thanks for the response. Issues happening again. I have verified all the below mentioned things in my side.

1. I have done Standard data set maintenance as well as specific data set maintenence

2. Cleared old state data aggregation from Data Warehouse

3. completed Database and Data Warehouse Indexing

4. Increased batch file inserting timeout from 5 minutes to 15 minutes.

5. Verified Language in Database side (English)

6. Checked Data Retention Period

7. Cleared Dirty Rows etc...  Kindly help me.

Hi,

The cause is should be that the Alert RasedDateTime and the LastModifiedDateTime was conflicting in the OpsMgrDB. AlertProcessStaging SPROC fails when processing an alert with a RaisedDateTime 70 years before the current date.

We can run below command to resolve this issue:

select * from Alert.Alertstage where RaisedDateTime > DBLastModifiedDateTime

Delete from Alert.Alertstage where RaisedDateTime > DBLastModifiedDateTime

Restart the health service on RMS

Another way to troubleshoot this issue:

In order to recover the situation, we can run the following SQL to detect the bad entries in the staging table:

SELECT * FROM Alert.AlertStage
WHERE (DATEDIFF(year, DBLastModifiedDateTime, RaisedDateTime) > 68) OR (DATEDIFF(second, DBLastModifiedDateTime, RaisedDateTime) < 68)
 
Please verify if any record is returned.  If yes, we could choose to remove them by running the following SQL commands:  

DELETE
FROM Alert.AlertStage
WHERE (DATEDIFF(year, DBLastModifiedDateTime, RaisedDateTime) > 68) OR (DATEDIFF(second, DBLastModifiedDateTime, RaisedDateTime) < 68)

Alternatively we could choose to update the offending datetimes to a more realistic value.
 
After that, we manually rerun the following stored procedure to see if the Alert processing works fine.
USE [OperationsManagerDW]
DECLARE @DataSet uniqueidentifier
SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Alert')
EXEC StandardDatasetMaintenance @DataSet

Hope this helps.

Regards,

For Data Warehouse Issue "31552, 31553", you can refer below link

http://blogs.technet.com/b/kevinholman/archive/2010/08/30/the-31552-event-or-why-is-my-data-warehouse-server-consuming-so-much-cpu.aspx

http://thoughtsonopsmgr.blogspot.com/2011/03/eventid-31552-failed-to-store-data-in.html

Hi All, 

Thanks for your updates on this. I have fixed my issue using below mentioned link.

http://sudheesh4.rssing.com/chan-14422683/all_p1.html

The actual problem in our environment was alert dataset had 14lakh rows/entries in my data warehouse. The above link helped me to move those data to new table (After moving those data to new table, still we can fetch the report from new table).

Regards,

Mohamed sybulla

• Edited by SCOM 2007 R2 Data Warehouse Issue 31552, 31553 Thursday, May 29, 2014 2:56 PM
• Marked as answer by SCOM 2007 R2 Data Warehouse Issue 31552, 31553 Thursday, May 29, 2014 3:01 PM
• Unmarked as answer by SCOM 2007 R2 Data Warehouse Issue 31552, 31553 Thursday, May 29, 2014 3:02 PM
• Marked as answer by Yan Li_Microsoft contingent staff, Moderator Friday, May 30, 2014 1:26 AM

Hi,

Open the registry, and locate HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0    Create a new Key under 3.0 named Data Warehouse.  Then create a new DWORD value named Command Timeout Seconds with a value of 900 seconds.  This will take the default 5 minute timeout to 15 minutes.

Following are good articles related to your issue.

http://blogs.technet.com/b/corydelamarter/archive/2014/08/12/performance-data-quot-missing-quot-in-the-opsmgr-warehouse-and-health-service-modules-event-31553.aspx

http://blogs.technet.com/b/kevinholman/archive/2010/08/30/the-31552-event-or-why-is-my-data-warehouse-server-consuming-so-much-cpu.aspx

https://systemcentersolutions.wordpress.com/

Thanks.

G.R.V------------------------------------------------------------------

Please remember, if you see a post that helped you please click "Vote As Helpful" and if it answered your question, please click "Mark As Answer"