Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoint is a point at which all dirty pages are guaranteed to have been written to disk.
By default, SQL Server automatically controls the checkpoint interval. If few modifications are made to the database, the time interval between checkpoints can be long. If the database is constantly being modified, the automatic checkpoints occur more frequently.
If your database is in Simple Recovery mode, after the checkpoint runs, SQL Server will truncate the log file.
What is TRACE FLAGS?
The trace flags are used to temporarily set specific server characteristics or switch off a particular behavior. What does it mean?
Example: trace flag 3505
By default, SQL Server controls when the checkpoint occurs. SQL Server automatically issues checkpoints to reduce the SQL Server recovery time and to permit log space reuse (truncation).
When you don't want this default behavior happens for a critical period, you can set the TRACE FLAG 3505 ON as DBCC TRACEON(3505,-1). After the application completes, you can turn the automatic checkpoint on as DBCC TRACEOFF(3505).
DBCC TRACEON(3505,-1) -->disable the automatic checkpoints
DBCC TRACEOFF(3505,-1) -->enable the automatic checkpoints.
