Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system?

Question

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?

Answer 1

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.

Check these site: http://www.sql.recoverytoolbox.com/ & download demo Recovery Toolbox for SQL Server.

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.

Answer 2

Bulk-Logged only helps if you are importing data from a text source such as csv file. To tell the engine no to maintain transaction since you are importing from a static source it is a repeatable process if it fails for any reason so that the sever don't have to care logging and it will be a faster process.

I had the same exact problem in the past and the only time that happened to me was I had an ETL that involves a lot of update and delete processes and I was working with around 7 million records that need to be transferred per day. The only way you could avoid that is by taking fewer batches and committing changes instead of working on the entire data set you have. Since you didn't specify what ETL tool and what destination database you are working on I can't give any specific answers.

Answer 3

problematic factor. query on google. just that could actually help!