May 4, 2016 at 2:19 pm
Hi,
I am doing bulk operations i.e.Copying data from x table having 200 million rows with 100 columns to another table. I am thinking to change the recovery model from Full to Bulk instead of simple, hoping the copy of this table will run approx 9 - 10 hrs. The question i have is does changing the recovery model from full to bulk will still keep my transaction log back happening and do you think the size of the log backup will be smaller vs full recovery type?
Thanks
May 5, 2016 at 1:04 am
Have a read through this article - towards the end about BULK LOGGED.
To summarise (from the article by Paul Randal):
https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx#id0060077
The BULK_LOGGED recovery model has the same transaction log truncation semantics as the FULL recovery model but allows some operations to be partially logged, which is called being minimally logged.....only the allocation changes are logged, which drastically reduces the number of log records thus reducing the potential for transaction log growth.
May 5, 2016 at 3:16 am
The log backup will be the same size, or even larger, as any extents affected by the minimally logged operation are copied into the transaction log backup. The advantage is that less space in the log itself will be needed for the operation.
The downside is that, if you need to restore the DB, you can only restore it to the times of the log backups, not any point in time within the log backup interval if there was a minimally logged operation within that log backup interval.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply