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

This post has been republished via RSS; it originally appeared at: Project Support Blog articles.

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.

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)

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.

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.

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

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!

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.

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.

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?