MOSS: Excessive Sync errors on Sharepoint 2007 Front End Web Server

Posted by Michael Coleman
October 28, 2009

In several MOSS deployments with multiple front ends, I have noticed many sync issues in the event logs.
A runtime exception was detected. Details follow.
Message: Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated.
Techinal Details:
System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command)
at Microsoft.Office.Server.UserProfiles.WSSSynchSqlSession.SynchExecuteNonQuery(SqlCommand cmd, Boolean throwOnFail)
at Microsoft.Office.Server.UserProfiles.WSSSynchSqlSession.SynchExecuteNonQuery(SqlCommand cmd)
at Microsoft.Office.Server.UserProfiles.SiteSynchronizer.WriteChangeLogConsumed()
at Microsoft.Office.Server.UserProfiles.SiteSynchronizer.Synch()
at Microsoft.Office.Server.Diagnostics.FirstChanceHandler.ExceptionFilter(Boolean fRethrowException, TryBlock tryBlock, FilterBlock filter, CatchBlock catchBlock, FinallyBlock finallyBlock)

Why cant anything ever just work with Microsoft?
 
It could be do to backing up and restoring databases to different applications.  It causes the GUID for the content DB to be the same, causing a conflict during synchronization.
 
To get a list of all the GUIDs with sync issues you can use the following command from one of the front ends:

stsadm -o sync -listolddatabases 0

You can then use this information to query SQL and see which databases are having issues.  OR, you can use this VBS script to get the information:
Set Args = WScript.Arguments
Connstr = "Driver={SQL Server};Server=SQL2005;Database=Config_DB"
Set Connect = CreateObject("ADODB.Connection")
Connect.Open Connstr
strInput = Args.Item(0)
Set DB = Connect.Execute("SELECT Id, ClassId, Name, Status, Version, Properties FROM Objects WHERE (Id = '" & strInput & "')")
Do Until DB.Eof
WScript.Echo Db("Id")
WScript.Echo Db("ClassId")
WScript.Echo Db("Name")
WScript.Echo Db("Status")
WScript.Echo Db("Version")
'WScript.Echo Db("Properties")
DB.MoveNext
Loop

To help get a baseline in our environment, I just ran:


stsadm -o sync -deleteolddatabases 0

This doesnt remove the databases, it removes the sync error.  I let the servers run over the weekend, then checked again.
 
To fix the issue, we detatched the databases in order to issue the a new GUID.
 
Detatching the DB will not delete it, just remove it from the farm.
 
To detach:

stsadm -o preparetomove -contentdb -contentdb : -site
stsadm -o deletecontentdb -url -databaseserver -databasename
To reattach:
stsadm -o addcontentdb -url -databasename -databaseserver

I hope that saves someone else some time.

SharePoint

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader or join our newsletter and receive future articles emailed directly to you.

Comments

No comments yet.

Leave a comment

(required)

(required)