This question already has an answer here:

We have a large database, with a size of approx 400GB. We are using bulk insert (from upstream system) with the following parameters:

`Description=DataDirect 6.0 SQL Server Native Wire Protocol`

The delta changes in the DB represent approx 70GB at month end. The transaction log file is falling over at 800GB (twice the size of the actual DB, where 800GB is the current size of the allocated transaction log drive). Autogrow is enabled on the transaction log, but we are unsure WHY it is becoming so large. Simple recovery is set.

Are there any parameters (either upstream on the bulk insert, or on the transaction log db ) we can fine tune?

marked as duplicate by Paul White Sep 3 '15 at 8:13

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from stackoverflow.com Aug 20 '12 at 13:24

This question came from our site for professional and enthusiast programmers.

  • Are you BULK INSERTing into a staging table, or directly into the target table? – Jon Seigel Aug 20 '12 at 13:31
  • The transactions are loaded in (up to) 1024 rows each, so don't think it would be determined as ONE large transaction. The bulk insert loads directly into the target table. Would that massively bloat the size of the transaction log? – Hamish Tonkin Aug 20 '12 at 14:14
  • Does your process include deleting all the data in all of the tables first, then bulk loading them? – CleanFill Aug 20 '12 at 17:02
  • No, the data is not deleted first, it is written straight into the existing tables (as they are delta updates, not full loads of all data) – Hamish Tonkin Aug 21 '12 at 10:26
  • 1
    What does your Log_Reuse_Wait show? Have you tried running that often when the log is growing really large to see what it's actually waiting on? This will be useful. – Ali Razeghi Nov 1 '12 at 16:21

While it's running try running the CHECKPOINT command separately. It could be that your system is not getting to perform checkpoints and may need some extra prodding to do so.

  • Surely doing additional checkpoints will just increase the transaction log even further? – Hamish Tonkin Aug 21 '12 at 10:25
  • 2
    A checkpoint is the process which involves getting the information which is in the log file(s) into the data file(s). If the database is in the Simple recovery model, then a checkpoint allows the log to be cleared. – Rob Farley Aug 21 '12 at 22:41

One option is to set the recovery model to BULK LOGGED for the duration of the month end load. FULL recovery mode logs all changes to the transaction log as well as the previous values so rollback can happen so it's possible that more data gets written to the log than the data file.

BULK LOGGED stores the location of the page that was changed and then that page is copied into the transaction log backup. The transaction log itself will be smaller but you may notice an increase in the size of the tlog backups. It's important to be aware that you can't do a point in time restore from a tlog backup that has bulk logged operations.

If your recovery model is already BULK LOGGED or SIMPLE look at the log_reuse_wait_desc for the database to determine what you need to do to clear the log. select name, log_reuse_wait_desc from sys.databases If it's LOG_BACKUP you need to increase the frequency of log backups. If it's something else update the question and we'll look into it.

Not the answer you're looking for? Browse other questions tagged or ask your own question.