Bulk Logged Recovery Model in SQL server

Understanding Bulk Logged Recovery Model

 

I interviewed 2-3 candidates recently and asked them simple question “What is Bulk Logged Recovery Model and How IT WORKS”. I know candidate will be very happy hearing such easy question in the interview. I also think same thing and before I go deep, I ask simple questions so that candidate is very much friendly.

I was surprised after getting answer from all of them. The answer was “Bulk operations never get logged in the transaction log and this way it increases the performance”. I can expect such answer from the person who is fresher or having 2-3 years of experience. But if the person has more than 5 years of experience and answer like this will force me to reject the candidate right away.

This makes me to write something about BULK LOGGED RECOVERY model which can remove some of the confusion about this mode.

 

First of all there is difference between NO LOGGED and MINIMALLY LOGGED. In Bulk Logged Recovery model, bulk operations are minimally logged and not fully logged. This does not mean that bulk operations are never logged in transaction log.

Here are the operations which get minimally logged when you database is in bulk logged recovery model:

·         Bulk import operations (bcp, BULK INSERT, and INSERT... SELECT).

·         SELECT INTO operations

·         WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns

·         Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data

·         CREATE INDEX operations (including indexed views).

·         ALTER INDEX REBUILD or DBCC DBREINDEX operations.

 

Actually in bulk logged recovery model, allocation of bulk operations are stored in transaction log and not complete data page. Since data pages are not getting logged in the transaction log, this recovery model does not take much space in the log. And this is the reason transaction log does not grow when we do costly bulk operations especially rebuilding the indexes. But when we take the transaction log backup, log backup will have complete data pages as well to provide you the ability to restore bulk operations. This is the reason of having bigger size of transaction log backup compare to transaction log file sometimes. Here the diagram which can give clear picture of this concept.

 

 

Data files are the one which basically holds the data pages of the bulk insert operations. This is the reason if a minimally-logged operation has been performed since the last log backup, and one or more data files were damaged and offline because of the disaster, a tail-of-the-log backup cannot be performed and so all user transactions performed since the last log backup will be lost.

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions.

Let me talk something about the advantage and disadvantage of this recovery model in SQL server.

 

Advantage:

Since bulk operations are minimally logged, this increases the performance of the bulk operations. Also it does not allow the log to grow unexpectedly when we do some costly operations like rebuild index, create index etc.

Disadvantage:

If transaction log is damaged, changes since the last backup must be redone. Also if any bulk operations occurred since the most recent log backup, changes since the last backup must be redone.

Remember:

1.    You cannot use point in time restore option if your database is set to bulk logged recovery model.

2.    You have to take the transaction log backups else your log will not be truncated.

3.    If you change recovery model to bulk logged from Full then you do not disturb the log backup chain. But it is always a very good practice to take the log backup before and after making changes of recovery model.

4.    Microsoft recommends using this recovery model when and only when we do bulk operations (Mentioned above). Once the operation(s) is/are completed, switch back to FULL recovery model so that your database can be restored to point in time if disaster happens.

 

Hope this helps you to understand the concept of BULK LOGGED RECOVERY model in SQL server. I feel this recovery model very important for the databases where we do not need point in time recovery and we have so many bulk operations running.