What is the standard process when initially populating a database with 5GB+ to avoid a large transaction log? From my understanding the log file would jump to a large size after the initial load and not decrease in disk space from that point on. Regular transaction log backups would free up space within the file, but the file would remain large.

Is it advisable then to set the target database to simple backup, load the data, use CHECKPOINT to truncate log, and then set the database to full backup mode?

Additionally, I know there is a shrink file operation that can be performed on the log, but some say never to use it, others seem to recommend it as a solution. Any thoughts?

2 Answers 2


You could initial load it in simple model. (as long as the load by itself isn't a multi step process where you want to be able to do a point in time restore).

Now after the load, it could be that your logfile has grown quite a bit if you had a long running transaction regardless of being in simple model. You can then shrink the log file.

Then you need to size the log file. A good starting point would be to check your largest table. If your in full recovery model, you'll need at least as much transaction log file space as your largest table. Since when you do regular index mantainance and you do a rebuild, the transaction log generated by the rebuild is about as much as your table. So sizing for your bigest table is a good start.

However, you'll see over time if your transaction record rate compared to your log backup frequency is making hte log file grow or not. You can either ajust the size of your log file or the frequency of your log backups.

Sizing up front is important. Having the file auto grow is a huge performance penalty, since the log file needs to be zero initialised. Remember, it always autogrows at the moment you are doing transactions, the transaction log writing is synchronous, so waiting for the autogrowth to finish is a direct wait on all pending transactions.

Then the auto grow option is also bad for the internal layout of your log file. see:




so properly sizing it up front will be best.


Use bulk operations - the bcp command-line utility or BULK INSERT commands, they can generate much smaller transaction logs and they are way way faster. See this for details about the transaction log generation.

(I am answering for Microsoft SQL Server, but equivalent utilities and command are available for all databases)

  • From what I've read SSIS uses BULK INSERT as its underlying operations? Either way it appears using the bulk operations aren't sufficient and the database needs to be set to bulk logged recovery mode prior to the operation
    – JohnB
    Aug 31, 2012 at 13:42
  • Sorry, no idea about SSIS - is that what you are using?
    – MiMo
    Aug 31, 2012 at 15:56
  • ...you're right, using bulk operations is not enough by itself - the table should be empty or without indexes, and there is still some logging.
    – MiMo
    Aug 31, 2012 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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