How does recovery work when server is shutdown before a checkpoint?

Question

So for any query that makes changes to data:

1. SQL Server pulls the page from disk into memory if it doesn't already exist in memory.
2. Lock the page/row and make the data changes.
3. Writes the changed to the log in memory.
4. Release the page/row lock.
5. Send acknowledgement to user

Then, whenever there is a checkpoint:

1. Write the log to disk
2. Write dirty page to disk

Suppose the server gets shutdown due to power failure just before a checkpoint. Then is the data from memory that wasn't burnt to disk lost? Or does SQL Server write the log to disk before sending acknowledgement to user?

Answer 1

In full durability, the default, the commit completes only when the data is physically written to the log file - it's only data pages that are later written to disk on checkpoint by the lazy writer.

There's a per-database setting called delayed durability that can override and allow the transaction to be committed but only written to the log file when the buffer is full, offering faster transactions but the risk of losing (as yet) unwritten committed transactions.

More info

Answer 2

You're right that there's a finely tuned choreography between disk, the buffer pool and the write-ahead log. Each part has to work with the others to guarantee consistent recovery after an un-planned shutdown. There are many choices to make and the sequence in which actions occur is critical to the success of the whole.

Most DBMS follow a protocol called ARIES published by IBM in 1992. Certainly SQL Server does. There's a readable overview here. This protocol's steps differ a little from the question's bullet points.

Somewhere before acknowledgement is sent to the user in step 5 the transaction has to be committed. This can be explicit (COMMIT statement) or implied (at the end of a statement without an explicit BEGIN TRANSACTION). At this point the write-ahead log records are forced to disk.

Log records are written to disk when the log buffers are flushed. This happens whenever a transaction commits or the log buffers become full.

As well as "before" and "after" copies of the changed data the log also receives "transaction start" and "transaction commit" records. Each log record has a Log Sequence Number (LSN). As data changes are written to the log the corresponding LSN is written to the data page in the buffer pool. Only the most recent LSN is required in each page. (You can read this value.)

During a checkpoint not only are dirty pages flushed, the fact a checkpoint happened is also written to the log and flushed. From a previous reference: "SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written."

To answer the actual question - what if there's a failure before the dirty page can be checkpointed? On restart, the system reads the log. It discovers that there was a transaction, that wrote to our page, and committed. It can read the required page back into the buffer pool. Because the just-read page contains the LSN of the most recent change made to it, the system knows what log records to re-apply -- everything after the page's LSN. Those records are re-applied and the transaction commited. Now the buffer pool contains our page as it was after our data change was made but before the outage.

In a similar way the protocol can deal with pages that were checkpointed while transactions that dirtied them were still open when the system failed, and with transactions that were rolled back before a checkpoint hardened the affected pages to disk.

There are a lot of details I've skimmed over and many corner cases that the full protocol handles (a second power loss while recovering from the first power loss?).

SQL Server 2019 introduced Accelerated Database Recovery which improves on ARIES to reduce the time required to recover from failure.