How the CHECKPOINT process deals with uncommitted transactions

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

At the beginning of my SQL Server Performance Tuning Workshop I always start by giving a general overview of what happens within SQL Server when we execute a query. Executing a SELECT query is quite simple, but executing a DML statement is more complex, because SQL Server has to change the corresponding page in memory, and has to log the whole transaction within the transaction log.

After talking about these specific steps, I always ask the same question: what happens when we have an uncommitted transaction, then the CHECKPOINT process kicks in, and then SQL Server crashes? Do we have the uncommitted data in our data files? Think for a few seconds, and write down your answer.

Creating the test scenario

Now I want to recreate this specific scenario together with you, and finally you will see if you were right. In the first step of this scenario I create a new database, a new table, and insert a record into it.

After we have inserted the record we want to know the page number where SQL Server stored that specific record. We can use the DBCC IND command to return all the pages of a specific table. In my case SQL Server used the page id 303.

Retrieving the page id

When we now dump out the page with the DBCC PAGE command (we have to have enabled traced flag 3604 in our session to see the output), we can see the hex values of the inserted A, B, and C values.

Dumping out the data page

But what happens now when we perform a CHECKPOINT process, and finally kill SQL Server? Does the uncommitted data get written physically to the data file? Let’s try it…

Crashing and recovering SQL Server

Let’s begin now a new transaction and update the first column of the inserted record.

As you can see from the code, we haven’t yet committed this transaction! It is still a pending, uncommitted transaction. And now let’s open another session, where we will manually perform a CHECKPOINT process, and finally crash SQL Server.

Do you think now that the uncommitted transaction was written to the data file? Not sure about it? Let’s figure it out! Let’s open the data file in a hex editor (like XVI32), and jump to the beginning of page id 303. The page id itself is just the physical offset within the data file where the page starts – multiplied by 8192 bytes, because the page size in SQL Server is 8kb. Therefore the page 303 starts at the integer offset 2482176 (303 x 8192). When we look at the hex dump we can see our uncommitted data!

We have uncommitted data in our data file!

The CHECKPOINT process doesn’t differentiate between a committed and an uncommitted transaction. It just goes to the Buffer Manager and asks for all dirty pages regardless of their transaction state.

Do we have now an inconsistent, corrupt database? No, not really. Because when we now startup SQL Server, every database goes through the Crash Recovery phase and all uncommitted transactions are just rolled back. We can see that behavior in the SQL Server log, when SQL Server starts up:

Crash Recovery runs during SQL Server startup

Summary

The CHECKPOINT doesn’t care about the state of your transactions. Every dirty page from the Buffer Pool is written out to the data files. If SQL Server crashes it doesn’t matter, because Crash Recovery is able to recover your database into a fully consistent state. I hope that this blog posting gave you a better understanding of the CHECKPOINT process, and how it works together with uncommitted transactions.

As home work you could also leave me a comment telling me about other scenarios where SQL Server has to run Crash Recovery to get a consistent view for your databases. How many different scenarios do you know in SQL Server? 🙂

Like or share to get the source code.

Thanks for your time,

-Klaus

3 Comments

It`s your turn

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

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top