{"id":77,"date":"2018-05-28T10:44:17","date_gmt":"2018-05-28T10:44:17","guid":{"rendered":"http:\/\/dbtut.com\/?p=77"},"modified":"2020-10-08T07:53:30","modified_gmt":"2020-10-08T07:53:30","slug":"what-is-database-checkpoint-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/05\/28\/what-is-database-checkpoint-in-sql-server\/","title":{"rendered":"What is Database Checkpoint in SQL Server"},"content":{"rendered":"

Database Checkpoint<\/strong> process is one of the most important part of SQL Server Architecture. Before you read this article, you need to understand the significance of the transaction log file. <\/span><\/p>\n

I would recommend reading the article “What is SQL Server Transaction Log<\/a>“. <\/span><\/p>\n

I wanted to write this article because many people have question marks about the working principle of sql server. <\/span><\/p>\n

You might think this article is a little theoretical. <\/span>But I want to insist on reading anyone who wants to deepen on SQL Server. First you should understand SQL Server write ahead logging mechanism to understand checpoint.<\/span><\/p>\n

Write Ahead Logging in SQL Server<\/h2>\n

In SQL Server, data is not read directly from the disk.<\/p>\n

Data is first transferred from the disk to the buffer cache (the space used by the sql server for data and index pages in memory).<\/p>\n

As long as the data is in buffer cache, if a query needs this data, it reads this data from the buffer cache.<\/p>\n

Over time SQL Server need to modify the data in the buffer cache. So what happens to changed data in the buffer cache?<\/p>\n

The answer is below question;<\/p>\n

What is Dity Page in SQL Server?<\/h4>\n

Pages that have been modified in the Buffer Cache and have not yet been written to the disk are called dirty pages.<\/p>\n

Well, data changed in memory. And then? When will the changed data be written to disk?<\/span><\/p>\n

What is Log Flush in SQL Server?<\/h4>\n

When a page in the Buffer Cache is modified, it is not immediately written to the disk.<\/p>\n

First a log is created in the log cache (the area where the log records are stored in memory).<\/p>\n

Unless the relevant record in the log cache is physically written to the transaction log file(this is known as log flush<\/strong>), the record that has changed in the buffer cache is not written physically to the data file. This protect mechanism called as Write Ahead Logging<\/strong>.<\/p>\n

If the data is written to the data file before it is physically written to the transaction log file, rollback operation of uncommitted transactions cannot be performed if the server is shut down. Because, SQL Server performs recovery operations using Transaction Log file. If it can not find the transaction records in the Transaction Log file, it can not rollback these transactions. That’s why, SQL Server protects data integrity using WAL mechanism we have mentioned just before.<\/p>\n

What is Lazy Writer in SQL Server?<\/h2>\n

The write operation of data from the buffer cache to the disk is known as page flushing<\/strong>.<\/p>\n

The page flushing operation is performed by “Lazy Writer”.<\/p>\n

SQL Server regularly monitors resources, and triggers Lazy Writer when there is contention on memory.<\/p>\n

And this way, when dirty pages in the buffer cache are written to the disk, enough space is available in the buffer cache for other sessions to use.<\/p>\n

What is Checkpoint in SQL Server?<\/h2>\n

SQL Server periodically runs the checkpoint process. <\/span><\/p>\n

When Checkpoint runs, records in the log cache and dirty pages will be written to disk <\/span>as I mentioned above<\/span>(records that have changed since read from disk or since the last checkpoint was run).<\/span><\/p>\n

Checkpoint can also be run manually with the CHEKCPOINT command.<\/p>\n

But I recommend you not to interfere with SQL Server internal processes.<\/p>\n

Checkpoint’s purpose is to shorten the recovery time when the database is opened.<\/p>\n

I described a scenario related to this in the article named “What is SQL Server Transaction Log<\/a>“.<\/p>\n

SQL Server Recovery Process<\/h2>\n

Assume that the SQL Server Server unexpectedly shuts down.<\/p>\n

I refer to the LSN concept in the article titled “What is SQL Server Transaction Log<\/a>“.<\/p>\n

The page header(A 96-byte field that stores metadata information for the page) of all data pages contains the LSN information of the last log file that affects this page.<\/p>\n

I have already told you that SQL Server uses the transaction log file while it is being recovered.<\/p>\n

In the header of the data pages, the LSN information of the corresponding log record determines how the log record is to be recovered.(redone\/roll forward or undone\/roll back)<\/p>\n

Below you can see what happens in any situation.<\/p>\n

For a log that is associated with a committed transaction;<\/h4>\n