Monday, January 9, 2012

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!