DTC Distributed transaction completed Issue | Multiple updates in

[Manoj Sharma]

I am performance testing some batch processing engines. These are written in
..NET and SQL Server and are typically concerned with generating XML files out
of data retrieved from the database.

I have two machines one running IIS Server and the other SQL Server.

Problem Description:

One of the engines is failing with the following exception:

Exception: System.Data.SqlClient.SqlException
Message: Distributed transaction completed. Either enlist this session in a
new transaction or the NULL transaction.

Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
This engine does multiple hits in the database during processing. It runs
successfully if the data is not huge. When run on peak data, the engine fails
and gives the above mentioned exception (increase in data increases the
database hits manifold)
Steps Already Taken:

1. Checked that SQL Server 2000 service pack 3 (sql2ksp3.exe) is installed.
(Microsoft site refers to the problem with distributed transactions in SQL
Server 2000 due to which the above mentioned exception comes up and asks to
install service pack 3 to fix it)

2. Found out that it’s not a code defect. This is because if the engine is
run multiple times, each time the exception comes up at different places
(evident from the logs)

3. Checked that the transaction log file is not getting full. Unrestricted
growth option is selected.

I have noticed that it’s the number of updates happening in the same
transaction which is probably causing this issue. The engine is performing
huge number of updates on a couple of tables and most of the times the engine
fails after same number of updates.

Is there any limitation on the number of database hits per transaction? In
the .NET code, we have used the following code to start/end transaction which
is called at the start/end of DB update.

private void StartTransaction()
{
ServiceConfig config = new ServiceConfig();
config.Transaction = TransactionOption.Required;
ServiceDomain.Enter(config);
}

private void AbortTransaction()
{
if(ContextUtil.IsInTransaction)
{
ContextUtil.SetAbort();
}
ServiceDomain.Leave();
}

private void CompleteTransaction()
{
if(ContextUtil.IsInTransaction)
{
ContextUtil.SetComplete();
}
ServiceDomain.Leave();
}

Do you find anything wrong with this?

If anyone has pointers to fix this issue please reply back.