Database Checkpoints: Faster Recovery After a Crash
A database checkpoint creates a known good point for faster crash recovery. Instead of writing every change to disk, it periodically flushes modified data from memory, reducing the amount of log data to process.
Why it exists
To solve the tension between performance and durability. Writing every database change to disk immediately would be extremely slow and a major performance bottleneck. Modifying data only in memory is fast but risks total data loss on a crash. Checkpoints provide a middle ground for durable, high-performance operations.
The mental model
Think of a checkpoint as taking a periodic "save game" snapshot. Instead of saving after every single move, which would be tedious and slow, the game saves your progress every ten minutes. If the power goes out, you only lose the last few minutes of progress, not everything since you started. A database checkpoint saves the current state of modified data to disk, so after a crash, the database only needs to replay transactions that occurred after that save point.
How it works
The database engine performs modifications to data pages in memory, in what's called the buffer cache. These modified but not-yet-disk-written pages are called "dirty pages". A checkpoint process does two main things: first, it writes all current dirty pages from memory to disk; second, it writes information about the checkpoint itself into the transaction log. This marks a point in the log from which recovery needs to begin. After a crash, the database can look at the last successful checkpoint and only needs to roll forward the changes in the transaction log that occurred after it, reducing recovery time.
When to use it
Checkpoints are a fundamental part of database operation. In SQL Server, different types are used in specific scenarios. Automatic checkpoints run in the background to meet a configured recovery time objective. Indirect checkpoints are a more modern default, also targeting a specific recovery time on a per-database basis. Manual checkpoints can be issued by an administrator, for instance, before a planned maintenance window. Internal checkpoints are used by other server operations, like creating a database backup, to guarantee a consistent state.
When not to use it
You don't "not use" checkpoints, but you must understand their main limitation. They do not eliminate the impact of long-running, uncommitted transactions. If a transaction started an hour ago and is still open when a checkpoint runs, the log records for that transaction cannot be truncated. This means that even with frequent checkpoints, recovery time can be extended significantly by these long-running operations. The footgun is relying on checkpoints alone for fast recovery without managing transaction scope.
One canonical example
In SQL Server, an administrator can configure a database to use indirect checkpoints with a specific goal. By running ALTER DATABASE ... SET TARGET_RECOVERY_TIME = 60 SECONDS, you are telling SQL Server to manage its checkpoint frequency to ensure that, in the event of a crash, recovery should take no more than approximately 60 seconds. The database engine will then issue checkpoints in the background as needed to meet this user-specified target.
Interview question
What is the primary benefit of implementing database checkpoints?
- a.To ensure that all database changes are immediately persisted to disk, guaranteeing zero data loss.
- b.To improve query performance by frequently reorganizing data pages on disk.
- c.To minimize the amount of transaction log processing required to restore the database after a system crash.Correct
- d.To prevent long-running, uncommitted transactions from blocking other database operations.
Why? this is the answer
The card states that checkpoints reduce the amount of log data to process and allow the database to only replay transactions that occurred after the checkpoint, thereby reducing recovery time. Option A describes a scenario that checkpoints avoid to improve performance, as writing every change immediately is slow.
Just read this? Test yourself on what you have been reading.
Read the original → learn.microsoft.com
- #databases
- #recovery
- #performance
- #sql server
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles