SqlException: Timeout expired error

Question

Hello,

I have been using the Sync Framework for my application for several months now without any problems. But today when i tried to recreate/sync my local database i got this error on 1 of my tables and only on this specific table:

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

The error is thrown in the GetChanges() function which is placed in my service looking like this:

public virtual SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
    {
      try
      {
        var tmp = this._serverSyncProvider.GetChanges(groupMetadata, syncSession);
        return tmp;
      }
      catch (SyncException se)
      {
        if (se.ErrorNumber == SyncErrorNumber.StoreException)
        {
          var sqlexcept = se.InnerException as SqlException;
          if (sqlexcept != null && sqlexcept.Class == 16 && sqlexcept.State == 3)
          {
            throw new ApplicationException("ChangeTrackingCleanedUp", se);
          }
        }
        throw se;
      }
      catch (Exception ex)
      {
        throw ex;
      }
    }

The error is not catched in the if statement because the error thrown contains these values: Class = 11 and State = 0.

I solved the problem by disabling "Change Tracking" on the specific table and then enabling it again. I don't see this as a secure and solid solution to the problem so i would like to know if you have any suggentions for what could have caused this error?

Answer 1

have you checked for any blocking transaction during the timeout or a change tracking clean-up operation on-going?

also, from Sync Fx doc (highlighting is mine):

"We strongly recommend that you use snapshot transactions when you are querying for change information. This helps ensure the consistency of change information and avoids race conditions that are related to the background cleanup task. For more information about snapshot isolation, see "Isolation Levels in the Database Engine" in SQL Server 2008 Books Online."

Answer 2

I tried to use snapshots transactions on the database but its not working. The error is only cast on one specific table. 

I have two services which uses this table. One service that runs the sync and a service that also do changes to the specific table. Can this be the problem? If one service is using the table and then the other service try to do something to the table at the same time?

 

Im using the SqlSyncAdapterBuilder and i haven't found any example on how to use snapshots transactions with this. Is there any code i should add to my code to get the snapshot transaction to work when i use the builder?

Here is the builder code i use for the specific table:

employeeBuilder.TableName = "dbo.Employees";
      employeeBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;

      string filterClause = "dbo.Employees.Id IN (SELECT EmployeeId FROM EmployeeProjects WHERE JobNo=@JobNo)";
      //employeeBuilder.FilterClause = "Employees.JobNo=@JobNo";
      employeeBuilder.FilterClause = filterClause;
      employeeBuilder.FilterParameters.Add(jobNoParameter);
      
      SyncAdapter employeeSyncAdapter = employeeBuilder.ToSyncAdapter();
      employeeSyncAdapter.TableName = "Employees";
      this.SyncAdapters.Add(employeeSyncAdapter);

 

Answer 3

it could be a problem if one of the service locks something that is needed by the other service.

to check, you can run sp_who or view the SQL Server Activity monitor if a service is blocking the other.