Fix "Sorry, something went wrong. There was an exception in the Database." in SharePoint search.

Introduction

We don't know when what types of issue will occur in the SharePoint server. Everything is working fine but all of a sudden some functionality breaks - this is a common behavior in the SharePointing world. As an example, we can give, "Search service" and "User Profile Service". Here I will explain about one of the uncertain incidents we have seen in our server just in the recent past - all of a sudden we SharePoint search stopped working, we did all traditional ways of troubleshooting but nothings work, finally found a solution which I will explain here. I thought of sharing this with the community so that others might be benefitted. And I believe troubleshooting in SharePoint is one of the major skills where SharePointers struggle to deal with the uncertain error.

All of a sudden, when our SharePoint 2016 search is stopped working. From the browser we got the below error:

ULS log report from front end server:

SearchServiceApplicationProxy::IsUrlMappingCachedWithHash--Error occured: System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: There was an exception in the Database. Please retry your operation and if the problem presists, contact an administrator. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: Microsoft.Office.Server.Search.Query.Pipeline.SearchObjectDatabaseException: There was an exception in the Database. Please retry your operation and if the problem presists, contact an administrator.
at Microsoft.Office.Server.Search.Query.Pipeline.QueryPipelineErrorHandler.TranslateSqlException(SqlException ex)
at Microsoft.Office.Server.Search.Query.UrlMapping.UrlMappingDatabaseManager.GetUrlMap(Tuple`3 key)
at Microsoft.Office.Server.Search.Administration.SearchServiceApplication.IsUrlMappingCachedInternal(Guid subscriptionId, SPUrlZone zone, String siteCollectionHash)
at Microsoft.Office.Server.Search.Administration.SearchServiceApplication.IsUrlMappingCachedWithHash(Guid subscriptionId, SPUrlZone zone, String siteCollectionHash)
at SyncInvokeIsUrlMappingCachedWithHash(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs,...).

ULS log report from application with search server:

System.Data.SqlClient.SqlException (0x80131904): Procedure or function proc_MSS_GetUrlMapping has too many arguments specified.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Office.Server.Data.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock) ClientConnectionId:92965ffb-2fb0-4b24-8915-6d20c50568a1 Error Number:8144,State:2,Class:16

Analysis (solution)

Run the SharePoint Configuration wizard in Application with search server successfully, then the search starts working as normal.

Notes:

1. To be on safer side, before running the SharePoint Configuration wizard we must take following backups in the server:

Take server snapshot or image backup of all servers(including the SQL server) in the farm.
Take all service application database backup.
Take all content database backup.
Take SharePoint_Config database backup.

2. Need to remove the "WSS_UsageApplication" database from availbiltiy group:

If you have an availability group in place in the SQL, we need to remove the "WSS_UsageApplication" database (logging database) from availbiltiy group otherwise while running the SharePoint configuration wizard will get an error and will not be able to complete the wizard running successfully. And after successfully running the wizard we need to add back the "WSS_UsageApplication" database back to the availability group.

Summary

Thus, in this blog we have learned about how to fix the error "Sorry, something went wrong. There was an exception in the Database. Please retry your operation and if the problem persists, contact an administrator" in SharePoint 2016 search.