Skip to main content

Someone killed your farm #projectserver

** WARNING ** following this post may kill your already sickly farm....

Imagine the scenario:
- during some troubleshooting, someone deletes some EPM databases that they believe no longer point to a PWA Site (but actually do)
or
- when deleting a PWA Site, some environmental issue interrupts the process before it completes
or
- an issue occurred during PWA Site provisioning
or
- who knows what, sometimes bad things happen......

what you will see:

Sharepoint will continually report events such as :

An exception occurred while running the job scheduler.   Reason: Cannot open database "ProjectServer_Published" requested by the login. The login failed.  Login failed for user xxxxxxxxxxxxxxxxx.  Technical Details: System.Data.SqlClient.SqlException: Cannot open database "ProjectServer_Published" requested by the login. The login failed.  Login failed for user xxxxxxxxxxxxxx     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception

and

Project Server Queue 7626  Critical  Cannot start queue. SSP: 150e20ee-631b-420d-9de8-7f47b43fc67e  SiteUID: 5afc4aab-f94e-4e69-b7ba-7127a75535c4 Url:  Queue: ProjectQ
Project Server Queue 7626   Critical Cannot start queue. SSP: 150e20ee-631b-420d-9de8-7f47b43fc67e  SiteUID: 5afc4aab-f94e-4e69-b7ba-7127a75535c4 Url:  Queue: TimesheetQ

- your event log will clog up with the above
- any other EPM site queues will begin to slow down
- users get very angry

What's happening under the hood?

basically deleting stuff from a SP farm WITHOUT doing so via STSADM, Powershell or the CA UI will cause all kinds of horrible problems that will mean you will either have to recreate Service Apps OR the whole farm (Depending on the scenario) to resolve.

What can you do:
well, beside hitting the person that did it in the first place, there is an STSADM command that could save you.  However I will repeat the warning at the top of this page.  running this command may kill your farm completely so ensure you have a DR strategy in place and ready to implement.

The command is:  stsadm -o deleteconfigurationobject -id {GUID}

Now the GUID bit is the ID of the object you wish to delete from the Configuration Database "Objects" Table.  

Which object should I delete, there are 1000's

- Objects are linked together via the Dependencies table
- your aim should be to attempt to identify the PWA site object that links to these now orphased DB objects.  therefore start with any GUIDs linked to errors in the event log
- passing the PWA site GUID through the STSADM command will delete the site (this is your only option) but SHOULD also delete all the dependent orphaned objects, thus stopping the errors occurring as all trace of the site will be removed.

Note:  In some scenarios, dependencies may be broken, which may cause some residual records to remain and problems to persist.  It is worth identifying all the dependant objects prior to beginning this process.


An Example of a query that will help identify the relevant PWA Site Objects from the Farm is as follows:

SELECT *
  FROM [SharePoint_Config].[dbo].[Objects]
  where Properties like '

an example query to identify orphaned records is here (mileage may vary)

SELECT OD.ID,OD.Name, O.ID,O.Name
FROM Objects
INNER JOIN Dependencies ON O.ID = D.ObjectID
INNER JOIN Objects AS OD ON D.DependantID = OD.ID
WHERE O.Name like '[INSERT-DB-NAME-IN-EVENT-LOG-ENTRIES HERE]'

                        above query courtesy of this post http://viksrini.blogspot.com/2010/04/delete-orphaned-project-server-database.html

have fun!
Labels:

Comments

Post a Comment

Popular posts from this blog

Issues update on #projectserver2013 - Timesheets and Publishing

Reporting Publish ** updated with links to other related discussions, and a VBA macro ** the following issue has been noted on publish since June 13 CU was applied: ReportingProjectChangeMessageFailed (24006) - Object reference not set to an instance of an object.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='4d869e56-f625-e311-bb41-005056b90052' QueueMessageBody='Project UID='e3f49977-b2bc-e211-8559-005056b90052'. PublishType='ProjectPublish'' Error='Object reference not set to an instance of an object.'. I have seen this noted previously on a similar issue: http://nearbaseline.com/blog/2013/06/ms-reporting-project-publish-jobs-failed-after-aprilcu/comment-page-1/#comment-14741 This is  caused by Baselined Milestones having NULL Baseline Cost values Original bug note with potential workaround is here: http://blogs.msdn.com/b/brismith/archive/2012/05/23/project-server-2007-reporting-project-pu...
4 comments
Read more

#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 ...
6 comments
Read more

Restoring PWA Site to another Web App in the same Farm

The scenario is this: SharePoint 2016 Farm with Project Server Two Web Apps Development UAT One PWA on Development Web App. I want to copy the PWA Site on Development web app to UAT to support a testing cycle. As far as I knew there were two options: 1) Content Database Restore and Attach Process would be backup your Dev Content Database, Restore to a new Content Database for QA, then mount the database on the appropriate web app and your off.... Problem:  Although you can do this with the -AssignNewDatabaseID switch in Powershell (to avoid two content db's having the same database id) the Site Collection (PWA) in the db still retains its SiteID which means there is a duplicate SiteID in the Configuration Database.  This stops the PWA site being created and alllocated correctly and becomes essentially orphaned. This method is only any good for MOVING not COPYING Back to the drawing board... 2) Backup-SPSite / Restore-SPSite I didn't believe this ...
1 comment
Read more