locked
SCOM 2007 R2 Data Warehouse Issue Event ID 31552 and 31553 RRS feed

  • Question

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

    Wednesday, May 14, 2014 8:33 AM

Answers

  • 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,

    Yan Li


    Regards, Yan Li


    • Edited by Yan Li_ Thursday, May 15, 2014 8:18 AM add info
    • Marked as answer by Yan Li_ Tuesday, May 27, 2014 6:46 AM
    Thursday, May 15, 2014 8:16 AM
  • 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


    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" Mai Ali | My blog: Technical | Twitter: Mai Ali

    • Marked as answer by Yan Li_ Tuesday, May 27, 2014 6:46 AM
    Tuesday, May 20, 2014 3:31 PM
  • 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
    Thursday, May 29, 2014 2:56 PM

All replies

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


    Note: This posting is provided 'AS IS' with no warranties or guarantees, and confers no rights. Please take a moment to "Vote as Helpful" and/or "Mark as Answer", where applicable. This helps the community, keeps the forums tidy, and recognises useful contributions.

    Wednesday, May 14, 2014 8:41 AM
  • 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.

    Wednesday, May 14, 2014 10:30 AM
  • 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,

    Yan Li


    Regards, Yan Li


    • Edited by Yan Li_ Thursday, May 15, 2014 8:18 AM add info
    • Marked as answer by Yan Li_ Tuesday, May 27, 2014 6:46 AM
    Thursday, May 15, 2014 8:16 AM
  • 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


    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" Mai Ali | My blog: Technical | Twitter: Mai Ali

    • Marked as answer by Yan Li_ Tuesday, May 27, 2014 6:46 AM
    Tuesday, May 20, 2014 3:31 PM
  • 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
    Thursday, May 29, 2014 2:56 PM
  • 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"

    Monday, May 11, 2015 1:31 PM