If you have a large(ish) resource pool, with over about 1500 resources then you may run in to an issue with the Reporting Database Refresh not completing correctly – with the initial symptom that many of the Reporting (Resource Sync) and all of the Reporting (Project Sync) jobs may just say Cancelled and if you click through to the errors from the queue you will see: Reporting message processor failed: ReportingResourceChangeMessageFailed (24008) - A RDS message that was spawned during a RDB refresh operation attempted to execute outside of the time range in which the refresh operation run.. Details: id='24008' name='ReportingResourceChangeMessageFailed' uid='e3caf6d3-cc85-4078-90d3-1ce1ad929776' QueueMessageBody='Resource UID: '7d536aef-9be1-46c7-971a-286e384918c8'. ChangeType='Add'. ResourceChangeType='All'' Error='A RDS message that was spawned during a RDB refresh operation attempted to execute outside of the time range in which the refresh operation run.'. Or Reporting message processor failed: ReportingProjectChangeMessageFailed (24006) - A RDS message that was spawned during a RDB refresh operation attempted to execute outside of the time range in which the refresh operation run.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='bfd6372f-b545-4368-8b8e-00807de566f0' QueueMessageBody='Project UID='65cae55a-48e3-44fe-826e-f4f7fa478cc8'. PublishType='All'' Error='A RDS message that was spawned during a RDB refresh operation attempted to execute outside of the time range in which the refresh operation run.'. and if you look in the ULS logs then you will see something like: 07/14/2011 12:19:04.56 Microsoft.Office.Project.Server (0x2084) 0x2538 Project Server Reporting auhd High PWA:https://Server/PWA, ServiceApp:Project Server Service Application, User:DOMAINUser, PSI: [RDS] ULS Event: ReportingResourceChangeMessageFailed was associated with exception: Microsoft.Office.Project.Reporting.ProjectReportingPublic.ReportException: A RDS message that was spawned during a RDB refresh operation attempted to execute outside of the time range in which the refresh operation run. at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.RDSBaseMessageProcessor.CheckIfAllowedToProceed(ReportingBaseMessage msg, MessageContext msgContext, Group messageGroup, JobTicket jobTicket) at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.ResourcesChangedMessageProcessor.HandleMessage(Message msg, Group messageGroup, JobTicket jobTicket, MessageContext mContext) ae4c9ca3-2a2c-44d1-b7d3-3d54589fcc8a 07/14/2011 12:29:16.10 Microsoft.Office.Project.Server (0x2084) 0x3868 Project Server Reporting atwr High PWA:https://Server/PWA, ServiceApp:Project Server Service Application, User:DOMAINUser, PSI: [RDS] ULS Event: ReportingProjectChangeMessageFailed was associated with exception: Microsoft.Office.Project.Reporting.ProjectReportingPublic.ReportException: A RDS message that was spawned during a RDB refresh operation attempted to execute outside of the time range in which the refresh operation run. at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.RDSBaseMessageProcessor.CheckIfAllowedToProceed(ReportingBaseMessage msg, MessageContext msgContext, Group messageGroup, JobTicket jobTicket) at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.ProjectPublishMessageProcessor.HandleMessage(Message msg, Group messageGroup, JobTicket jobTicket, MessageContext mContext) ae4c9ca3-2a2c-44d1-b7d3-3d54589fcc8a The first message will appear for cancelled resource sync jobs, and the second for projects. If you don’t happen to notice this issue, then the next thing you might see are failed reporting publish jobs such as the resources that assignments belong to may not exist in the reporting database (if they were among the cancelled ones. These errors will look something like this: 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='6b806909-7a24-4409-8b80-b898f4a904a9' QueueMessageBody='Project UID='79bf1075-a46a-467c-828a-24a1dc00ebbb'. 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='29aca4ff-eb33-46ac-b6e6-1126f8532dae' JobUID='8ed284d5-7015-4e6e-a344-6380aae4b0d1' ComputerName='ServerName' GroupType='ReportingProjectPublish' MessageType='ReportProjectPublishMessageEx' MessageId='1' Stage=''. For more details, check the ULS logs on machine ServerName for entries with JobUID 8ed284d5-7015-4e6e-a344-6380aae4b0d1. To better understand the reason for the failure it may help to understand more about the reporting database refresh process. It is a process that gets automatically started based on certain conditions that would mean the data in the reporting database would not be consistent – and the most common of these is that some metadata such as the custom fields have been restored from archive. Once this happens then the reporting data may be incorrect – as it may have custom field data that no longer makes sense – for CFs that were not in the archive so they no longer exist. If you monitor the queue you will see an initial job that says Reporting Database Refresh and this will soon change to Reporting Database Refresh(Sleeping) – which is ok. You will then see several sets of jobs getting added at intervals. It will look something like this: Reporting (Fiscal Period Sync) – Immediate Reporting (Resource Capacity Range Sync) – Immediate Reporting (Lookup Table Sync) for each lookup table, and added to the queue 5 minutes after the Reporting (Resource Capacity Range Sync) Reporting (Custom Field Metadata Sync) for each custom field, and added to the queue 5 minutes after the Reporting (Lookup Table Sync) Reporting (Entity User View Refresh) for each view and added at the same time as the Reporting (Custom Field Metadata Sync) Reporting (Resource Sync) for each resource and added to the queue 5 minutes after the Reporting (Entity User View Refresh) Reporting (Workflow Metadata Sync) for stage, phase etc. – added 5 minutes after Reporting (Resource Sync)’s finish Reporting (Enterprise Project Type Sync) for the EPT’s – added at the same time as the Reporting (Workflow Metadata Sync) Reporting (Project Publish) for each project and added 5 minutes after the Reporting (Enterprise Project Type Sync) Reporting (Timesheet Assignments Refresh) – added 10 minutes after the Reporting (Project Publish) Reporting (Timesheet Project Aggregation) one for each timesheet period – added at the same time as the Reporting (Timesheet Assignments Refresh) While all this is going on the initial job will wake every few minutes to make sure things are still going along OK – and it is at this point that the bug this blog is about can break things. It is a timing issue and it is possible that when the waking job checks to see if all is OK it gets a response it is not expecting so it thinks things have failed – writes a failure message to one of our database tables along with a failure time. Subsequent jobs are checking this table too and will see that things have apparently failed so they will just cancel (or some of the jobs may show “Failed but not blocking correlation”). This is the reason for the error message saying “A RDS message that was spawned during a RDB refresh operation attempted to execute out
side of the time range in which the refresh operation run” – as the current time does not fit between the apparent start and end time of the reporting database refresh. So how to recover? There will be a fix coming along – and my hope is that it will make the August 2011 Cumulative Update – but there is a workaround if you should find yourself in this situation. You will need to force a reporting update for all the resources and all the projects. For the resource you can open them in Resource Center and Save (no change needed), and you will see the Reporting (Resource Sync) and the Reporting (Timesheet Project Aggregation) jobs kicked off for this user. The reason you need to refresh all resources and not just the ones that failed is this second group of jobs – the Reporting (Timesheet Project Aggregation) – which puts data from the admin lines of the timesheets into the MSP_EpmAssignments and MSP_EpmAssignmentsByDay tables. You can use the Bulk Edit option to enable you to do multiple resources at once but for this to work you need to make a change (you could add a new CF and just type some text into it) – and you also need to be sure all your resources are checked in. You may find the page a little unresponsive when you hit Save (you might want to limit it to a few hundred at once – I did around 200 at a time and that worked for me . A trick that might help – if you select a group of rows using the shift-click you can then check the select box for all of the selected rows. I’m still playing around with the options here – I will update the post if I find some new tricks and tips. For projects you will need to re-publish all the plans. You can do this either using ProjTool from the SDK https://msdn.microsoft.com/en-us/library/gg446880.aspx or use a PowerShell commands made available by the code gallery sample at https://archive.msdn.microsoft.com/pj14PowershellPSI . The getting started guide on that page has a sample that can do a check-out, publish and check back in.
More here:
Project Server 2010: Reporting Database Refresh failing with large resource pools
Leave a comment!
You must be logged in to post a comment.