none
Reporting Database Refresh stuck in "Waiting to be processed (Sleeping)" RRS feed

  • Question

  • Hi all,

    Project Server 2010 - In the queued jobs log, we're having a lot of "Reporting" jobs failing - mainly "Reporting (Resource Sync)" and "Reporting (Project Publish)".

    After some research the suggestion is to force a rebuild of the reporting database by backing up/restoring custom fields, but when I try to do this, the reporting database refresh job sits in the queue with the status of "Waiting to be processed (Sleeping)" and never actually does anything.

    How can I get around this issue? Is there any other way of refreshing the reporting DB?

    Thanks


    • Edited by AsherF Monday, September 10, 2012 11:08 AM
    Monday, September 10, 2012 11:08 AM

All replies

  • Are you facing this issue only for a particular project or all projects?

    Abhijeet

    Monday, September 10, 2012 11:33 AM
  • Abhijeet,

    It's happening with most projects, but not all the time. I haven't been able to detect a pattern to when it does and doesn't work

    Thanks again

    Monday, September 10, 2012 11:36 AM
  • Update: I've just done a check for the last 2 weeks and every "Reporting (Project Publish) job has failed for every project!

    Below is more detail from the error log for one of the projects:

    • Reporting message processor failed:
      • ReportingProjectChangeMessageFailed (24006) - The INSERT statement
        conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.. Details: id='24006'
        name='ReportingProjectChangeMessageFailed'
        uid='e82cd2d3-0345-4034-96d1-9924593796f4' QueueMessageBody='Project
        UID='54d98508-22db-4203-b560-57b09b62ef07'. PublishType='ProjectPublish''
        Error='The INSERT statement conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.'.
      • ReportingProjectChangeMessageFailed (24006) - The INSERT statement
        conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.. Details: id='24006'
        name='ReportingProjectChangeMessageFailed'
        uid='2a7c85a7-0007-442d-910d-a1510fd52534' QueueMessageBody='Project
        UID='54d98508-22db-4203-b560-57b09b62ef07'. PublishType='ProjectPublish''
        Error='The INSERT statement conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.'.
      • ReportingProjectChangeMessageFailed (24006) - The INSERT statement
        conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.. Details: id='24006'
        name='ReportingProjectChangeMessageFailed'
        uid='f2d6a55f-7274-4f2a-8cc0-203673bf6f8f' QueueMessageBody='Project
        UID='54d98508-22db-4203-b560-57b09b62ef07'. PublishType='ProjectPublish''
        Error='The INSERT statement conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.'.
      • ReportingProjectChangeMessageFailed (24006) - The INSERT statement
        conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.. Details: id='24006'
        name='ReportingProjectChangeMessageFailed'
        uid='34ebc20a-9b5a-4ca4-8691-4f840feb8396' QueueMessageBody='Project
        UID='54d98508-22db-4203-b560-57b09b62ef07'. PublishType='ProjectPublish''
        Error='The INSERT statement conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.'.
      • ReportingProjectChangeMessageFailed (24006) - The INSERT statement
        conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.. Details: id='24006'
        name='ReportingProjectChangeMessageFailed'
        uid='34b7bf69-c7e2-48cc-8bf0-35ba2aaa7c5c' QueueMessageBody='Project
        UID='54d98508-22db-4203-b560-57b09b62ef07'. PublishType='ProjectPublish''
        Error='The INSERT statement conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.'.
      • ReportingProjectChangeMessageFailed (24006) - The INSERT statement
        conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.. Details: id='24006'
        name='ReportingProjectChangeMessageFailed'
        uid='29160ede-e28f-4427-af8d-28c08eaf884d' QueueMessageBody='Project
        UID='54d98508-22db-4203-b560-57b09b62ef07'. PublishType='ProjectPublish''
        Error='The INSERT statement conflicted with the FOREIGN KEY constraint
        "FK_MSP_EpmAssignment_ResourceOwnerUID". The conflict occurred in database
        "ProjectServer_Reporting", table "dbo.MSP_EpmResource", column 'ResourceUID'.
        The statement has been terminated.'.
    • Queue:
      • GeneralQueueJobFailed (26000) -
        ReportingProjectPublish.ReportProjectPublishMessageEx. Details:
        id='26000' name='GeneralQueueJobFailed'
        uid='17b19de3-edc2-4e5e-b635-bfb576d0ed47'
        JobUID='b7c1be83-dd9d-4b7e-9ae7-07f0e3ed60de' ComputerName='CHE-SVR-103'
        GroupType='ReportingProjectPublish' MessageType='ReportProjectPublishMessageEx'
        MessageId='1' Stage=''. For more details, check the ULS logs on machine
        CHE-SVR-103 for entries with JobUID
        b7c1be83-dd9d-4b7e-9ae7-07f0e3ed60de.
    • Edited by AsherF Monday, September 10, 2012 11:40 AM
    Monday, September 10, 2012 11:38 AM
  • The ultimate suggestion here is to try SQL Trace while you publish one of your next project.

    The logs found as part of SQL Trace can give the closer hint on what's happening exactly.


    Abhijeet

    Monday, September 10, 2012 11:55 AM
  • What is the Version of Project Server for which you are getting these errors?


    Abhijeet

    Monday, September 10, 2012 11:57 AM
  • Abhijeet, we are using Project Server 2010. Sorry, I should have included that in my original post

    Thanks

    Monday, September 10, 2012 1:20 PM
  • Hi,

    If all Publishing (Reporting) jobs are failing, chances are reporting databases is out of sync.

    You may need to review this blog information

    http://blogs.msdn.com/b/brismith/archive/2011/07/14/project-server-2010-reporting-database-refresh-failing-with-large-resource-pools.aspx

    Sorry for jumping into the thread.


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful; if a post has been useful to you.This can be beneficial to other community members reading the thread.

    Monday, September 10, 2012 3:10 PM
    Moderator
  • Hrishi,

    Thanks for this, but we only have about 30 resources in our database. Also, this article doesn't actually give a fix, just refers in the comments to the August 2011 CU, but we have that already!

    Thanks


    • Edited by AsherF Tuesday, September 11, 2012 10:17 AM typo
    Tuesday, September 11, 2012 10:16 AM
  • Open all the resources in ERP in Project professional and then save the ERP.

    Check in the queue whether all resources sync jobs are successful.

    Later open the project in Project Pro and perform Save and publish and check if the reporting publish jobs are completed successfully.


    Thanks and Regards, Roopkumar Kamat (MCITP)

    Tuesday, September 11, 2012 11:12 AM
  • Can you go to SharePoint Central Administration and click on Upgrade and Migration>Review Database Status

    Check the status column against all the databases in your deployment


    Abhijeet

    Tuesday, September 11, 2012 11:19 AM
  • Hi,

    Could you please execute following query on reproting database and let us know if you see any data

    select * from MSP_EPMResource_Userview


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful; if a post has been useful to you.This can be beneficial to other community members reading the thread.

    Tuesday, September 11, 2012 1:56 PM
    Moderator
  • Hrishi,

    I only get three results - Unassigned Resource, Local Resource and Unknown Resource

    Wednesday, September 12, 2012 9:06 AM