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

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

i wasn't having problems in inserting ~15000 rows in sql server database when using sqlbulkcopy in conditions there 183m existing rows in database.

but when there 300m existing rows in database, experienced exception:

timeout expired. timeout period elapsed prior completion of operation or server not responding.

note there no constraints or anything.it denormalized table.

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 on happening? btw, sql server standard i'm using

you have 2 properties on sqlbulkcopy object can help you

bulkcopytimeout: value of timeout, 30 seconds default. set value 0 disable timeout entirely. batchsize: instead of inserting 300mm straight table, inserts records @ batched size, maybe 1mm/time prevent timeout issue (and ease load bit)

references:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.bulkcopytimeout(v=vs.110).aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.batchsize(v=vs.110).aspx

sql timeout sqlbulkcopy