locked
SqlTransaction exception when applying SqlSyncScopeProvisioning for big tables RRS feed

  • Question

  • I got this error message when applying SqlSyncScopeProvisioning for a big table (2 millions rows). SQL Server Profiler shows the query stops when inserting rows  into it's tracking table. It always times out after ~76 seconds.

    _tracking table Insertion Query:
    "INSERT INTO [Fill_tracking] ([FillId], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [trunc]) SELECT [base].[FillId], NULL, 0, @@DBTS+1, NULL, 0, 0, [base].[trunc] FROM [Fill] [base] LEFT OUTER JOIN [Fill_tracking] [side] ON [base].[FillId] = [side].[FillId] WHERE [side].[FillId] IS NULL"

    _tracking table Creation query:
    "CREATE TABLE [Fill_tracking] ([FillId] int NOT NULL, [update_scope_local_id] int NULL, [scope_update_peer_key] int NULL, [scope_update_peer_timestamp] bigint NULL, [local_update_peer_key] int NOT NULL, [local_update_peer_timestamp] timestamp NOT NULL, [create_scope_local_id] int NULL, [scope_create_peer_key] int NULL, [scope_create_peer_timestamp] bigint NULL, [local_create_peer_key] int NOT NULL, [local_create_peer_timestamp] bigint NOT NULL, [sync_row_is_tombstone] int NOT NULL, [restore_timestamp] bigint NULL, [last_change_datetime] datetime NULL, [trunc] bit NULL)"

    The same code works fine with other 3 small tables (total around 10,000 rows).

    error:
         This SqlTransaction has completed; it is no longer usable. 

    stack trace:
     at System.Data.SqlClient.SqlTransaction.ZombieCheck()
       at System.Data.SqlClient.SqlTransaction.Rollback()

       at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.Apply(SqlConnection connection)
       at DatabaseSync.SyncHelper.ConfigureSqlSyncProvider(String scopeName, String[] tableList, SqlConnection conn, Boolean isServer) in C:\projects

    I changed connectionstring timeout to 0. It didn't work. Does anyone know how it occurs?

    Thanks,

    -jshaqd
    • Moved by Max Wang_1983 Thursday, April 21, 2011 10:24 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, November 30, 2009 11:40 PM

Answers

  • Hi,

    There is another way to workaround this limitation. You can set SqlSyncTableProvisioning.PopulateTrackingTable = DbSyncCreationOption.Skip before calling SyncSyncScopeProvisioning.Apply(). With the TSQL command that you found in the the SQL profile, you can directly run it in SSMS to popluate the tracking table for your existing records.


    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, December 3, 2009 9:38 PM
    Moderator

All replies

  • Can you try to insert such rows into a dummy tracking table using a sql connection?
    I am trying to see if this is something related to the sync queries or the sql server/connection itself outside of the sync provisioning.
    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, December 1, 2009 1:10 AM
  • I can sync 3 smaller tables with no issue.

    Thanks,

    -jshaqd
    Tuesday, December 1, 2009 5:26 PM
  • I meant, can you try to install the 2 million rows that you mention and narrow down whether it is a sync provisioning issue or a sql connection issue.
    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, December 1, 2009 9:38 PM
  • I can insert them into database in 42 seconds.

    -jshaqd
    Tuesday, December 1, 2009 11:02 PM
  • Look at the stack, it appears that the operation completed but with errors and is not a timeout issue. Can you check if you received any SQL errors?


    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, December 3, 2009 6:28 AM
  • Hi,

    It is not a supported scenario to provision a table with a large number of records. The internal SqlCommand is using the default SqlCommand.CommandTimeout value - 30 seconds. Please consider provisioning an empty table first and then insert rows into the table.

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, December 3, 2009 8:37 PM
    Moderator
  • Hi,

    There is another way to workaround this limitation. You can set SqlSyncTableProvisioning.PopulateTrackingTable = DbSyncCreationOption.Skip before calling SyncSyncScopeProvisioning.Apply(). With the TSQL command that you found in the the SQL profile, you can directly run it in SSMS to popluate the tracking table for your existing records.


    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, December 3, 2009 9:38 PM
    Moderator

  • I am thinking about that. I would capture the scripts from SqlSyncTableProvisioning and create a separate SqlCommand to run the insert _tracking table query in code. Hopefully that would work.

    Thanks a lot for your advice.

    -jshaqd
    Thursday, December 3, 2009 9:43 PM
  • solved. Thanks a lot.

    -jshaqd
    Tuesday, December 8, 2009 5:30 PM