"Manoj Sharma" <ManojSharma@discussions.microsoft.com> wrote in message
news:27BA5FBF-960E-4EC7-8BB8-E8AD9EBB9D07@microsoft.com...
>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(CommandBehavior
> 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.