Project Server: Where did all those Reporting Project Sync jobs come from?


This also applies to Project Online – and all versions of Project Server – 2010, 2013 and 2016.  The Reporting (Project Sync) job is a low priority job that moves data into the reporting schema following either a publish of the project – or any update to the contents of the SharePoint site associated with the project.  This could be a new issue or risk, or a deliverable – or an update to any of those, or a new document – or the topic of this blog post – an update to a document on the site.  My screenshots are from Project Server 2013, and I am patched up to date.

In most cases the number of these jobs that happen is not a problem – but as they are low priority they tend to wait for other jobs.  This can mean that when other jobs are running slowly you will see these jobs queueing up.  That doesn’t mean that these jobs are making the queue slow – it is just a result of the fact that the queue is busy so these jobs get left until last (saving, publishing, status updates etc. are more time critical).  There is one case when this is not the case – and I’ll come back to that later – initially I’ll just deal with the normal things you might see in the queue.  If you edit a document, or change its properties then you will see a job in the queue.

image

This is pretty quick (I’m surprised I managed to catch a single one ‘Processing!) – and the result of this is an update or entry to the pjpub.MSP_WssDcoument table (dbo.MSP_WssDocument in the older versions)

image

So far so good.  A couple of things can add to the normal number of jobs, and the first one I’ll discuss relates to Document Sets.  A document set is a specific SharePoint content type that enables you to treat a set of documents as one – and updates to properties of the set get applied to all the documents.  As our Reporting (Project Sync) is triggered by the event receiver of the document library this can make for additional jobs getting fired.  This also changed since October 2015 as SharePoint changed how the properties where propagated which resulted in the event receiver getting hot more.  On my site I have the document I just added – and a document set.

image

My document set contains a bunch of Office documents – spreadsheets, Word files, PowerPoints as well as a couple of images and even some mpps – and a OneNote!  I’ve configured my document set to have synchronized properties and I’m going to make an update to one of these.

 

image

I immediately see a couple of sync jobs go through the queue.

image

The thing that actually propagates the properties to the rest of the document set is a timer job, and by default this is set to run every 15 minutes.  It is the Document Set fields synchronization job and I have mine set to run every 10 minutes to speed up my testing – and it looks like it should be running very soon!

image

Once the timer job runs then if we take a look back in the queue we see that a few more jobs have been processed.  In my case they are coming through a little bit on the slow side – as I’d also been playing about with workflows on this list (more later) but generally you will see these pretty quickly and there will be one for every update for every document in the set.

image

If I were to make multiple updates before the timer job next ran then I would see multiple jobs per document – so 20 updates with 20 documents in the set could see 400 jobs hit the queue.  This is still small fry and these go through really quickly – but as I mentioned you may see them waiting if there are more important jobs to process.

The next ‘normal’ behavior that can trigger multiple jobs is working on a OneNote document that lives on the project site.  When you are working on OneNote you probably realise you never need to save, as it is constantly updating the saved notebook with your changes.  Guess what?  Each of these ‘saves’ triggers the document event receiver on SharePoint and this triggers the Reporting (Project Sync) job.  Thanks to Elli Johnston for bringing this one to my attention – and be sure to read her blog.

image

image

Again, no great problem as these jobs fly through. 

But it can get worse if other things such as workflow come in to play.  Depending how a site or list workflow is written and what events trigger it – it can touch the documents in such a way that they appear to be updated – which can trigger the event receiver and the Reporting (Project Sync) job – and the document set fields synchronization – which can in turn make the document look like it has been updated… And if the workflow is triggered by updates?  Well this can give a feedback loop that will add very many jobs to the queue!  In this case it can actually slow the queue down – not because of the effort of running the jobs – but just due to the shear number of jobs, which can mean the stored procedures that control the queue processing can take time to work out what should get processed next.  Obviously this final piece needs many different things to all come together to give the perfect storm of updates – but this is certainly not an totally uncommon set of features to use.  We are looking at this final piece currently to see how we can avoid this scenario – and we may also revisit the earlier conditions too.  These are not a problem in themselves, but the update to the reporting schema is not that useful – just changing the ModifiedDate of the record at the most.

I’d certainly be interested in hearing if the MSP_Wss…. tables are often used in reporting – and suspect that the Issues and Risks ones are – but the Document one probably is less useful?


Comments (2)

  1. ElliJ says:

    We do get some limited use out of the wss reporting tables for document info. They would be a lot more useful if they included custom SharePoint column data, of course, but if your documents have specific name schemas, you can add some criteria in a CASE statement to pull data for specific documents. We use it to help us find the URLs and to do a high level cursory check against last modified or created dates. Here’s an example:

    Our Status Reports are created in OneNote and always have the words “Status Report” in them, so if we put this CASE statement (this is only part of it) into our query, we can show the last modified dates to see which PMs have not updated their status reports in a given set of time.

    CASE WHEN MSP_WssDocument.ItemRelativeUrlPath like ‘%.one%’ AND MSP_WssDocument.ItemRelativeUrlPath like ‘Status Report%’ then ‘Status Report’

    It doesn’t help if they have modified them incorrectly or modified old OneNote pages instead of creating a new entry, but it does help us catch any major outliers. We have a couple more similar items we check against. We throw the data into a pivot table and I’ve updated the query to also tie into certain Project Server data (joining on MSP_WssDocument.ProjectUID= MSP_EpmProject_UserView.ProjectUID) so that we can exclude projects in close-out phase etc.

    I can also confirm that even as our OneNote documents are getting bigger (more pages which trigger a job on each save) we haven’t seen any more problems with these jobs in the queue.

    Hope this helps!

    1. Thanks Elli, that is very useful info on how you use this table. We are currently looking at how we can reduce the number of Reporting (Project Sync) jobs that run – especially the ones that are adding no value. Potentially some useful jobs that update a modified date may also get filtered out, but we are still looking at the final ‘fix’ to make in this area. We have already implemented a fix in Project Online and if you use PJO you may already have seen the number of this type of jobs reduce. As the MSP_WssDocuments table isn’t exposed through OData then nothing is lost by this reduction. We hope to release an update to reduce the flow of these jobs for Project Server 2013 in the September package of updates. Once we have the fix finalised then I’ll add an update into the body of the blog post.
      In terms of getting at the additional data from the SharePoint custom columns we have no plans to extend the schema, but of course once you have the document Url you could make OData calls against the list to pull other interesting information back.
      Best regards,
      Brian.

Skip to main content