The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction. Please resolve

Question

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.\n   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I am continously  getting this exception when ever large amount of data is processed.

We have developed an application in C# for archiving the data.

Statistics regarding data
We are testing it on 6 tables having  total  40 columns of datatype  varchar,decimal and newsequentialguid .
Each table contains 4 million row among which 2 million rows satisfy the archiving criteria .

While testing we are keeping the database and application on same server.

Configuration  regarding MSDTC (dcomcnfg)
Transaction Timeout :3600

Default Co-ordinator :Local

Security Configuration
Network DTC Access : Enabled
Allow Remote Client : Enabled
Allow Remote administration :Enabled
Allow inbound : Enabled
Allow outbound :Enabled
No Authentication Required :Checked
Enable TIP transaction :Enabled
Enable XA Transactions :Enabled


Machine
Windows XP SP2 ,.NET Framework 3.5 , Sql server 2005 Standard edition

Firewall service in not running .

Now the public function archive is executed in thread
In this function under  the transactionscope (system.transaction)
we first archive the data on to the file system and then delete the data from the selected tables.
The problem occurs when we are trying to delete the data , in first few tables the deletion logic works( we are trying to delete the rows in batches , by using SET rowcount 50000) but then after it hangs and the query fails giving the error .

Please help

Answer 1

This really looks like a timeout issue.

Are you using System.Transactions.TransactionScope? Are you specifying a timeout when constructing the TransactionScope? The default timeout is 60 seconds, so you might try increasing this if your database work is around the same duration.

-Clark

---

SDET (Transactions Team)

Answer 2

Hi Clark

Thanks for your efforts
Yeah i had increased the timeout but even then the problem didn't get resolved.
So then in machine.config i added a tag

 <system.transactions>
        <!--
            <defaultSettings
                distributedTransactionManagerName = "" [String]
                timeout = "00:01:00" [HH:MM:SS]
            />
        -->
        <defaultSettings distributedTransactionManagerName="" timeout="02:01:00" />
        <!--
            <machineSettings
                maxTimeout = "01:30:00" [HH:MM:SS]
            />
        -->
        <machineSettings maxTimeout="02:10:00" />
       </system.transactions>

Actually these setting were present in machine.config.default , just moved the tag from there to machine.config

And everything started working .


Thanks
Shweta Shukla

Answer 3

shwetashukla,

Did you update the machine.config on the DB server, or the client machine, or both?

Thanks!