Different manifestations of SQL Server CheckPoint in three recovery models

The recovery model for SQL Server includes the simple recovery model, the bulk-logged mode, and the full recovery model

When we execute the checkpoint, we actually update the data page data in the cache to the data file.

And the change of log file is how to express, we together through the example look.

First we create a database (omitted from the Build library script)

Setting up a database add five records to the simple recovery model

ALTER DATABASE Dbtrain set recovery simple

INSERT into test values (1, ' wwww ')

Go 5

Then execute DBCC log (' Dbtrain ', 2) to see the current activity log information

If you re-execute

Checkpoint

DBCC log (' Dbtrain ', 2)

You will see that the log is truncated, leaving only the log records of the checkpoint operation

If you replace the full recovery model with the bulk-logged mode

ALTER DATABASE Dbtrain set recovery full/bulk_logged

Repeating the above action will find that the log is still truncated, which makes people very puzzled.

The reason is because we are the newly created database, without a full backup,

The restore mode is simple before you make a backup, no matter how you set it up.

It makes no sense to keep the transaction log if you do not make a full backup.

Different manifestations of SQL Server CheckPoint in three recovery models