How Bulk-logged recovery means nothing to your transaction log backup

A question I hear a lot from fellow DBA’s is how they can minimize the impact of index maintenance in a log shipped environment for large databases.

The problem with running index maintenance like rebuilds in VLDB’s is that they generate very large transaction log backups. If the SQL Server environment you manage uses log shipping to duplicate production data to another database you will, sooner or later, run into synchronization issues because the transaction log backups get very large because of your index maintenance and take a longer time to restore on your target server.

One option I hear a lot about is switching your database recovery model to Bulk-logged before you start your index maintenance and switching it back when you’re done. Because Bulk-logged recovery minimally logs bulk operations, like an index rebuild, our transaction log backup should get a lot smaller….but….will it?

Let’s do some testing to find out if there is any truth about the above statement!

For this test I’m using a Microsoft SQL Server 2008 with the AdventureWorks2008 database.

Step 1)
Let’s find out the recovery model of our AdventureWorks database:


sp_helpdb 'AdventureWorks2008'

As you can see we are running in Full recovery mode.

Step 2)
Let’s check our current transaction log size and usage:

DBCC sqlperf(logspace)


As you can see it’s 241MB and about 2% of that is used.

Step 3)
Time to generate some logging! We will be reindexing all the indexes in the AdventureWorks database.

USE AdventureWorks2008
EXEC sp_MSforeachtable "DBCC DBREINDEX ('?')"

Now let’s take a look at the size and usage of the transaction log again:


That reindex sure had some impact, the size is still the same but we’re using 58% of the transaction log now instead of the 2% before we ran our reindex.

Step 4)
Let’s find out what this means for a transaction log backup in full recovery, we’ll run a backup of the transaction log:

BACKUP LOG [AdventureWorks2008]
TO  DISK = N'E:\Backup\adventureworks_test1.trn'

Let go check out the filesize of that transaction log backup:


It’s around 140MB.

So now that we’ve seen what happens after an index rebuild in full recovery model let’s try and do the same on Bulk-logged recovery and find out it’s effect on the transaction log backup!

Step 5)
Let’s switch our AdventureWorks database to Bulk-logged recovery:

USE [master]

ALTER DATABASE [AdventureWorks2008]

Step 6)
Let’s check our recovery model again:

sp_helpdb 'AdventureWorks2008'


Great, it’s in Bulk-logged mode, so far so good!

Step 7)
Let’s check our transaction log size and usage now that we’re in Bulk-logged recovery:

DBCC sqlperf(logspace)


It’s still around 241MB and using 1% of space this time.

Step 8)
Let’s do the same index rebuild action we did in full recovery mode:

USE AdventureWorks2008
EXEC sp_MSforeachtable "DBCC DBREINDEX ('?')"

Let’s check the transaction log size and usage and see what it looks like in Bulk-logged recovery:


Wow! That’s amazing, only 2,9% used of the 241MB instead of the 58% we had in full recovery model!

So far the Bulk-logged recovery has kept its end of the bargain, let’s see what happens when we take a transaction log backup!

Step 9)
Let’s take that transaction log backup:

BACKUP LOG [AdventureWorks2008]
TO  DISK = N'E:\Backup\adventureworks_test2.trn'

Now let’s see how big that transaction log backup is, seeing it’s usage is only 2.9% it should be really small….or should it?


Look at that! It didn’t get any smaller at all…that’s not what we agreed on Bulk-logged recovery mode!

So why did this happen? It’s actually very simple.

As we noticed in the screenshot in step 8 we see that the index rebuild action was indeed minimally logged. However when we take a transaction log backup with the database in Bulk-logged recovery we do not only backup the transaction log but also the data pages that have changed since the last transaction log backup.

Bulk-logged recovery relies on Bulk-changes bitmap page that contains a bit for every extent, for each extent that has been updated by a bulk-logged operation since the last transaction log backup that bit will be set to 1 so during a transaction log backup SQL Server knows what extents it has to include in the backup.

So instead of being smaller, the transaction log backup size stays more or less the same.

More info about this bulk-logged behavior can be found on Technet:

Another SQL Server myth has been


Write a Reply or Comment

Your email address will not be published.