November 6, 2014 at 11:11 am
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?
November 6, 2014 at 11:42 am
Have you tried to understand the transaction log, how it works, and the differences between Full, Bulk-logged, and simple recovery models?
Because if you had, you may not be asking this question. The answer is, it depends, but I seriously doubt that it will make a difference
This is the MSDN article on the transaction log. It describes the differences between the various recovery models, what each of these log, and what each of these minimally log.
http://msdn.microsoft.com/en-us/library/ms190925.aspx
On this site, there is a very good series of articles called the Stairway series. Tony Davis and Gail Shaw wrote a series on managing the transaction log.
http://www.sqlservercentral.com/stairway/73776/
There is one misconception in your post:
bugarl (11/6/2014)
So is there some operations which are not logged in Bulk-Logged Recovery Model compared to Simple Recovery Model?
EVERYTHING is logged, regardless of the recovery model. In bulk and simple, there are operations that are minimally logged.
The big difference is that the simple recovery model truncates the log when a checkpoint is issued.
Taking a full backup every night will do nothing about the size of the database.
Is the transaction log actually big, or is there a significant amount of free space?
It appears that what you are seeing is normal behavior. You probably NEED a log of this size.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 7, 2014 at 2:22 am
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/[/url] & 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.
November 7, 2014 at 3:51 am
In SIMPLE recovery model, SQL Server still logs all transactions, but will minimally log certain bulk operations; in effect, the level of logging is the same as that applied in BULK_LOGGED model.
So that answers your question if there is a point in changing recovery model.
I would try to split your operations into smaller transactions but only if the log is really growing to unreasonable sizes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply