Skip to main content

Queue Failure - "User Synchronization for Project Web Access App Root Site and Project WSS Workspace"

Event

You see a queue failure - "User Synchronization for Project Web Access App Root Site and Project WSS Workspace"

You may see a ULS entry that looks something like this:-


10/06/2008 17:11:37.16 Microsoft.Office.Project.Server (0x07D8) 0x0CF0 Windows SharePoint Services Database 6f8g Unexpected Unexpected query execution failure, error code 1205. Additional error information from SQL Server is included below. "Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." Query text (if available): "{?=call proc_SecAddPrincipalToRole(?,?,?,?,?,?)}"


What this means

Some element of PWA or Project Workspace permissions synchronisation has partially or completely failed due to a deadlock. Users may not be able to access the PWA site and/or WSS Workspaces associated to Project files

Triggers

The User Synchronisation of Project Workspaces and PWA Root Site queue job is a resource-centric operation. This means that the job can be triggered in the following scenarios:-

• A Resource Record is Saved
• A Security Group is Saved
• A Security Category is Saved
• The PWA permissions list is Saved

The following actions describe some of those key activities that do NOT trigger this specific synchronisation job as they can be described as project-centric:-

• Adding/Removing Resources on Plans/Tasks
• Synchronisation of Project Workspace via PWA Server Settings

In a normal operating scenario you would not expect high volumes of these jobs to be created - if you do then you should check your processes to minimise these occurances (see end)

Processing

The process this job follows can be described loosely as:-

I. All PWA and PWS permissions are removed (including any Fine Grained Permissions)
II. For the first resource to be synchronised, PWA site permissions are added via the appropriate (Microsoft Office Project Server) Permissions Set
III. For this resource, PWS site permissions are added based on their Project Access Rights and via the appropriate (Microsoft Office Project Server) Permissions Set across ALL Sites
IV. Steps 2&3 are iterated for each resource

The primary job that runs this process (and is captured in logging) is the SynchronizeSingleUsermembershipsInWSS.


Why can this job fail?

There is an increased risk of this job failing if one or more of the following statements are true:
Triggers:

I. There are ongoing changes being made to the Security Model (Category/Group permissions changes)
II. There are ongoing changes to Security Group Memberships
III. There are large volumes of resource account changes being made

Contributing Factors where the triggers are evident:

I. There are a large number of Project Workspaces in the environment
II. Resources have access to a large number of Project Workspaces
III. There are a large number of Active Users in the environment
IV. More than 1 of the above jobs can be triggered at any given point


So how can I resolve this?

1 - By Resource
If this problem is reported for one particular resource then open and save the resource record in PWA-> Server Settings->Manage Users and Groups

2 - For all resources
if this problem is more widespread then you can Open and Save the "Team Members" group (if this does include all resources in the Enterprise Resource Pool). Note that this should be done outside of normal working time to avoid a repeat of the problem.

So how can I stop it happening again?


1 - Minimise the amount of Resource-centric changes are made on Production during standard working hours.

2 - To minimise the user impact, grant Authenticated User access to PWA via a User Group.

or alternatively to avoid the issue completely (in conjunction with point 2 above)

3 - Turn off automatic user synchronisation for PWA, WSS or ALL

>> Run the UpdateUserSyncSetting.exe tool to set the User Sync Settings. Jon
provided you with the documentation to run the tool. This only needs to be run on
one of the servers.
>> Set up a job that will run the appropriate sync tools during non-business
hours. Make sure to start it early enough that it will finish before business
hours and not interfere with SQL jobs like backups.
>> The tools that you will need to run will be based on what setting you use for the
UpdateUserSyncSetting.exe tool.

If you set the tool to:

0 - then you will not need to use the sync tools as this will be normal
behavior.
1 - then you will need to run the WorkspaceRDBUpdate tool.
2 - then you will need to run the WorkspaceUpdate tool.
3 - then you will need to run the WorkspaceUpdate and the WorkspaceRDBUpdate
tools.

Note: My preferred method for these settings is to turn all auto synchronisation off, then use the Security Model (and some processes) to drive Project-centric synchronisations to wss workspaces (which are not affected by this tool) based on group memberships and roles on the project, and use the Authenticated Users access to PWA

These tools only need to be run on one server as they just add the necessary sync
jobs to the queue.

Comments

  1. Hi Carl, Thanks for posting this info - it is very useful indeed. One question: where is the UpdateUserSyncSetting tool you mention? Does it come with PS2007, or is it something I have to obtain elsewhere?

    Thanks,
    Richard

    ReplyDelete
  2. i believe the official line is that it is only available via MS Support as there may be an unsupported risk associated. feel free to email me direct for more info

    ReplyDelete

Post a Comment

Popular posts from this blog

#projectserver2013 VIEW FAILURE: The view failed to load. Press OK to reload this view with the default settings. Press cancel to select another view.

** UPDATE ** includes notes relating to secondary bug where Timesheet is created without Administrative tasks.

Does this ring any bells?

This has been bugging me for months, but finally I have a repro for this:

Issue Summary:  When a task is deleted from a plan that is approved into a previous or current timesheet - even when there are no actuals on the task - you can no longer view the timesheet

The following repro has been proven:
- Setup system with Single Entry Mode, with enforced Status Approval before Timesheet Approval
- Create resource as own timesheet manager
- Create new project
- Create two tasks in the same week, starting monday with 5 days duration:  1) Task to assign actuals, 2) Task to delete post submission
- Assign Resource to tasks
- publish project
- as Timesheet User, go to the appropriate timesheet period for the tasks created
- Assign actual work to one task (task 1), leaving task 2 with no actual work
- Submit timesheet
- as Project Status Manager, approve the time on task 1 …

What to do when your application server goes bang

What happens when someone kills your Application Server?
So imagine the scenario:
Three Server Solution - SQL - SharePoint 2010 and Project Server 2010 Application Server (Central Admin Host) - Wfe/ReportServer
We wake one bleary Monday morning to find that some numpty has killed the application server and the users are baying for blood.
Well surprisingly SharePoint handles this disaster recovery scenario particularly well.  Well.  Better than I thought it would to be honest.
Rough steps:
- quick SQL backup to be safe - Rebuild your application server - reinstall pre-reqs - reinstall SP, PS, SPFSP1, SPS+PSSP1, Cumulative Update and other stuff you usually put on there. - Run your configuration wizard to reattach to the Farm, and select Host CA Site
The last step was what I was VERY wary of.  Would the server simply reattach to the Farm, even when there is no CA server available?  
Bingo your back.... almost.... you are going to get errors a-gogo in your event log as things just aren't quite back…

Reporting from Project Server 2016 - multiple sites and userviews

Just a quickie...
I've been interested in how MS have handled the "multiple PWA sites in a Content DB" thing since I read that this was their new approach.  Most of my reporting is via SSRS so i am reliant (still... in 2016) on DB queries rather than OData feeds (tsk) and this "querying a PWA DB with more than one PWA site in it is unsupported" quote was worrying me.

So it looks like what is happening is this.

When you create the first PWA site in a Content DB it hard-codes the SiteID into the _Userview view design elements.  This means that your first PWA Site is the default.  All the data for subsequent sites are still held in the tables against separate SiteID's but you cannot utilise the OOTB _Userview components (see below)


SELECT        ProjectFields....
FROM            pjrep.MSP_TVF_EpmProject('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmProject 
LEFT OUTER JOIN
                      pjrep.MSP_TVF_EpmInternalProjectHierarchies('FF19B767-CA6…