CODEWITHSUNDEEP
c#linqlarge-filessqlbulkcopydml
aarona
aarona

Reputation: 37324

SqlBulkCopy.WriteToServer hangs Thread.Abort is called but not sure why

Given:

Some code:

SqlConnection con = null;
SqlTransaction transaction = null;

try
{
  con = getCon(); // gets a new connection object
  con.Open();
  transaction = con.BeginTransaction();

  var bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, transaction)
  {
    BatchSize = 1000,
    DestinationTableName = "Table1"
  };

  // assume that the BenchMark class is working
  b = new BenchMark("Table1");
  bulkCopy.WriteToServer(_insertTable1s.AsDataReader()); // _insertTables1s is a List<Table1>
  b.Complete();
  LogHelper.WriteLogItem(b);

  b = new BenchMark("Table2");
  bulkCopy.DestinationTableName = "Table2";
  bulkCopy.WriteToServer(_insertTable2s.AsDataReader()); // _insertTables2s is a List<Table2>
  b.Complete();
  LogHelper.WriteLogItem(b);

  // etc... this code does a batch insert into about 7 tables all having about 40,000 records being inserted.

  b = new BenchMark("Transaction Commit");
  transaction.Commit();
  b.Complete();
}
catch (Exception e)
{
  transaction.Rollback();

  LogHelper.WriteLogItem(
    LogLevel.Critical,
    LogType.DataProcessing,
    e.ToString());
}
finally
{
  con.Close();
}

The Problem:

On my local development environment, everything is fine. Its when I run this operation in the cloud that causes it to hang. Using the LogHelper.WriteLogItem method, I can watch the progress of this process. I observe it hang randomly on a particular table. No exception is thrown so the transaction isn't rolled back. Say it hangs on Table2 bulk insert. Using MS SQL Management Studio, I run queries on Table3, Table2 and Table1 with no issue (this means that the transaction was aborted?)

Since it hangs, I'll go rerun the process. This time it hangs sooner so I might get logs like this:

7755   Benchmark   LoadXML took 00:00:04.2432816
7756   Benchmark   Table1 took 00:00:06.3961230
7757   Benchmark   Table2 took 00:00:05.2566890
7758   Benchmark   Table3 took 00:00:08.4900921
7759   Benchmark   Table4 took 00:00:02.0000123

... it hangs on Table5 (because the BenchMark never completed). I go to run it again and the rest of the log looks like:

7780   Benchmark   LoadXML took 00:00:04.1203923

... and it hangs here now.

I'm using rackspace cloud hosting if that helps. I have been able to fix this in the past by deleting all the tables from my dbml file and readding them but this time its not working. I'm wondering if the amount of data being processed is causing the problem?

EDIT: The code in this example is run in an Asynchronous thread. I've found out that the Thread is Aborting for an unknown reason and I need to find out why to solve this problem.

Upvotes: 1

Views: 2312

Answers (2)

aarona
aarona

Reputation: 37324

Because this process is done asynchronously (i.e. a thread is kicked off to handle this) the thread has a problem which aborts it and that is why I get strange behavior where the code stalls at different places. I've solved this by completing this task synchronously (it works but its not ideal).

I guess the real issue is why my thread is aborting since I'm not aborting it in any of my code. I believe that its due to amount of data that is being processed, but I could be wrong.

Either way, I've solved my problem.

Upvotes: 1

davisoa
davisoa

Reputation: 5439

If you have admin to your server or database, you can run

SELECT * FROM sys.dm_tran_session_transactions

to see what transactions are currently active - From Pinal

Additionally, you can run sp_lock to make sure there isn't something blocking your transaction.

Upvotes: 1

Related Questions