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:

bulk_log_01

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)

bulk_log_02

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
GO
EXEC sp_MSforeachtable "DBCC DBREINDEX ('?')"
GO

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

bulk_log_03

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'
GO

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

bulk_log_04

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]
GO

ALTER DATABASE [AdventureWorks2008]
SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO

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

sp_helpdb 'AdventureWorks2008'

bulk_log_05

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)

bulk_log_06

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
GO
EXEC sp_MSforeachtable "DBCC DBREINDEX ('?')"
GO

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

bulk_log_07

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'
GO

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?

bulk_log_08

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: http://technet.microsoft.com/en-US/library/ms190692(v=sql.100).aspx

Another SQL Server myth has been

bulk_log_09

Write a Reply or Comment

Your email address will not be published.