Large bulk inserts cause transaction aborts or timeouts

Question

Problem

When doing a bulk insert with several hundred thousand rows over two databases (programmatically, same instance, same schema) and about 17 tables per database, I nearly always receive a Transcaction Aborted or a Timeout Expired exception.

How

Using a EntityFramework 6 data context, I run a SqlCommand on the database.

using (var tran = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
    context.Database.ExecuteSqlCommand(
        "BULK INSERT " + table +
        " FROM '" + Directory +
        table + ".csv' WITH ( DATAFILETYPE = 'widechar', FIRSTROW = 2, FIELDTERMINATOR = '[TERM]', ERRORFILE = '" +
        Logfile + DateTime.Now.ToString(@"yyyy-MM-dd") + "-R" + DateTime.Now.Second + DateTime.Now.Millisecond +
        ".txt', ROWTERMINATOR = '\n', KEEPNULLS, TABLOCK )"); 

    tran.Complete();
}

Setting timeout values

To combat the problems I've been facing I have set both Command and Transaction timeouts to eighteen minutes. Either exception occurs long before said eighteen minutes have passed.

context.Database.CommandTimeout = 1080;
TransactionOptions transactionOptions = new TransactionOptions();
transactionOptions.Timeout = TimeSpan.FromMinutes(18);

Full exceptions

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__58()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__57()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommand(TransactionalBehavior transactionalBehavior, String commandText, Object[] parameters)
   at System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
   at System.Data.Entity.Database.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
   at System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[] parameters)
   at MyProject.Updaters.MSSQL.Csv.ImportCsvFiles() in c:\Users\MyUser\Desktop\MyProject2\XMLtoDBcron\Updaters\MSSQL\Csv.cs:line 48
ClientConnectionId:fef47d9c-3e13-444f-939b-f9bd570abb36

+

System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.TimeoutException: Transaction Timeout
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionStateAborted.BeginCommit(InternalTransaction tx, Boolean asyncCommit, AsyncCallback asyncCallback, Object asyncState)
   at System.Transactions.CommittableTransaction.Commit()
   at System.Transactions.TransactionScope.InternalDispose()
   at System.Transactions.TransactionScope.Dispose()
   at MyProject.Updaters.MSSQL.Csv.InsertCsvData() in c:\Users\MyUser\Desktop\MyProject2\XMLtoDBcron\Updaters\MSSQL\Csv.cs:line 109
   at MyProject.Updaters.MSSQL.Csv.ImportCsvFiles() in c:\Users\MyUser\Desktop\MyProject2\XMLtoDBcron\Updaters\MSSQL\Csv.cs:line 74

What to do

I couldn't find any settings or similar within SQL Server (using SSMS). Any extra data you request to aid in providing an answer will be given.

Answer 1

Regarding timout during sql commands execution I would recommend you to set CommandTimeout via ObjectContext wrapped within DbContext:

((IObjectContextAdapter)context).ObjectContext.CommandTimeout = 1080;

This approach always worked for me.