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.