We recently had a problem with a brand new Project Web App (PWA) site in Project Server Subscription Edition (SE). Database views used for reporting (normally suffixed by UserViews) were missing from the pjrep schema in the content database.
Running Repair-SPProjectWebInstance -Identity 'https://domainname.for.pwa.site/sites/sitename' -RepairRule 7 did not resolve this issue.
There were two sites in the content database, one site that had been soft deleted and the actual site.
When Repair-SPProjectWebInstance -Identity 'https://domainname.for.pwa.site/sites/sitename' -RepairRule 7 is used, it executes pjrep.MSP_Epm_GenerateAllMultiValueAssociationViews stored procedure to create database views.
This stored procedure checks how many records exist in database table pjpub.MSP_WEB_ADMIN. If there is more than one record, the stored proc exits and does not create views.
The problem is the soft deleted site record existed in pjpub.MSP_WEB_ADMIN, so the stored procedure would never create the views.
- Identify Site ID that was soft deleted by executing this SQL against the content database
SELECT SiteID, WADMIN_DEFAULT_SITE_COLLECTION, WADMIN_IS_DELETED
FROM pjpub.MSP_WEB_ADMIN
WHERE WADMIN_IS_DELETED = 1- Force delete site using SharePoint PowerShell
# Reference to good PWA site
$GoodSite = Get-SPSite 'https://domainname.for.pwa.site/sites/sitename'
# get reference to the content database
$siteDatabase = $GoodSite.ContentDatabase
# Use the SiteID value from SQL in previous step
$SiteIDToDelete = 'SiteID from sql'
# force delete it
$siteDatabase.ForceDeleteSite($SiteIdToDelete, $false, $false)- Execute Repair using Sharepoint PowerShell
Repair-SPProjectWebInstance -Identity 'https://domainname.for.pwa.site/sites/sitename' -RepairRule 7- UserViews now exist in the
pjrepschema of the content database