Does using Bulk Logged recovery model for Bulk Operations will reduce the size of Transaction log backups..?

I've been working on SQL Server from more than 5 years and I keep coming across various SQL Server misconceptions (or say a DBA Myth). In this specific post I’ll demystify a common DBA myth around log-shipping: "In a log shipping setup, to have smaller size T-log log backups being shipped to secondary, switch to "BULK_LOGGED" recovery model before performing any bulk operation"

SCENERIO:

- You have configured Log Shipping for a high OLTP database
- On Primary Server, to optimize the database performance, a daily RE-INDEXING job is scheduled
- Everyday, after the RE-INDEXING job is executed, T-log grows huge and size consecutive T-Log backup is large. It takes lot of time (Of course the Network Resources!!) to ship T-log backups to secondary server
- So, to resolve this issue, DBA came with below plan:

1. Change the recovery model of database to BULK_LOGGED
2. Perform RE-INDEXING (which qualifies to be minimally logged operation)
3. Change the recovery model back to FULL

- you believes, following these steps will reduce the size of T-log and thereby minimize network resource usage. This is "THE MYTH"


EXPLANATION:

- Backups under BULK_LOGGED recovery model, will include log records and the data pages changed by bulk operations, which will actually make a log backup very large.

Here’s an extract from the Books Online (http://technet.microsoft.com/en-us/library/ms190692(SQL.90).aspx). Note the parts marked in Bold Red below:

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. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large. Additionally, backing up the log requires access to the data files that contain the bulk-logged transactions. If any affected database file is inaccessible, the transaction log cannot be backed up and all operations committed in that log are lost.

LET’S BREAK THIS MYTH – With some testing!!

For purpose of demonstration, I will be using AdventureWorks sample database.

--1. Verify the recovery model of the AdventureWorks database

sp_helpdb 'AdventureWorks' 
Go 

AdventureWorks Recovery=FULL

--2. Verify current T-log usage

dbcc sqlperf(logspace) 

Database Name Log Size (MB) Log Space Used (%) Status
------------------ ------------- ------------------ -----------
AdventureWorks 257.9922 1.920442 0

--3. Perform re-indexing on AdventureWorks database

Use AdventureWorks 
Go 
exec sp_MSforeachtable "DBCC DBREINDEX ('?')" 
Go 

--4. Verify T-log usage after Re-Indexing

dbcc sqlperf(logspace) 

Database Name Log Size (MB) Log Space Used (%) Status
----------------- ------------- ------------------ -----------
AdventureWorks 257.9922 41.09579 0

--5. Backup T-Log in FULL recovery model

BACKUP LOG [AdventureWorks] TO  DISK = N'C:\Backups\LOG_BCKUP_IN_FULL_RECOV.trn' 
GO 

--6. Verify the size of T-log Backup in FULL recovery


Size = 102.9 MB

--8. Change the Recovery Model to BULK_LOGGED

USE [master] 
GO 
ALTER DATABASE [AdventureWorks] SET RECOVERY BULK_LOGGED WITH NO_WAIT 
GO 

--9. Verify the recovery model of the AdventureWorks database

sp_helpdb 'AdventureWorks' 
Go 

AdventureWorks Recovery=BULK_LOGGED

--10. Verify current T-log usage

dbcc sqlperf(logspace) 

Database Name Log Size (MB) Log Space Used (%) Status
----------------- ------------- ------------------ -----------
AdventureWorks 257.9922 2.983337 0

--11. Perform re-indexing on AdventureWorks database

Use AdventureWorks 
Go 
exec sp_MSforeachtable "DBCC DBREINDEX ('?')" 
Go 

--12. Verify T-log usage after Re-Indexing

dbcc sqlperf(logspace) 

Database Name Log Size (MB) Log Space Used (%) Status
----------------- ------------- ------------------ -----------
AdventureWorks 257.9922 4.773189 0

--13. Backup T-Log in BULK_LOGGED recovery model

BACKUP LOG [AdventureWorks] TO  DISK = N'C:\Backups\ADW_REINDX_IN_BULK.trn' 
GO 

--14. Verify the size of T-log Backup in BULK_LOGGED


Size = 109.7 MB

CONCLUSION: Myth Busted!!

- Bulk operations (like Re-indexing) under BULK_LOGGED recovery model are minimally logged, which will reduce T-log file growth. However this will NOT reduce the size of consecutive T-log backups[1]. So this approach is no good for scenario in subject.

NEXT STEPS:
- Go and test this yourself :)

REFERENCE READ:    

Backup Under the Bulk-Logged Recovery Model : http://technet.microsoft.com/en-us/library/ms190692(SQL.90).aspx

Overview of the Recovery Models : http://msdn.microsoft.com/en-us/library/ms189275(SQL.90).aspx

[1] A T-log Backup under Bulk Logged recovery model will contain both ‘modified data pages’ and ‘information from T-log files’. As such, there are 2 additional factors that can influence number of data pages being backed up and consequently the overall size of T-log backup in bulk logged recovery model:
Page Fullness – Is derived from Average Bytes free per page and Average Page Density(FULL). The higher the numbers are, the less full the pages. So basis page fullness, we can consume more or less data pages for transactions.
Index Fill Factor – Fill factor is the % of space in an Index Page that is used when an index is created or rebuild. The purpose is to leave behind some free space in the index page to accommodate future insert/update to avoid page splits.