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

TPG Apps Highlights - Risk Matrix #projectonline #projectserver #risk

This post is the first of a series to highlight the apps available for Project Server and Project Online from the SharePoint store  ( https://store.office.com/search.aspx?productgroup=SharePoint&qu=tpg ) and direct via your local TPG office. The first of this series will look at the s imple plug-and-play apps that all users of Project Online can make use of quickly and easily.   T hese are: Risk Matrix  Milestone Trend Analysis (MTA) WBS Chart viewer Next we will focus on the challenge of  Resource Request Supply and Demand by demonstrating our more recent TeamLink and TeamManager apps. Team Manager App is a Resource Manager/Owner app for allocating resource supply to Projects and BAU activities and monitoring demands against commitments Team Link App is a PM tool for monitoring Project demands vs the supply provided by the Resource Managers  Finally I will highlight some of the benefits of our integration tools when used in the context of Project Online
Post a Comment
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 w
1 comment
Read more

Migration Project Server (2010 to 2019) Issue 1 - Upgrading from 2013 to 2016 - Error encountered while migrating project data in Content database. The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users

I was intending to write a set of posts on the topic of migrating SharePoint and Project Server from 2010 on premise through 2013, 2016 to 2019 Azure, using SQL Managed Instance as the backend SQL Service.  This set of posts are still getting drafted and updated as we move through this cycle, but I came across a huge blocker this week that I wanted to post on, whilst it was fresh. Our Azure migration machines (2013 and 2016) are: - dual core 2.3ghz - 28GB Ram 2013 server is Windows Server 2012 R2 running SQL 2012 SP1 2016 server is Windows Server 2016 running SQL 2014 SP1 The Project Server dataset we are dealing with here is c.100GB (50% archive data). The uplift from SP/PS 2010 to 2013 went without a hitch over a 2.5hr period which was a huge win.  However when attempting to upgrade to 2016 we hit a hitch. Firstly some background:  When you perform the Migrate-SPProjectDatabase command, the following will happen (well it certainly did to us) - the ProjectWebApp DB fro
1 comment
Read more