Checkpoint in SQL Server

As mentioned earlier, recovery interval can control how much of the modified data SQL Server retains in memory. In fact, we have a question not to say.

That is, the modified data in memory is written back to disk, which means that the data version on the disk and the data operation in the log are synchronized. Of course it means that if the system crashes this time, SQL Server does not need to be restored.

But if it does collapse at this time, how does SQL Server know that the data for these log operations has been written to disk, so SQL Server has a concept.

The concept is checkpoint.

When SQL Server writes some modified data (called dirty data) back to disk (the term of this operation is flush), SQL Server leaves a tag in the log to indicate that the data before this tag is already written to disk. And this tag is called checkpoint.

So we can say that checkpoint represents a flush operation.

SQL Server triggers the checkpoint in the following cases, which is flush:

SQL Server when the service is stopped normally

The log requires a recovery operation that takes more than recovery interval time

SQL Server has insufficient memory available

When the administrator manually submitted the checkpoint command

More than 70% data in the log file