Special people use Bulk logged recovery mode.

Who uses Bulk Logged?

Special people use Bulk logged recovery mode or so I have inferred from word on the street and Microsoft's BOL explanation. Bulk Logged is now referred to as a "special-purpose" recovery mode for versions 2008R2 and above. This maybe the one simple explanation that could have demystified Bulk logged years ago for DBA's who choose to over look it because they didn't know what it is. Now you know, it's for your "special purpose". What special purpose? Steve Martin explains in the movie the Jerk "Well, when I was a kid my mom told me... there goes my special purpose! And someday I'd find out what my special purpose was!"

Today's the day! 

Your Special Purpose

Bulk logging recovery mode will log less transactions (minimal logging) in the log file than full or simple recovery modes. While a database is in Bulk logged recovery mode, the MSSQL engine will handle Bulk operations differently than the other two recover models. Bulk Logged exists because big data loads take up space and use log files in way that the DBA may not want it to do. For instance, You may have a finite space for your log or you may have grown your log in a deliberate fashion for best performance. Whatever the case is, you don't want to take additional space or fragmented the growth on your log file. In special cases when a bulk insert operation is detected, SSQL refrains from logging individual transactions within that operation to the log file. The operation is then carried out by the engine which places the data directly into the database and marks only the event metadata in the log file. I will get into the details of the why and how later so let's get back to your "special purpose". Special purpose indicates that Bulk logged is not a long term recovery model although you may find a case for it in your environment specially if that includes a data warehouse. What Microsoft is trying to say is that bulk logged is meant to be turned on in special situations and then turned off when the special situation is complete. If you are loading 30GB of data into your database and you have a log file with 10GB of space for growth, you can change your recovery model to Bulk Logged before the operation and save the log file. In addition, it can be used to minimize I/O on the log file in cases were the log file needs to be readily available while a bulk operation is in mode. In any case, you know you have a special situation and it is killing your log file, so you have the option of switching your recovery model to Bulk Logged. 

There are some caveats to Bulk Logged, of course, but you can choose if they are acceptable to your environment before making the decision to use Bulk Logged, I will detail the risk later.

Why Bulk Logged

Bulk logging recovery mode is a way to get around the design element of MSSQL that provides recoverability in every case, logging transactional I/O to the log file. Someone at Microsoft must have realized that the recoverability of MSSQL may at some times be a burden for bulk operations but they did not call it out for what it truly is, however they have recently upgraded it to a "special purpose" recovery mode. I may get some push back from the community by spelling out this way but that is truly what is going on, we are circumventing the "log everything" function of MSSQL. Let's get on to looking at those special purposes and spell them out so there is less confusion than there has been over the past decade. These are the bulk operations that will be minimally logged in your log file if the recovery mode is set to Bulk Logged:

Bulk Import Operations:

1) Operations carried out by the BCP utility. http://msdn.microsoft.com/en-us/library/ms162802(v=sql.105).aspx

  2) "bulk insert" command. http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx

  3)" insert" command. http://msdn.microsoft.com/en-us/library/ms174335(v=sql.105).aspx

  4) "select into" clause http://msdn.microsoft.com/en-us/library/ms188029(v=sql.105).aspx

  5) Index rebuilds and dbcc dbreindex or alter index rebuild. (see below)

 There are some cases where bulk transactions may still be logged under Bulk Logged, most notably in cases with transaction replication working against the table. You can get details here: http://msdn.microsoft.com/en-us/library/ms190422(v=sql.105).aspx

How Bulk Logged works

When a bulk operation is detected and it meets all the criteria for minimal logging, the MSSQL engine then marks the log file that a bulk operation has begun. During the bulk operation, data is written to tables (pages) but not to the transaction log. Instead the MSSQL engine relies on a page called the "bulk changes bitmap page" this page contains a bit for each page that was changed during the bulk operation. When a transaction log backup occurs after the bulk operation, the MSSQL engine pulls each page indicated on the bitmap page as having been effected by the bulk operation into the transaction log backup file. So to an extent, pun intended, moving the data that would have hogged space in your transaction log to the transaction backup file. MSSQL then uses these pages in your backup file during a transaction log restore process to re-apply the bulk operation. Well isn't that convenient?

Bulk Logged Notes

Restores:

The thing to remember is that with Bulk Logged, some transactions are not fully logged so you loose some ability to do a point in time restore. In addition, because the trans log backup stores pages, you must restore the entire trans log backup file if it contains a bulk operation. This may not be good if your RPO is one hour and the bulk operation or log backup spans greater than one hour so do your homework before presenting bulk log as an option. 

Disk Space:

Keep in mind that the space you have allocated for backups will need to accommodate the addition data in your trans log backups, well kind of, you may have been storing these transactions in full mode anyway.

Read only:

If you are using a database that is normally in read only and after your bulk operation it is put back into read only, each trans log backup will continue to pull in the changed pages. This is because the backup operation cannot set the bit that indicates that the page has been altered. Truth be told, if you have a read only database, why are you doing trans log backups? I thought I would mention it anyway.

Replication:

With transactional replication, select into and bulk insert are fully logged because they need to be moved to the subscriber and there is no why around it. 

Writetext and Updatetext

Statements that alter existing text, ntext and image data are fully logged however new data of the same type is not. This also goes for the WRITE clause in update statements.

Indexes:

 Using bulked log during index rebuilds and dbcc dbreindex or alter index rebuild will minimize logging operations in your log file. This occurs whether it is an online or offline operation.