Timeout Expired using SqlBulkCopy when there are 300Million rows in SQL Server Database

sql sqlbulkcopy timeout

Question

When utilizing SQLBulkCopy under certain circumstances when there are 183M existing rows in the database, I wasn't seeing any issues putting around 15000 rows into the SQL Server database.

However, I encountered the problem when there were 300M existing records in the database:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Keep in mind that there are no rules or restrictions. Just a denormalized table, really.

using (var sqlBulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.TableLock, null))
            {
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_HISTORY_PARMETER_ID,
                    SqlServerDatabaseStrings.SQL_FIELD_HISTORY_PARMETER_ID);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_SOURCE_TIMESTAMP,
                    SqlServerDatabaseStrings.SQL_FIELD_SOURCE_TIMESTAMP);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_VALUE_STATUS,
                    SqlServerDatabaseStrings.SQL_FIELD_VALUE_STATUS);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_ARCHIVE_STATUS,
                    SqlServerDatabaseStrings.SQL_FIELD_ARCHIVE_STATUS);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_INTEGER_VALUE,
                    SqlServerDatabaseStrings.SQL_FIELD_INTEGER_VALUE);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_DOUBLE_VALUE,
                    SqlServerDatabaseStrings.SQL_FIELD_DOUBLE_VALUE);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_STRING_VALUE,
                    SqlServerDatabaseStrings.SQL_FIELD_STRING_VALUE);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_ENUM_NAMEDSET_NAME,
                    SqlServerDatabaseStrings.SQL_FIELD_ENUM_NAMEDSET_NAME);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_ENUM_NUMERIC_VALUE,
                    SqlServerDatabaseStrings.SQL_FIELD_ENUM_NUMERIC_VALUE);
                sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_ENUM_TEXTUAL_VALUE,
                    SqlServerDatabaseStrings.SQL_FIELD_ENUM_TEXTUAL_VALUE);

                sqlBulkCopy.DestinationTableName =
                    SqlServerDatabaseStrings.SQL_TABLE_HISTORYSAMPLEVALUES;
                sqlBulkCopy.WriteToServer(historySampleValuesDataRow);
                sqlBulkCopy.Close();

            }

Any ideas as to what's going on? By the way, I use SQL Server Standard.

1
0
10/23/2014 3:02:20 AM

Accepted Answer

Your sqlBulkCopy object has two helpful features that you may use.

  • BulkCopyTimeout: The timeout value, which by default is 30 seconds. To completely turn off the timeout, set this value to 0.
  • BatchSize: To avoid the timeout problem, inserts the entries in batches of maybe 1MM at a time rather than directly adding 300MM into the database (and ease load a bit)

References:

0
10/23/2014 2:54:38 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow