Migrate-SPProjectDatabase: Error encountered while migrating

Question

We did thorough testing on the Project Server Migration and found the SQL Server is not responding as expected.

 

Every time we run the PowerShell Migration command it aborts at the final stage while the database conversions in place, throwing the SQL error (below). We tried reinstalling SharePoint on all the servers in the farm to ensure the issue is not occurring from the SharePoint perspective, even took two steps back and repeated the migration from SharePoint 2010 to 2013 & 2016 respectively.

 

SQL First Error:

 

Migrate-SPProjectDatabase: 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. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

 

The latest error observed after the fresh SharePoint installation is as below: Our research on this resolution, clearly shows bout the SQL transaction logs for the Content database, Project Server databases & SQL lock resources. Please find the top MSDN & SQL resolution articles below:

 

Reference 1: Two Transaction Logs getting full - MSDN

 

Reference 2: Database-engine-cannot-obtain-a-lock-resource-at-this-time - SQLServerCentral

 

Latest Error:

 

Migrate-SPProjectDatabase : Error encountered while migrating project data in Content database. Action 16.0.34.0 of Microsoft.Office.Project.Server.Upgrade.PSDatabaseSequence failed.

At line:1 char:1

+ Migrate-SPProjectDatabase -DatabaseName ProjectWebApp2016DB -SiteCollection "htt ...

+ CategoryInfo : OperationStopped: (Microsoft.Offic...ProjectDatabase:PSCmdletMigrateSPProjectDatabase) [

Migrate-SPProjectDatabase], SPException

+ FullyQualifiedErrorId : Microsoft.Office.Project.Server.Cmdlet.PSCmdletMigrateSPProjectDatabase

 

Also, we have bigger problem now while creating any new web applications & site collections on the SharePoint server. As we tried creating New Web App for 6-7 times, every time we came across the below error.