{"id":788,"date":"2009-09-27T21:28:00","date_gmt":"2009-09-27T21:28:00","guid":{"rendered":"\/blogs\/paul\/post\/How-do-checkpoints-work.aspx"},"modified":"2017-04-13T12:47:36","modified_gmt":"2017-04-13T19:47:36","slug":"how-do-checkpoints-work-and-what-gets-logged","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/","title":{"rendered":"How do checkpoints work and what gets logged"},"content":{"rendered":"<p><span style=\"font-family: verdana, geneva; font-size: small;\">(Check out my Pluralsight online training course: <em><a href=\"http:\/\/www.pluralsight.com\/training\/Courses\/TableOfContents\/sqlserver-logging\" target=\"_blank\">SQL Server: Logging, Recovery, and the Transaction Log<\/a>.)<\/em><\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">This is a blog post I&#8217;ve been meaning to do for a while, and I&#8217;ve recently noticed some info on the web about checkpoints which is a little misleading, so I want to do a quick post to explain how checkpoints work as far as log records are concerned. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">When a checkpoint operation occurs, no matter how it&#8217;s triggered\u00a0(for instance\u00a0through a\u00a0manual <span style=\"font-family: 'courier new', courier;\">CHECKPOINT<\/span>, from a database or differential backup, or automatically) the same set of operations occurs: <\/span><\/p>\n<ul>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change. <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Before a page is written to disk, all log records up to and including the most recent log record describing a change to that page are written to disk (yes, log records can be cached in memory too). This guarantees recovery can work and is called <em>write-ahead logging<\/em>. Log records are written to the log sequentially, and log records from multiple transactions will be interspersed in the log. The log cannot be selectively written to disk, so writing a dirty page to disk that only has a single log record affecting it may mean writing many more previous log records to disk as well. <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Log records describing the checkpoint are generated. <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">The LSN of the checkpoint is recorded in the database boot page in the <span style=\"font-family: 'courier new', courier;\">dbi_checkptLSN<\/span> field (see <\/span><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-20-boot-pages-and-boot-page-corruption\/\"><span style=\"font-family: verdana, geneva; font-size: small;\">Search Engine Q&amp;A #20: Boot pages, and boot page corruption<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">). <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">If in the SIMPLE recovery model, the VLFs in the log are checked to see whether they can be marked inactive (called <em>clearing<\/em> or <em>truncating<\/em> the log &#8211; both of which are terrible misnomers, as nothing is either physically cleared or truncated). <\/span><\/div>\n<\/li>\n<\/ul>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">I&#8217;m using some terms you may not have come across &#8211; for a background primer on logging, recovery, and transaction log architecture see my article in the February 2009 TechNet Magazine &#8211; <\/span><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/2009.02.logging.aspx\"><span style=\"font-family: verdana, geneva; font-size: small;\">Understanding Logging and Recovery in SQL Server<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\"> (or come to my Wednesday November 4th PASS Spotlight session with the same title). <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Checkpoints are not really tracked in the transaction log &#8211; it just serves as a useful repository for information about which transactions are active at the time of the checkpoint. The LSN of the last checkpoint is recorded in the database boot page. This is where recovery starts, and if this page is inaccessible, the database cannot be attached, opened, or processed in any way &#8211; partly because it&#8217;s the boot page that knows whether the database was cleanly shut down or not, and partly because it&#8217;s the only place that records the LSN of the last checkpoint record. You may say, well it&#8217;s recorded in the transaction log too, but what if the log is corrupt in some way? <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">One area of confusion I&#8217;ve seen is that the checkpoint log records are overwritten by subsequent checkpoints. Absolutely not &#8211; once written, a log record is NEVER updated or overwritten &#8211; it will only be overwritten when the log wraps and the VLFs are re-used (see <\/span><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-more-on-the-circular-nature-of-the-log\/\"><span style=\"font-family: verdana, geneva; font-size: small;\">Inside the Storage Engine: More on the circular nature of the log<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">). <\/span><span style=\"font-family: verdana, geneva; font-size: small;\">This has led to further confusion about when checkpoint information is retrievable from the log, using commands such as <span style=\"font-family: 'courier new', courier;\">fn_dblog<\/span>. <\/span><\/p>\n<p><span style=\"font-size: small;\">For the rest of this post, I want to show you what&#8217;s going on in the transaction log when checkpoints occur under different circumstances.<\/span><\/p>\n<p>[Edit: note that from 2012 onward, there&#8217;s an additional log record about updating the boot page.]<\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Consider the following example: <\/span><\/p>\n<blockquote><p><span style=\"font-family: 'courier new', courier; font-size: small;\">CREATE DATABASE CheckpointTest;<br \/>\nGO<br \/>\nUSE CheckpointTest;<br \/>\nGO<br \/>\nCREATE TABLE t1 (c1 INT);<br \/>\nGO<br \/>\nINSERT INTO t1 VALUES (1);<br \/>\nGO<br \/>\nCHECKPOINT;<br \/>\nGO<br \/>\nSELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);<br \/>\nGO <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\"><span style=\"font-family: 'courier new', courier;\">Current LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n00000047:00000051:009b\u00a0 LOP_BEGIN_CKPT<br \/>\n00000047:00000091:0001\u00a0 LOP_END_CKPT<\/span> <\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">We see the log records for the checkpoint. In this case the checkpoint is very simple and so there are only two records &#8211; beginning and ending the checkpoint. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">If we run another checkpoint, what do we see? <\/span><\/p>\n<blockquote><p><span style=\"font-family: 'courier new', courier; font-size: small;\">CHECKPOINT;<br \/>\nGO<br \/>\nSELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);<br \/>\nGO <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\"><span style=\"font-family: 'courier new', courier;\">Current LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n00000047:00000092:0001\u00a0 LOP_BEGIN_CKPT<br \/>\n00000047:00000093:0001\u00a0 LOP_END_CKPT<\/span> <\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Only information about one checkpoint, but with different LSNs for the log records. It&#8217;s not that the previous checkpoint was overwritten, it&#8217;s that we did a checkpoint\u00a0&#8211; so the active portion of the log has moved forward and the log records for the previous checkpoint aren&#8217;t considered active any more as they&#8217;re not required (for instance, for database mirroring, an active transaction, a log backup, transactional replication). They&#8217;re still there in the log though, but just aren&#8217;t part of the required portion of the log and so aren&#8217;t dumped by <span style=\"font-family: 'courier new', courier;\">fn_dblog<\/span>. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Now, what if I create an active transaction? In another connection, I&#8217;ll do: <\/span><\/p>\n<blockquote><p><span style=\"font-family: verdana, geneva; font-size: small;\"><span style=\"font-family: 'courier new', courier;\">USE CheckpointTest;<br \/>\nGO<br \/>\nBEGIN TRAN;<br \/>\nGO<br \/>\nINSERT INTO t1 VALUES (1);<br \/>\nGO<\/span> <\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">And what if we do a checkpoint and look at the log now? <\/span><\/p>\n<blockquote><p><span style=\"font-family: verdana, geneva; font-size: small;\"><span style=\"font-family: 'courier new', courier;\">CHECKPOINT;<br \/>\nGO<br \/>\nSELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);<br \/>\nGO<\/span><\/span><\/p>\n<p>Current LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n00000047:00000094:0001\u00a0 LOP_BEGIN_XACT<br \/>\n00000047:00000094:0002\u00a0 LOP_INSERT_ROWS<br \/>\n00000047:00000094:0003\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000094:0004\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000094:0005\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000094:0006\u00a0 LOP_BEGIN_CKPT<br \/>\n<strong>00000047:00000096:0001\u00a0 LOP_XACT_CKPT<br \/>\n<\/strong>00000047:00000096:0002\u00a0 LOP_END_CKPT<\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">We see the start of the open transaction, the insert of the record, the update of row counts in metadata, and the checkpoint. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">You may notice that there&#8217;s another log record being generated for the checkpoint &#8211; <span style=\"font-family: 'courier new', courier;\">LOP_XACT_CKPT<\/span>. This is only generated when there are active (uncommitted) transactions and it lists information about all actvie transactions at the time the checkpoint begins. This is used during crash recovery to work out how far back in the transaction log to go to start REDO and UNDO operations (well, technically only UNDO will need to go this far back). Focusing in on this log record, we can see: <\/span><\/p>\n<blockquote><p><span style=\"font-family: 'courier new', courier; font-size: small;\">SELECT [Current LSN], [Operation], [Num Transactions], [Log Record]<br \/>\nFROM fn_dblog (NULL, NULL) WHERE [Operation] = &#8216;LOP_XACT_CKPT&#8217;;<br \/>\nGO <\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier; font-size: small;\">Current LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Num Transactions<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n00000047:00000096:0001\u00a0 LOP_XACT_CKPT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 <\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier; font-size: small;\">Log Record<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\n0x000018 &lt;snip&gt; 7805000000000000<strong>470000009400000001000<\/strong>401<strong>470000009400000002000<\/strong>00001 &lt;snip&gt; 6621000000000000 <\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">This log record contains information about each active (uncommitted)\u00a0transaction at the time of the checkpoint. Without going into all the details about what&#8217;s in the payload for this log record, you can see two things in there: <\/span><\/p>\n<ul>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">The LSN of the <span style=\"font-family: 'courier new', courier;\">LOP_BEGIN_XACT<\/span> log record for the oldest active transaction (the first bold number above &#8211; match it against the <span style=\"font-family: 'courier new', courier;\">LOP_BEGIN_XACT<\/span> in the log dump a little higher up) <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">The LSN of the first log record making a database change for that transaction (the second bold number above &#8211; match it against the <span style=\"font-family: 'courier new', courier;\">LOP_INSERT_ROWS<\/span> in the log dump a little higher up) <\/span><\/div>\n<\/li>\n<\/ul>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">You&#8217;ll notice that these LSNs are stored byte-reversed.\u00a0 <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">How about if we do another checkpoint? <\/span><\/p>\n<blockquote><p><span style=\"font-family: 'courier new', courier; font-size: small;\">CHECKPOINT;<br \/>\nGO<br \/>\nSELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);<br \/>\nGO <\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier; font-size: small;\">Current LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n00000047:00000094:0001\u00a0 LOP_BEGIN_XACT<br \/>\n00000047:00000094:0002\u00a0 LOP_INSERT_ROWS<br \/>\n00000047:00000094:0003\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000094:0004\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000094:0005\u00a0 LOP_COUNT_DELTA<br \/>\n<strong>00000047:00000094:0006\u00a0 LOP_BEGIN_CKPT<\/strong><br \/>\n00000047:00000096:0001\u00a0 LOP_XACT_CKPT<br \/>\n00000047:00000096:0002\u00a0 LOP_END_CKPT<br \/>\n<strong>00000047:00000097:0001\u00a0 LOP_BEGIN_CKPT<\/strong><br \/>\n00000047:00000098:0001\u00a0 LOP_XACT_CKPT<br \/>\n00000047:00000098:0002\u00a0 LOP_END_CKPT <\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">This time we see the log records for the current checkpoint and the previous one &#8211; as the active log stretches all the way back to the start of the oldest active\u00a0transaction &#8211; no matter how many checkpoints we do while there&#8217;s an active transaction. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Now what if we start another active transaction in a third connection? <\/span><\/p>\n<blockquote><p><span style=\"font-family: verdana, geneva; font-size: small;\"><span style=\"font-family: 'courier new', courier;\">USE CheckpointTest;<br \/>\nGO<br \/>\nBEGIN TRAN;<br \/>\nGO<br \/>\nINSERT INTO t1 VALUES (2);<br \/>\nGO<\/span> <\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">And then go back to the original connection and do another checkpoint and dump the log again: <\/span><\/p>\n<blockquote><p><span style=\"font-family: 'courier new', courier; font-size: small;\">CHECKPOINT;<br \/>\nGO<br \/>\nSELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);<br \/>\nGO <\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier; font-size: small;\">Current LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n<strong>00000047:00000094:0001\u00a0 LOP_BEGIN_XACT<br \/>\n<\/strong>00000047:00000094:0002\u00a0 LOP_INSERT_ROWS<br \/>\n00000047:00000094:0003\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000094:0004\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000094:0005\u00a0 LOP_COUNT_DELTA<br \/>\n<\/span><span style=\"font-family: 'courier new', courier;\"><span style=\"font-size: small;\"><strong>00000047:00000094:0006\u00a0 LOP_BEGIN_CKPT<br \/>\n<\/strong>00000047:00000096:0001\u00a0 LOP_XACT_CKPT<br \/>\n00000047:00000096:0002\u00a0 LOP_END_CKPT<br \/>\n<strong>00000047:00000097:0001\u00a0 LOP_BEGIN_CKPT<br \/>\n<\/strong>00000047:00000098:0001\u00a0 LOP_XACT_CKPT<br \/>\n00000047:00000098:0002\u00a0 LOP_END_CKPT<br \/>\n<strong>00000047:00000099:0001\u00a0 LOP_BEGIN_XACT<\/strong><br \/>\n00000047:00000099:0002\u00a0 LOP_INSERT_ROWS<br \/>\n00000047:00000099:0003\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000099:0004\u00a0 LOP_COUNT_DELTA<br \/>\n00000047:00000099:0005\u00a0 LOP_COUNT_DELTA<br \/>\n<\/span><\/span><span style=\"font-size: small;\"><span style=\"font-family: 'courier new', courier;\"><strong>00000047:00000099:0006\u00a0 LOP_BEGIN_CKPT<br \/>\n<\/strong>00000047:0000009b:0001\u00a0 LOP_XACT_CKPT<br \/>\n00000047:0000009b:0002\u00a0 LOP_END_CKPT<\/span><\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">You can see that we now have three sets of checkpoint log records, and two active transactions. Only one of these sets of checkpoint log records is the pertinent one &#8211; the previous two have been superceded, but the log records haven&#8217;t been overwritten or removed, as you can clearly see. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Looking inside all\u00a0the <span style=\"font-family: 'courier new', courier;\">LOP_XACT_CKPT<\/span> records, we can see (with a bit of creative formatting of the output): <\/span><\/p>\n<blockquote><p><span style=\"font-family: 'courier new', courier; font-size: small;\">SELECT [Current LSN], [Operation], [Num Transactions], [Log Record]<br \/>\nFROM fn_dblog (NULL, NULL) WHERE [Operation] = &#8216;LOP_XACT_CKPT&#8217;;<br \/>\nGO <\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier; font-size: small;\">Current LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Num Transactions<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n00000047:00000096:0001\u00a0 LOP_XACT_CKPT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<br \/>\n00000047:00000098:0001\u00a0 LOP_XACT_CKPT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<br \/>\n00000047:0000009b:0001\u00a0 LOP_XACT_CKPT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 <\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier; font-size: small;\">Log Record<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n0x000018 &lt;snip&gt; 7805000000000000<strong>470000009400000001000<\/strong>40147000000940000000200000001 &lt;snip&gt; 21000000000000<br \/>\n0x000018 &lt;snip&gt; 7805000000000000<strong>470000009400000001000<\/strong>40147000000940000000200000001 &lt;snip&gt; 21000000000000<br \/>\n0x000018 &lt;snip&gt; 7805000000000000<strong>470000009400000001000<\/strong>40147000000940000000200000001 &lt;snip&gt; 21000000000000 &#8230;<br \/>\n&#8230; 7905000000000000<strong>470000009900000001000<\/strong>4014700000099000000020000000100000002000000DC000000 <\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">The first two checkpoints only list one active transaction and the most recent one lists two, as we&#8217;d expect. The log record payload for the first two lists the same oldest active transaction (highlighted in bold). The payload for the last checkpoint lists the same oldset active transaction (as that hasn&#8217;t changed), but now lists an additional transaction (match the <strong><span style=\"font-family: 'courier new', courier;\">470000009900000001000<\/span><\/strong> with the LSN of the second <span style=\"font-family: 'courier new', courier;\">LOP_BEGIN_XACT<\/span> in the log dump above), and has a transaction count of two.<\/span><\/p>\n<p><span style=\"font-size: small;\">Just to finish things off, let&#8217;s look at the boot page of the database either using DBCC PAGE or DBCC DBINFO:<\/span><\/p>\n<blockquote><p><span style=\"font-family: 'courier new', courier; font-size: small;\">DBCC TRACEON (3604);<br \/>\nGO<br \/>\nDBCC DBINFO (&#8216;CheckpointTest&#8217;);<br \/>\nGO<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier; font-size: small;\">DBINFO STRUCTURE:<\/span><br \/>\n<span style=\"font-family: 'courier new', courier; font-size: small;\">DBINFO @0x6711EF64<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;\"><span style=\"font-size: small;\">dbi_dbid = 18\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_status = 65536\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_nextid = 2089058478<br \/>\ndbi_dbname = CheckpointTest\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_maxDbTimestamp = 2000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_version = 611<br \/>\ndbi_createVersion = 611\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_ESVersion = 0<br \/>\ndbi_nextseqnum = 1900-01-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_crdate = 2009-09-28 07:06:35.873<br \/>\ndbi_filegeneration = 0<br \/>\n<strong>dbi_checkptLSN<\/strong><\/span><\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;\"><span style=\"font-size: small;\"><strong>m_fSeqNo = 71\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_blockOffset = 153\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_slotId = 6<br \/>\n<\/strong>dbi_RebuildLogs = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_dbccFlags = 2<br \/>\ndbi_dbccLastKnownGood = 1900-01-01 00:00:00.000<br \/>\n&lt;snip&gt;<\/span><\/span><\/p><\/blockquote>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">The\u00a0<span style=\"font-family: 'courier new', courier;\">dbi_checkptLSN<\/span>\u00a0is dumped out in decimal &#8211; converting to hex gives us \u00a0<span style=\"font-family: 'courier new', courier;\">(47:99:6)<\/span>, which exactly matches the LSN of the most recent\u00a0<span style=\"font-family: 'courier new', courier;\">LOP_BEGIN_CKPT<\/span> record in the log dump above.<\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Hopefully this explains things clearly!<\/span><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) This is a blog post I&#8217;ve been meaning to do for a while, and I&#8217;ve recently noticed some info on the web about checkpoints which is a little misleading, so I want to do a quick post to explain how [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,48,62,98],"tags":[],"class_list":["post-788","post","type-post","status-publish","format-standard","hentry","category-checkpoint","category-inside-the-storage-engine","category-on-disk-structures","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How do checkpoints work and what gets logged - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How do checkpoints work and what gets logged - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) This is a blog post I&#8217;ve been meaning to do for a while, and I&#8217;ve recently noticed some info on the web about checkpoints which is a little misleading, so I want to do a quick post to explain how [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-09-27T21:28:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:47:36+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/\",\"name\":\"How do checkpoints work and what gets logged - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-09-27T21:28:00+00:00\",\"dateModified\":\"2017-04-13T19:47:36+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How do checkpoints work and what gets logged\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How do checkpoints work and what gets logged - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/","og_locale":"en_US","og_type":"article","og_title":"How do checkpoints work and what gets logged - Paul S. Randal","og_description":"(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) This is a blog post I&#8217;ve been meaning to do for a while, and I&#8217;ve recently noticed some info on the web about checkpoints which is a little misleading, so I want to do a quick post to explain how [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/","og_site_name":"Paul S. Randal","article_published_time":"2009-09-27T21:28:00+00:00","article_modified_time":"2017-04-13T19:47:36+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/","name":"How do checkpoints work and what gets logged - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-09-27T21:28:00+00:00","dateModified":"2017-04-13T19:47:36+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"How do checkpoints work and what gets logged"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/788","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=788"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/788\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=788"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=788"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=788"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}