The Curious Case of… eager writing and minimally-logged operations

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

I had an email discussion with someone last week who was wondering about log backups and minimally-logged operations. Specifically they were confused as to how the log backup works if no checkpoint has occurred since the operation completed. Surely the data pages are still dirty in memory?

Not so.

Think about what a log backup has to do: it must contain enough information to be able to fully replay a committed operation when the backup is restored. This then means that because a minimally-logged operation only logs page and extent allocations, and not the contents of the pages themselves, a log backup after a minimally-logged operation must also back up those pages that were modified by that operation. Actually, it backs up entire extents, not the individual pages. This works as follows:

  • For each extent that is changed by a minimally-logged operation, a bit is set in a bitmap page (called either the minimally-logged bitmap or bulk-change map or BCM), and there is one of these bitmap pages that tracks such extents for every 4GB of each data file.
  • The next log backup after the minimally-logged operation completes backs up all the log and then scans the minimally-logged bitmaps and reads any extents marked as changed.
  • The minimally-logged bitmaps are then reset.

Aside: You might think that there’s a race condition between a concurrent log backup and a minimally-logged operation, but that’s not possible as those two operations are mutually exclusive (i.e. a minimally-logged operation cannot start while a log backup is occurring, and vice versa). This is done using a database-level subresource lock which will show up in the output from sys.dm_tran_locks as ‘BULKOP_BACKUP_LOG’, in a similar manner to the BULK_OPERATION hobt subresource lock I blogged about here.

Anyway, back to the original question. If no checkpoint occurs after the minimally-logged operation and before the next log backup, something must be guaranteeing that the pages changed by the operation are already on disk. This is done using what’s called eager writing. Before a minimally-logged operation can commit, all pages changed by it must be durable in the data files on disk. Eager writing does this by writing the pages to disk as the minimally-logged operation fills them, not waiting for a checkpoint operation. Thus when the minimally-logged operation commits, it is fully durable on disk in the data files and the log.

And it’s not just for the correctness of log backups that this occurs; it’s also necessary to guarantee that crash recovery works correctly too.

Bottom line: there are three ways that a data file page can be written to disk after it’s been changed (a.k.a ‘dirtied’): a checkpoint, via the lazy writer, or by eager writing.

2 thoughts on “The Curious Case of… eager writing and minimally-logged operations

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.