What is the impact of power failure on recovery assuming checkpoint has not occurred?

Question

When an INSERT query is fired, SQL server records this in its log and sends the confirmation to user that query is complete. Meanwhile it also updates the data page. Both of these (log and data page) reside in the memory.

Irrespective of the recovery model (simple, bulk or full), whenever a checkpoint occurs, SQL server will flush the log and dirty pages from memory into the disk.

Question: Suppose there is a power failure after sending confirmation to user and prior to a checkpoint, then, since the log from memory is not yet written to disk, will this INSERT operation be lost, even though the user had got a confirmation? Does this violate the Durable property of ACID?

Answer 1

The default behaviour in SQL Server is fully durable transaction commits. This means that transactions are hardened to disk before transaction is acknowledged as committed. As per the docs:

SQL Server transaction commits can be either fully durable, the SQL Server default, or delayed durable (also known as lazy commit).

Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk.

--

Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place.

Durability can be set on database level, explicitly on transaction statement and on natively compiled stored procedures. See the linked resource for details.

As @ik_zelf noted, there is a caveat on durability. For storage that has write-cache enabled, one will get an acknowledgement of successful write when storage controller has stored the write operation in its cache. If there is a power failure, the write might be lost. That's why there are UPS systems and battery-backed cache modules for disk controllers.

Of course, if the log file is stored on a RAM drive, it's going to be a whole other can of worms.

Answer 2

Q: Will this INSERT operation be lost in a power failure?

For the main question - as I understand it - see the excellent article by Itzik Ben-Gan: Understanding log buffer flushes, where among other detailed explanation it states:

The way SQL Server enforces transaction durability, in part, is by ensuring that all of the transaction’s changes are written to the database’s transaction log on disk before returning control to the caller. In a case of a power failure after a transaction’s commit was acknowledged, you know that all those changes were at least written to the on-disk transaction log. ...

Additional questions/clarifications:

Q: Then what does it mean by log written to buffer first and flushed to disk at checkpoint? If the log is already on the disk then what is the purpose of flushing the log to disk?

A: Log buffer is in memory. One of the conditions for the log buffer to be flushed to disk is when:

SQL Server gets a commit request of a [fully durable] transaction that changes data.

Answer 3

When an INSERT query is fired, SQL server records this in its log and sends the confirmation to user that query is complete. Meanwhile it also updates the data page. Both of these (log and data page) reside in the memory.

I think you have a misunderstanding here: at commit time, the (relevant part of) transaction log is committed to the log files on disk. This is what ensures that committed transactions are not lost. Yes, the log and data pages are also in memory, but that is just a buffering mechanism, it does not ensure durability of transactions.

Irrespective of the recovery model (simple, bulk or full), whenever a checkpoint occurs, SQL server will flush the log and dirty pages from memory into the disk.

Again, a misunderstanding. Checkpointing is a different process, whereby the log is copied into the main data files, and therefore the log on disk can be flushed. At this point, the data files on disk are now up to date with any previous transactions.

Question: Suppose there is a power failure after sending confirmation to user and prior to a checkpoint, then, since the log from memory is not yet written to disk, will this INSERT operation be lost, even though the user had got a confirmation? Does this violate the Durable property of ACID?

As explained above, all transactions are flushed to disk (in the log file) at commit time. In the event of an unexpected shutdown, at restart time the log is rolled forward, in other words: all committed transactions are pushed through to the main data pages.

Answer 4

It depends on where exactly the power failure occurs. A power failure on the storage will very often lead to corruption and a full restore followed by most of the times to an incomplete point in time recovery.

This is why storage is very often protected by a ups, as it should be. A failure on the server is normally not more than an inconvenience.