Bulk Insert takes 4x as long on first operation of the day

Question

I do Bulk Inserts into a table with about 14 million rows at fiver minute increments during a 7 hour period during the day. These inserts take somewhere between 9-14 secs. However, the first insert always takes about 40 secs. Anyone know what SQL Server 2005 would be doing differently on the first insert into a table for that day?

From what I've read I should probably use the SqlBulkCopy class instead of just using a bulk insert in a stored procedure. Is that that the general consensus?

Answer 1

Do you have a truncate/shrink job that runs overnight?

If so then this means that your first operation will cause the file to grow, hence a wait while this happens.

Answer 2

A maintenance job that shrinks the database in the night may be the culprit. In such a case, database file will grow on your first insert and that will cause a delay on the insert.

Also, if you are doing large inserts, consider disabling and re-enabling indices.