Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

migrated from stackoverflow.com Aug 31 '12 at 13:06

2 Answers

up vote 0 down vote accepted

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:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

and:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

so properly sizing it up front will be best.

share|improve this answer
up vote 1 down vote

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)

share|improve this answer
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 – user1181412 Aug 31 '12 at 13:42
Sorry, no idea about SSIS - is that what you are using? – MiMo Aug 31 '12 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 '12 at 16:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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