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?
-
Adjust your load process to smaller batches. This might help the transaction log keep up without having to grow. You might even consider throwing in a manual checkpoint.– user507Nov 7, 2014 at 1:47
Add a comment
|
2 Answers
Logging occurs in all recovery models. The log is retained in bulk logged and full recovery models until the log is backed up. Bulk logged only minimally logs certain actions e.g. an index rebuild, meaning you can't do point in time restores when using bulk logged. As you are using simple recovery model, changing to bulk logged will be of no benefit to reducing log size. It would make your setup more complex actually as you would have to start taking regular log backups.
If you have a large transaction in simple recovery model, this transaction will still be logged and cause your log file to grow. This is what allows you to rollback the transaction in any recovery model. If you left that transaction open (don't commit or rollback) your log would grow and grow. Once committed (or rolled back) that logging information no longer has to be retained in simple recovery model and SQL server will reuse the log in a cyclic nature.
Your solution would be to minimise the size of your ETL insert transactions. You could perform smaller inserts in batches and commit those (if that is appropriate for data consistency). You could also look at transaction handling in your ETL tool. SSIS for example allows all inserts in a package to be part of the same transaction, you could change this behaviour so each transformation has it's own transaction.
The bulk-logged recovery model is similar to the full recovery model, so it basically doesn't truncate the log on checkpoint as the simple recovery model does.
If you want to keep the transaction log small, simple recovery is your best option. If the log is growing is because some big transaction is being submitted to the instance and there really is nothing you can do about it: it's expected and the log space taken is what the instance needs to execute the transaction.
