SharePoint: PSConfig Fails and User Profile Sync service is stuck on starting

  • Article

Issue Description

SharePoint Products Configuration wizard fails and User Profile Sync service gets stuck on starting.

Cause

Query threshold in SQL was configured to be limited to 300. This can be configured using the Use Query Governor to Prevent long-running Queries option under Connections in SQL Server properties.

Steps for Resolution

-> While running psconfig.exe -cmd upgrade -inplace b2b -wait -force command, we see the below error in the upgrade log:

[PSCONFIG] [SPUpgradeSession] [INFO] [Date and Time]: SPContentDatabase Name=rei80_content

[PSCONFIG] [SPUpgradeSession] [ERROR] [Date and Time]: Upgrade [SPContentDatabase Name=rei80_content] failed.

[PSCONFIG] [SPUpgradeSession] [INFO] [Date and Time]: SPContentDatabase Name=rei80_content

[PSCONFIG] [SPUpgradeSession] [ERROR] [Date and Time]: Exception: The query has been canceled because the estimated cost of this query (974) exceeds the configured threshold of 300. Contact the system administrator.

[PSCONFIG] [SPUpgradeSession] [INFO] [Date and Time]: SPContentDatabase Name=rei80_content

[PSCONFIG] [SPUpgradeSession] [ERROR] [Date and Time]: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.SharePoint.Utilities.SqlSession.ExecuteScript(TextReader textReader, Int32 commandTimeout)

at Microsoft.SharePoint.Utilities.SqlSession.ExecuteScript(String path, Int32 commandTimeout)

at Microsoft.SharePoint.Upgrade.SPUtility.ExecuteSqlFile(SqlSession sqlSession, ISqlSession isqlSession, SqlFile sqlFileId, Int32 timeOut)

at Microsoft.SharePoint.Upgrade.SPContentDatabaseSequence.Upgrade()

at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse)

-> While starting User Profile Sync service we noticed similar kind of error in ULS Logs:

[Date and Time] OWSTIMER.EXE (0x127C) 0x0C68 SharePoint Portal Server User Profiles 9q15 High UserProfileApplication.SynchronizeMIIS: Failed to configure ILM, will attempt during next rerun. Exception: System.Data.SqlClient.SqlException: The query has been canceled because the estimated cost of this query (410) exceeds the configured threshold of 300. Contact the system administrator. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.IdentityManagement.SetupUtils.IlmWSSetup.ExecuteSQL(String queryString) at Microsoft.IdentityManagement.SetupUtils.IlmWSSetup.LoadSQLFile(String FileName) at Microsoft.IdentityManagement.SetupUtils.IlmWSSetup.IlmBuildDatabase() at Microsoft.Office.Server.UserProfiles.Synchronization.ILMPostSetupConfiguration.ConfigureIlmWebService(Boolean existingDatabase) at Microsoft.Office.Server.Administration.UserProfileApplication.SetupSynchronizationService(ProfileSynchronizationServiceInstance profileSyncInstance). c7d9f2d7-ba92-4a32-a836-aebc3012c249

[Date and Time] OWSTIMER.EXE (0x127C) 0x0C68 SharePoint Portal Server User Profiles 9i1u Medium UserProfileApplication.SynchronizeMIIS: End setup for 'User Profile Service Application'. c7d9f2d7-ba92-4a32-a836-aebc3012c249

[Date and Time] OWSTIMER.EXE (0x127C) 0x11A0 SharePoint Foundation Database 5586 Critical Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below. Could not find stored procedure 'proc_ProcessStorageMetricsChanges'. a45a9a05-a196-445a-be02-141b4fe01d52[Date and Time] OWSTIMER.EXE (0x127C) 0x11A0 SharePoint Foundation Timer 6398 Critical The Execute method of job definition Microsoft.SharePoint.Administration.SPStorageMetricsProcessingJobDefinition (ID e6ebbee7-d68c-4535-8ea0-b951bac40801) threw an exception. More information is included below. Could not find stored procedure 'proc_ProcessStorageMetricsChanges'. a45a9a05-a196-445a-be02-141b4fe01d52

[Date and Time] OWSTIMER.EXE (0x127C) 0x0FE4 SharePoint Foundation Database 5586 Critical Unknown SQL Exception 208 occurred. Additional error information from SQL Server is included below. Invalid object name 'TVF_Sites_NoLock_Id'. f9f8094f-83a0-4515-afd2-7c4ae5591646

-> Connect to the SharePoint instance in SQL Server Management Studio, right-click on the Instance name and click Properties.

-> Click Connections and uncheck the 'Use query governor to prevent long-running queries' option.

-> Save the changes and run PSconfig command on all SharePoint servers and start the User Profile Sync service