Transaction Log Concepts – Part 2: Full Database Backups and Mirroring/Availability Groups

By | 8th March 2016

In Transaction Log Concepts – Part 1: ACID, Write Ahead Logging, Structure of the Log (VLF’s) and the Checkpoint/Lazywriter/Eagerwriter process I talked about Write Ahead Logging and how the Checkpoint process interacts with the Transaction Log; To recap – the transaction log is an append only log of all changes to the Database, an incremental Log Sequence Number (LSN) is used to track the order of changes and determine when the contents of the log is in sync with the contents of the data files – remember data is written to the log first then to the data files.

In this part we will look at how the Transaction Log behaves when using Full Database Backups and Database Mirroring (or Availability Groups).

Once you understand what a Log Sequence Number (LSN) is and how the various database features interact with the log you’ll have it nailed!

Full Database Backups

Keeping the backup process in it’s simplest terms – the Database backup reads from the beginning to the end of the database exporting the used portion into an external file.

Consider this: you have a highly active database but it’s the same piece of the database (Extents) that are constantly modified, you kick a database backup off at 9pm, the full backup takes precisely 2 hours – how do you keep track of changes to the database while your backup is running i.e. over those 2 hours?

You need to keep track of all changes to the database since the backup has started, sound familiar? Remember – the Transaction Log keeps track of all changes to a Database, so the backup process is now simple – you do a start to end read of the used portion of the Database to an external file and then once complete you append the Transaction Log to the external backup file.

When the backup is started the minimum LSN of the active portion of the transaction log is captured, from therein SQL Server cannot truncate past that log marker regardless of how often you backup the transaction log – you can still and should backup the transaction log and the log since the last transaction log backup LSN will be exported to the external file but the VLF’s in the log will not be marked for reuse – you just end up with more and more active log even though you’ve backed it up.

Example – the concept

Log File actual size = 1GiByte, Transaction log backup has just happened so all bar one of the VLF’s are inactive.

Database is constantly updated and creates 100MiByte of log data per minute, the database backup takes 1 hour, there is a log backup every 10 minutes.

Full Backup starts at LSN 1000.
Log backup 1 - last log backup LSN 1000, Full Backup start LSN 1000, used log is 1GiB, log backup = 1GiB
Log backup 2 - last log backup LSN 2000, Full Backup start LSN 1000, used log is 2GiB, log backup = 1GiB
Log backup 3 - last log backup LSN 3000, Full Backup start LSN 1000, used log is 3GiB, log backup = 1GiB
Log backup 4 - last log backup LSN 4000, Full Backup start LSN 1000, used log is 4GiB, log backup = 1GiB
Log backup 5 - last log backup LSN 5000, Full Backup start LSN 1000, used log is 5GiB, log backup = 1GiB
Log backup 6 - last log backup LSN 6000, Full Backup start LSN 1000, used log is 6GiB, log backup = 1GiB
Full backup completes, appends 6GiB of log to the external file.
Log backup 7 - last log backup LSN 7000, Full Backup start LSN 1000, used log is 1GiB - rest has been purged because of completion of the Database Backup.

As you can see only the used portion of the log since the last log backup is backed up so you don’t repeat even though the log is growing.

Example – seen through SQL Server

The output below was created using a 23GiB copy of a TPC-H database build, you need to put the database into Full recovery first and take a Full backup first prior to playing, the log is still backed up at the end of a Full database backup when the database is in Simple recovery, it’s just easier to see the effect when in Full recovery.

Connection A:

set nocount on;

declare @c_custkey bigint = ( select top 1 c_custkey from customer )

while 1 = 1
begin
	update customer
		set c_comment = cast( newid() as varchar(118) )
	where c_custkey = @c_custkey	

end

Connection B:

set nocount on;

while 1 = 1
begin
	waitfor delay '00:00:10'

	dbcc sqlperf(logspace)

	checkpoint

	backup log TPCH to disk = 'D:\TEMP\TPCH.trn' with init

	dbcc sqlperf(logspace)

	print ''
	print ''

end

Connection C:

backup database TPCH to disk = 'D:\TEMP\TPCH.bak' with init

Results:

In the results below you can clearly see that a) the Used portion of the log keeps growing despite successive transaction log backups until the last log backup which has been done after the Full backup has completed, secondly b) you can see that each log backup is approximately the same actual size and the complete log isn’t backed up over and over again.

If we tot up the number of log pages backed up (54,599 = 436MiB) it is in the same ball park if we compare the full backup size with the activity below to one with no activity (49,312 pages = 394MiB).

Log Size (MB) Log Space Used (%) - Before LOG backup
------------- ------------------
361.0547      31.07892

 Processed 13295 pages for database 'TPCH', file 'tpch_log' on file 1.
 BACKUP LOG successfully processed 13295 pages in 0.501 seconds (207.306 MB/sec).

Log Size (MB) Log Space Used (%) - After LOG backup
------------- ------------------
361.0547      29.1829
Log Size (MB) Log Space Used (%) - Before LOG backup
------------- ------------------
361.0547      50.86552

 Processed 10499 pages for database 'TPCH', file 'tpch_log' on file 1.
 BACKUP LOG successfully processed 10499 pages in 0.230 seconds (356.600 MB/sec).

Log Size (MB) Log Space Used (%) - After LOG backup
------------- ------------------
361.0547      51.31126
Log Size (MB) Log Space Used (%) - Before LOG backup
------------- ------------------
361.0547      72.37045

 Processed 9991 pages for database 'TPCH', file 'tpch_log' on file 1.
 BACKUP LOG successfully processed 9991 pages in 0.267 seconds (292.319 MB/sec).

Log Size (MB) Log Space Used (%) - After LOG backup
------------- ------------------
361.0547      72.88692
Log Size (MB) Log Space Used (%) - Before LOG backup
------------- ------------------
361.0547      94.66149

 Processed 10331 pages for database 'TPCH', file 'tpch_log' on file 1.
 BACKUP LOG successfully processed 10331 pages in 0.278 seconds (290.320 MB/sec).

Log Size (MB) Log Space Used (%) - After LOG backup
------------- ------------------
361.0547      95.2349
Log Size (MB) Log Space Used (%) - Before LOG backup
------------- ------------------
431.0547      98.30132

 Processed 10483 pages for database 'TPCH', file 'tpch_log' on file 1.
 BACKUP LOG successfully processed 10483 pages in 0.199 seconds (411.537 MB/sec).

Log Size (MB) Log Space Used (%) - After LOG backup
------------- ------------------
431.0547      1.471115

Warning over Open Transactions

If you have a long lasting transaction, perhaps a process has accidentally started a transaction, then the log will not shrink down – you can back up the log and the behaviour is as above in terms of only backing up new stuff however the VLF’s will not free up so your log will keep growing and growing until the open transaction is committed or rolled back. You can use DBCC SQLPERF(LOGSPACE) to see the size of the log, you can use DBCC OPENTRAN( … ) to see the open transactions in a database.

Database Mirroring / Availability Groups

I’ll simply start by saying – Ditto the above! It uses the same technique around tracking the LSN but instead of a fixed point i.e. the start of a backup, the minimum LSN follows the state of mirroring/availability group replication.

Both Mirroring and Availability Groups use the Transaction Log, the log is read sequentially and the information is compressed and streamed across to the mirror or secondary replica’s.

Ever had a AG or Mirror disconnect? Notice that your transaction log just keeps growing and growing….

The LSN is not moved forward until the transaction is hardened off on the mirror/secondary replica, that means not just reaching the transaction log on the other machine but actually it being written to the data files (recovery) albeit the databases are permanently in a form of recovery mode.

Recommendations

When taking Full Backups you need to minimise the amount of transaction log activity – that means scheduling your index maintenance outside that window, schedule batch jobs accordingly etc.

When using mirroring or availability groups then size your logs with possible failure of the stream in mind, make sure your log has space to grow if you don’t want to pre-size your transaction log. If you size your log based on everything working fine then you are setting yourself up for failure. How big will your log grow if the network to your remote site is down for an hour?

Make sure transactions are short lived!

Leave a Reply

Your email address will not be published. Required fields are marked *