SharePoint 2013 – User profile – ResetSynchronizationDatabase : Operation is not valid due to the current state of the object

SharePoint – User profile – ResetSynchronizationDatabase : Operation is not valid due to the current state of the object

Issue

I was trying to reset the SharePoint UPS sync database and came across below error message:

PS C:\> $upa.ResetSynchronizationDatabase()
 Exception calling "ResetSynchronizationDatabase" with "0" argument(s):
 "Operation is not valid due to the current state of the object."
 At line:1 char:1
 + $upa.ResetSynchronizationDatabase()
 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
 + FullyQualifiedErrorId : InvalidOperationException

Event ID: 3, 5586 

 .Net SqlClient Data Provider: 4060 :  : System.Data.SqlClient.SqlException: Cannot open database "SP_xxxx" requested by the login. The login failed.

Login failed for user 'xxxxxxx'.

.Net SqlClient Data Provider: 924 : UpdateRequestStatusForCompletedActionWorkflows : System.Data.SqlClient.SqlException: Database 'SP_XXX is already open and can only have one user at a time.

Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Database 'SP_xxxx is already open and can only have one user at a time.

Resolution

Sync Database is in SQL availability group or set to single user.



Remove Sync DB from SQL availability group and set DB to multi user and make sure User Profile Sync account(farm account) have DBOwner access of sync db.