Take the 2-minute tour ×
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.

We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set this database into Bulk-Logged Recovery Model? We are taking full backups on daily basis. So is there some operations which are not logged in Bulk-Logged Recovery Model compared to Simple Recovery Model?

share|improve this question
2  
Your current recovery model has the least amount of logging. BULK-LOGGED offers more logging as it keeps track of modifed extents performed by bulk operations. –  jl01 Feb 17 '12 at 14:40
add comment

3 Answers 3

up vote 1 down vote accepted

If you log grows on ETL in simple mode it means your inserts are not minimally logged. The requirements for minimal logging are described in Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging in Bulk Import:

Minimal logging requires that the target [of a INSERT BULK operation] table meets the following conditions:

  • The table is not being replicated.
  • Table locking is specified (using TABLOCK).

The Data Loading Performance Guide also mentions the TF 610 effects and how it affects minimal logging.

share|improve this answer
add comment

If at all, your log size will grow when changing from simple recovery to bulk logged. You may be having log running transactions, or uncommitted/runaway transactions that are keeping a hold of your log file, causing it to grow.

share|improve this answer
add comment

Bulk Logged will not help you in this situation. Your transaction log is growing because you are performing large transactions. In simple mode, the log is truncated after a checkpoint.

Depending on how you are loading your data, you may need to break it up into smaller chunks. Make sure you are not loading all the data in one large transaction.

share|improve this answer
add comment

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.