Bulk-logged recovery model in details

We use bulk-logged recovery model when we want SQL Server to minimally log bulk transaction. Let us take a deep drive to understand this technology and it’s implementation in real life.

Facts around bulk-logged recovery model:

1. Bulk-logged recovery model minimally logs the bulk transactions.
2. Bulk-logged recovery model does not break the transaction log backup sequence. That means your capability to restore a database from transactional log backup is not impacted if you switch a database from full to bulk-logged recovery model and vice versa.

How Bulk-logged recovery model works:

Once you set a database into Bulk-logged recovery model, SQL Server knows that you do not want to record entire bulk transaction in the transaction log file of the database. Hence only the allocations of pages are recorded in transactional log file and actual data pages from bulk transactions are not recorded. (This technology is called minimally logged transaction as it reduces the amount of data SQL Server has to write in transactional log to a great instance.)

How bulk-logged recovery model impact transactional log backup technology:

SQL Server backup engine backs up the physical data pages changed from bulk-logged transaction within the transaction log backup file. These data pages are referred from the transaction log file where only the allocations of pages were recorded from bulk transactions. Hence there is no reduction in the size of transaction log backup file if you use bulked-log recovery model.
Also it is worthy to note that transactional log backup file contains data pages in this case.

When we can use bulked-logged recovery model:

Major benefit of using bulked-logged recovery model is minimally logged transaction as this technology reduces I/O load of server to great extent. This provides opportunity for performance increase for bulk transactions on database/s.
 

It is common practice to put a database in bulk-logged recovery model when you want to perform bulk transactions on this and you believe such transaction will create enough I/O load to slow down performance. One typical example to use this technology in corporations is to put databases in to bulked-log mode while regular maintenance tasks (like rebuild of index) executes on it.