Error in SQL Server database synchronization using SyncServices and WCF

Question

I have database on the server - Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64). Configured to synchronize with a SQL Server Compact database client through WCF using Microsoft Synchronization Services for ADO.NET.

Sometimes this error began to emerge:

Microsoft.Synchronization.Data.SyncException: Unable to enumerate changes at the DbServerSyncProvider for table 'TABLE NAME' in synchronization group 'GROUP NAME'.

---> System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)

Table names are different each time.

Tell me please how to solve this problem.

Thanks.

tom evers tom evers · Accepted Answer · 2012-09-26T22:30:31

I believe that you will get this error if you haven't had a successful sync during the retention period you have specified on the database. See this link

We also get this occasionally when the sync process is unable to get data due to an open transaction or locking from another process. (I believe the fix for this is to SET READ_COMMITTED_SNAPSHOT ON:

ALTER DATABASE yourdatabasenamehere
SET READ_COMMITTED_SNAPSHOT ON

GO
)

Error in SQL Server database synchronization using SyncServices and WCF

1 Answers