locked
R2 DW/Reporting issues RRS feed

  • Question

  • You can see my original post at:

    But the scope of the issue has changed.

    The problem I am having is with running performance and availability reports from the DW.  Regardless of any report run, the availability shows up as "unmonitored" in the report.  This is in direct contrast to the actual health of computers, webapps, and distributed applications that have their health monitoring just fine in the Monitoring view.

    The only errors I'm seeing related to the DW are:

    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. 

    These alerts come and go.  I have checked my Datawarehouse SQL server authentication accounts.  Username is single space, as is the password.  Permissions on the DW are OK according to technet documentation.  Interestingly enough, the exchange 2010 data is being written and reports just fine, its just everything else.  Reporting services are fine for ACS.

    Any Ideas?  I'm stumped.  This seems to have started about the time I moved the RMS role.  I followed the Technet steps.  I verified data IS being written to the DW, so their is new data present, it just can't seem to pull any of it.

    A DBCC CHECKDB didn't report any errors.  I've run sp_updatestats on the DW, that didn't help either.  At this point, I'm tempted to just reinstall the DW completely.


    Wednesday, February 3, 2010 4:20 PM

Answers

  • Well, finally got it all fixed.  Was seeing a lot of 31552 errors in my event log.  Which is pretty common if you search the forums.

    Mine constantly referred to errors with "state data set".  Makes sense as I couldn't report on any state dataset information either.  I don't recommend running this script without contacting Microsoft first.  I'm posting it because it may prompt a Launching point for troubleshooting.

    "This issue is caused by an aggregation job taking an abnormally long amount of time to complete.

    Here is the procedure to fix our issue. Essentially, what we are trying to do is to disable this from SCOM and run it directly on the DW, because there is a Maintenance frequency in SCOM with 60 seconds.

     

    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 was the problem, which in our case is “State data set”
    d. Check the box next to Enabled and change the override value to “False”, then apply the changes.

     

    Restart the “System Center Management” service on the RMS.
    Wait approximately 10 minutes.

    Then connect to the SQL server that hosts the OperationsManagerDW database and open SQL Management Studio.
    Run the query below replacing the portion in single quotes with the name of the data set in question, which in our case is “State data set”

    --Note: This query could take up to a few hours to complete. Do not stop the query prior to completion.


    USE [OperationsManagerDW]
    DECLARE @DataSet uniqueidentifier
    SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'State')
    EXEC StandardDatasetMaintenance @DataSet

    "
    • Marked as answer by ty72182 Wednesday, March 3, 2010 6:17 PM
    Wednesday, March 3, 2010 6:17 PM

All replies

  • Well, I'm going to try to open a case with PSS.  Hopefully something comes of it.  Any help would still be appreciated!
    Thursday, February 4, 2010 3:27 PM
  • <<<<<Any Ideas?  I'm stumped.  This seems to have started about the time I moved the RMS role.  I followed the Technet steps.  I verified data IS being written to the DW, so their is new data present, it just can't seem to pull any of it.

    A DBCC CHECKDB didn't report any errors.  I've run sp_updatestats on the DW, that didn't help either.  At this point, I'm tempted to just reinstall the DW completely.>>>>>

    Hi Ty,

    I had the same problem(s) and rebuilt the whole environment.....I got so sick of the time I was spending troubleshooting. Worked a treat!!
    With careful planning, the whole lot was going again inside 8 hours.

    BTW, are you using Virtual or physical servers. I had no end of issues on VMWare, switching to Physical made an incredible difference.
    2c,
    John Bradshaw

    Saturday, February 6, 2010 10:54 AM
  • Well, finally got it all fixed.  Was seeing a lot of 31552 errors in my event log.  Which is pretty common if you search the forums.

    Mine constantly referred to errors with "state data set".  Makes sense as I couldn't report on any state dataset information either.  I don't recommend running this script without contacting Microsoft first.  I'm posting it because it may prompt a Launching point for troubleshooting.

    "This issue is caused by an aggregation job taking an abnormally long amount of time to complete.

    Here is the procedure to fix our issue. Essentially, what we are trying to do is to disable this from SCOM and run it directly on the DW, because there is a Maintenance frequency in SCOM with 60 seconds.

     

    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 was the problem, which in our case is “State data set”
    d. Check the box next to Enabled and change the override value to “False”, then apply the changes.

     

    Restart the “System Center Management” service on the RMS.
    Wait approximately 10 minutes.

    Then connect to the SQL server that hosts the OperationsManagerDW database and open SQL Management Studio.
    Run the query below replacing the portion in single quotes with the name of the data set in question, which in our case is “State data set”

    --Note: This query could take up to a few hours to complete. Do not stop the query prior to completion.


    USE [OperationsManagerDW]
    DECLARE @DataSet uniqueidentifier
    SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'State')
    EXEC StandardDatasetMaintenance @DataSet

    "
    • Marked as answer by ty72182 Wednesday, March 3, 2010 6:17 PM
    Wednesday, March 3, 2010 6:17 PM
  • I've tried this solution, but it didn't help me. Output when I run this query:

    Caution: Changing any part of an object name could break scripts and stored procedures.
    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

     

    I've tried another data set - 'State'(I have problem with Event dataset), it runs ok!

     

    Any ideas?

    Friday, February 25, 2011 9:09 AM
  • i am getting this same error too
    Sunday, September 4, 2011 12:36 AM