Getting This SqlTransaction has completed; it is no longer usable for Large data

[arjunbharara]

Dotmim.Sync.SyncException: [InternalApplyChangesAsync]..[InternalApplyChangesAsync]..[InternalApplyTableChangesAsync]..This SqlTransaction has completed; it is no longer usable. ---> System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. at Microsoft.Data.SqlClient.SqlTransaction.ZombieCheck() at Microsoft.Data.SqlClient.SqlTransaction.Commit() at Dotmim.Sync.DbConnectionRunner.d__29.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Dotmim.Sync.BaseOrchestrator.<>c__DisplayClass1_1.<b__5>d.MoveNext() --- End of inner exception stack trace --- at Cluster.Manager.DotmimSync.DotmimSyncProvider.Sync(ClusterServerDbo localServerDbo, ClusterServerDbo remoteServerDbo, Boolean decryptConnection, String dbSyncScope) at Cluster.Manager.ServerManager.DoDBSync(Boolean overideModeCheck)

[arjunbharara]

[Mimetis]

No idea, need a sample to reproduce, to be able to help you

[arjunbharara]

Thank you for your response; the issue was resolved when I upgraded to the latest version.

Currently, I’m experiencing performance issues during synchronization. I’m syncing around 37,000 rows, with the table size being approximately 1.2 GB. The process takes a minimum of 5 minutes to complete. Is this expected, or am I encountering performance bottlenecks?

Here’s my current SyncOptions configuration:
var syncOptions = new SyncOptions
{
CleanMetadatas = true,
DisableConstraintsOnApplyChanges = true,
DbCommandTimeout = 360,
BatchSize = 50000
};

Even when I set TransactionMode to PerBatch, the performance does not improve. Could you provide suggestions to optimize the synchronization process further?

[Mimetis]

How many columns do you have in your table ?

[arjunbharara]

The table ApplicationDefi contains approximately 40 rows. When 5000 new rows are inserted into this table, an additional 30,000 rows are inserted into the AppIcon table, which is related to ApplicationDefi.

The AppIcon table stores image data and has six columns, with a total data size of around 1 GB.

[Mimetis]

Is it possible to have an example I can use to reproduce the error ?

[arjunbharara]

I am currently facing an exception while filtering data. The exception thrown is:

Dotmim.Sync.FilterParamColumnNotExistsException: The parameter 'IsSystemUser' does not exist as a column in the table 'MMCUserDbo'.

This occurs when I attempt to provision, and the following code is executed:

if (setup.Tables[tableForFilter.TableName] != null)
{
var filter = new SetupFilter(tableForFilter.TableName);
filter.AddParameter(clusterColumnSyncDbo.ColumnName, tableForFilter.TableName);
filter.AddCustomWhere(clusterColumnSyncDbo.Filter);
setup.Filters.Add(filter);
}
The filter value I am using is: [side].[IsSystemUser] = 0.

Although my table contains the column IsSystemUser, I am still encountering this exception. Could you help me resolve this issue?

[arjunbharara]

Do i have to add the parameters as i do noy have predefined values for them

[Mimetis]

if clusterColumnSyncDbo.ColumnName is not part of tableForFilter.TableName, you can use the AddParameter(string parameterName, DbType type, bool allowNull = false, string defaultValue = null, int maxLength = 0) method where you can specify a parameter that is not a column

[arjunbharara]

clusterColumnSyncDbo.ColumnName is part of the tableForFilter. the clusterColumnSyncDbo.ColumnName is 'IsSystemUser'
for which it is throwing error.
Also it id not creating a column for parameter in my Tracking table.

[Mimetis]

I don't know, I need more info, like the table schema and the custom where clause, to be able to reproduce the error

[arjunbharara]

table schema
USE [EDCDB_arjun1]
GO
/****** Object: Table [dbo].[MMCUserDbo] Script Date: 28-11-2024 16:26:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MMCUserDbo](
[Id] [uniqueidentifier] NOT NULL,
[UserName] nvarchar NOT NULL,
[UserDisplayName] nvarchar NOT NULL,
[AdServerId] [uniqueidentifier] NULL,
[UserType] [int] NULL,
[IsSystemUser] [bit] NOT NULL,
[OrganizationNodeId] [uniqueidentifier] NOT NULL,
[UserPrincipalName] nvarchar NULL,
[EmailId] nvarchar NULL,
[PhoneNumber] nvarchar NULL,
CONSTRAINT [PK_MMCUser] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MMCUserDbo] WITH NOCHECK ADD CONSTRAINT [FK_AuthServerId] FOREIGN KEY([AdServerId])
REFERENCES [dbo].[AuthenticationServerDbo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[MMCUserDbo] CHECK CONSTRAINT [FK_AuthServerId]
GO

ALTER TABLE [dbo].[MMCUserDbo] WITH NOCHECK ADD CONSTRAINT [FK_MMcUser_OrganizationNode] FOREIGN KEY([OrganizationNodeId])
REFERENCES [dbo].[OrganizationNodeDbo] ([Id])
GO

ALTER TABLE [dbo].[MMCUserDbo] CHECK CONSTRAINT [FK_MMcUser_OrganizationNode]
GO

custom where condition :- "[side].[IsSystemUser]= 0"
parameter name is :-

getting this error while provisioning
Dotmim.Sync.SyncException: '[ProvisionAsync].Provision:TrackingTable, StoredProcedures, Triggers, ScopeInfo, ScopeInfoClient.Overwrite:False..[InternalProvisionServerAsync].Provision:TrackingTable, StoredProcedures, Triggers, ScopeInfo, ScopeInfoClient.Overwrite:False..[InternalProvisionAsync].Provision:TrackingTable, StoredProcedures, Triggers, ScopeInfo, ScopeInfoClient.Overwrite:False..[InternalCreateStoredProceduresAsync].Table:MMCUserDbo.Overwrite:False..[InternalCreateStoredProcedureAsync].Table:MMCUserDbo.StoredProcedure:SelectInitializedChangesWithFilters..Invalid column name 'IsSystemUser'.'

[Mimetis]

I think you don't have to use AddCustomWhere, A classic filter should be fine, in my opinion.

Side note: I see you are using CASCADE DELETE. It's not working with DMS as the triggers are not triggered when a cascaded row is deleted

I used this code, that is correctly working:

USE [master]
GO 

-- Server database
if (exists (select * from sys.databases where name = 'AdventureWorks'))
Begin
	ALTER DATABASE [AdventureWorks] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [AdventureWorks]
End
Create database [AdventureWorks]
Go
-- Client database. No need to create the schema, Dotmim.Sync will do
if (exists (select * from sys.databases where name = 'Client'))
Begin
	ALTER DATABASE [Client] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [Client]
End
Create database [Client]
Go

USE [AdventureWorks]
GO
CREATE TABLE [dbo].[MMCUserDbo](
[Id] [uniqueidentifier] NOT NULL,
[UserName] nvarchar(50) NOT NULL,
[UserDisplayName] nvarchar(50) NOT NULL,
[AdServerId] [uniqueidentifier] NULL,
[UserType] [int] NULL,
[IsSystemUser] [bit] NOT NULL,
[OrganizationNodeId] [uniqueidentifier] NOT NULL,
[UserPrincipalName] nvarchar(50) NULL,
[EmailId] nvarchar(50) NULL,
[PhoneNumber] nvarchar(50) NULL,
CONSTRAINT [PK_MMCUser] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Insert into [MMCUserDbo] Values 
	(newid(), 'John', 'John', NEWID(), 1, 1, NEWID(), 'John', '[email protected]', '01-01-01-01'),
	(newid(), 'Jane', 'Jane', NEWID(), 1, 0, NEWID(), 'Jane', '[email protected]', '01-01-01-01')

And the sync code:

var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(ServerDb);
var clientProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(ClientDb);
var options = new SyncOptions();

var setup = new SyncSetup("MMCUserDbo");

var filter = new SetupFilter("MMCUserDbo");
filter.AddParameter("IsSystemUser", "MMCUserDbo");
filter.AddWhere("IsSystemUser", "MMCUserDbo", "IsSystemUser");
setup.Filters.Add(filter);

var progress = new SynchronousProgress<ProgressArgs>(s =>
    Console.WriteLine($"{s.ProgressPercentage:p}:  " +
    $"\t[{s?.Source?[..Math.Min(4, s.Source.Length)]}] {s.TypeName}: {s.Message}"));

var agent = new SyncAgent(clientProvider, serverProvider, options);

do
{
    try
    {
        Console.ForegroundColor = ConsoleColor.Green;
        var parameters = new SyncParameters
        {
            { "IsSystemUser", true   }
        };
        var s = await agent.SynchronizeAsync(setup, parameters, progress: progress);
        Console.WriteLine(s);
    }
    catch (SyncException e)
    {
        Console.ResetColor();
        Console.WriteLine(e.Message);
    }
    catch (Exception e)
    {
        Console.ResetColor();
        Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
    }

    Console.WriteLine("--------------------");
}
while (Console.ReadKey().Key != ConsoleKey.Escape);

[arjunbharara]

I am facing an issue where I need to apply multiple complex filters on a single table. Some of these filters involve calling functions, such as:

1.[dbo].IsTeamValidforDCDRSync = 1
And
2. [base].[Id] IS NULL OR ([base].[ClientType] = 5 AND [side].[PoolId] IN (SELECT [innerpool].[Id] FROM [dbo].[PoolDbo] [innerpool] WHERE [innerpool].[Id] = [side].[PoolId] AND [dbo].[IsPoolValidforDCDRSync]([innerpool].[DVMCreationType], [innerpool].[PoolCreationType], [innerpool].[PoolType], [innerpool].[PoolProviderType]) = 1))
Because of the complexity and reliance on scalar function calls, I cannot use a simple WHERE clause. Additionally, while testing a previous condition, I found an interesting behavior: when I removed [side] from the filter [side].[IsSystemUser] = 0, the provisioning process was successful.

Do you have any suggestions or alternatives for implementing these filters more efficiently?

like this i have lot of table for filters

[Mimetis]

Please open new issue if needed and you found a bug in DMS, or go to the discussion to ask questions.

Seems you did not confirmed my solution works (or confirm it is) but I will close the issue as it's not relevant to the title
Thanks

[arjunbharara]

sorry ,i totally forgot to answer that
yes it is working fine like that but i have lots of filters and those filters are long and complex that is why i can't use simple where condition.