none
Data was written to the Data Warehouse staging area but processing failed - 31553 error RRS feed

  • Question

  • Hi All,

    I am getting Event Id: 31553 event on all three Management Servers (SCOM 1801). Please let me know how to fix. In few blogs, it was mentioned for ManagedEntity table, but in my case it is "EventProcessStaging" table. I have checked the rows count on Event.EventStage and Event. Event2Process tables, where it is less than 10000 only. So, help me to fix.

    Both Event Staging and Alert Staging tables are increasing in count after truncating the tables, but not being processed at all. 

    Event Id: 31553

    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 EventProcessStaging, Line 398, Message: Sql execution failed. Error 8115, Level 16, State 1, Procedure -, Line 1, Message: Arithmetic overflow error converting IDENTITY to data type int. 

    One or more workflows were affected by this.  

    Workflow name: Microsoft.SystemCenter.DataWarehouse.CollectEventData 
    Instance name: MS1.domain.com
    Instance ID: {B85006FB-F9A9-A524-D7D1-3CF431AA933D} 
    Management group: Prod


    Regards, Suresh

    Wednesday, August 14, 2019 6:09 PM

All replies

  • Wednesday, August 14, 2019 8:01 PM
  • Hi Suresh,

     

    For the error code, it seems the related table is ran out of ID. For the Procedure EventProcessStaging, based on my research, eventstage2 process table may also be related.  Please confirm if this is the affected table. If it is , contact your SQL engineer to reseed the identity of this table to see if the issue can be fixed.

    Note: Please do backup before you do any action to avoid the impact on your environment

     

    Hope the information can help.

     

    Best regards

    Crystal


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, August 15, 2019 2:53 AM
  • >> Line 398, Message: Sql execution failed. Error 8115, Level 16, State 1, Procedure -, Line 1, Message: Arithmetic overflow error converting IDENTITY to data type int.
    this error indictae that, Procedure EventProcessStaging procedure try to insert a value in a column which is more than the limit of data type.
    solution:
    1) use DBCC CHECKIDENT('tablename')
    2)In order to solve this issue, use  DBCC CHECKIDENT ('tablename',RESEED, n+1)
    3) table relate to above procedure is

    • Alert.Alert_XXXXX
    • Alert.AlertDetail_XXXX
    • Alert.AlertParameter_XXXXXXXXXXXX
    • Alert.alertResolutionState_XXXXXXXXX
    • Event.Event_XXXXX
    • Event.EventDetail_XXXX
    • Event.EventParameter_XXXXXXXXXXXX
    • Event.EventRule_XXXXXXXXX

    Roger

    Thursday, August 15, 2019 10:08 AM
  • Hi CyrAZ,

    Yes, I saw these blogs but they are not for "EventProcessStaging" procedure. They are for MangedEntity tables. I have used the query to check the count in Event Process tables but they are only with 6 digits (didnt exceed the integer value).


    Regards, Suresh

    Friday, August 16, 2019 10:32 AM
  • Hi System Center guy,

    I have checked the count of rows present in all below tables and they are less than 400000 (six digits only - didn't exceed the defined integer value which is of 10 digit length)

    • Alert.Alert_XXXXX
    • Alert.AlertDetail_XXXX
    • Alert.AlertParameter_XXXXXXXXXXXX
    • Alert.alertResolutionState_XXXXXXXXX
    • Event.Event_XXXXX
    • Event.EventDetail_XXXX
    • Event.EventParameter_XXXXXXXXXXXX
    • Event.EventRule_XXXXXXXXX
    • Alert.AlertStage
    • Alert.AlertStage2Process
    • Event.EventStage
    • Event.EventStage2Process



    Regards, Suresh

    Friday, August 16, 2019 10:36 AM
  • I have followed my blog and truncated Event.EventStage table to clear 20000 events. When I run the below query, I am getting an error as in Event ID 31553. I couldn't manually run the stored procedure for Event dataset. The same query works fine for Alert dataset.

    exec StandardDatasetMaintenance @DatasetId='xxxx-xxxx-xxxx-xxxx-xxxx'

    https://bsuresh1.wordpress.com/2014/03/18/alert-data-is-not-being-inserted-into-scom-data-warehouse/

    Any suggestion would be highly appreciated. My current SCOM version is 1807.


    Regards, Suresh

    Friday, August 16, 2019 12:24 PM
  • You could try enabling debug log for EventProcessStaging: https://nocentdocent.wordpress.com/2009/02/18/data-warehouse-debugging/
    Friday, August 16, 2019 1:49 PM