ID Number: Q78363
1.10 1.11 4.20
OS/2
Summary:
To improve performance, SQL Server maintains a cache of the previously
read and/or modified ("dirty") pages. Periodically, these pages are
physically written to the disk, ensuring that the changes to any rows
are reflected in user databases.
More Information:
SQL Server will flush pages from the cache to the disk under the
following conditions:
1. When a checkpoint is issued.
There are five conditions that can cause a checkpoint to be issued:
a. The database owner or the system administrator (SA) can issue a
CHECKPOINT command at any time.
b. When a DUMP DATABASE or DUMP TRANSACTION command is issued, the
first step of the DUMP process is to do a checkpoint in that
database.
c. A checkpoint is issued in each database after the recovery
process has finished recovering that database. This feature can
be disabled by using the sp_dboption system procedure with the
"no chkpt on recovery" option set to true.
d. A checkpoint will automatically be issued by the checkpoint
checking process when it determines that enough transactions
have occurred since the last checkpoint. Although the checkpoint
checking process wakes up about once per minute to see how much
activity has taken place, the frequency with which it actually
does a checkpoint is determined by the number of transactions
and the value that has been set for the recovery interval. Note
that if the "trunc. log on chkpt." option has been set using
sp_dboption, a checkpoint will occur every time the checkpoint
checking process wakes up (about once per minute).
e. A checkpoint is issued when the SA issues the SHUTDOWN command.
2. When a new page needs to be brought into the cache.
When SQL Server determines that a page is needed that is not
currently in the cache, it looks for a free buffer in the cache
page chain. When it has finished going through the chain, the page
from the oldest buffer is flushed to disk.
3. At the end of a fast bulk copy or SELECT INTO.
When the fast BCP (non-logged version) is used, or when a table is
created using SELECT INTO, the newly-inserted rows are not logged.
Also, many or all of them may still be in the cache. To lessen the
chance of losing data in the event that SQL Server is stopped
without a checkpoint, all dirty pages are flushed to disk when the
BCP or SELECT INTO finishes.
4. Log pages are flushed when a transaction ends.
When a transaction ends, either with a COMMIT or ABORT, the log
pages are flushed to disk.
5. When a page is split, the newly allocated page is immediately
flushed to disk.
6. LOAD DATABASE writes all pages to disk.
When a database is being loaded with the LOAD DATABASE command, all
pages in the dump are written directly to disk. In addition, any
pages in the database that are not in the dump are also initialized
and flushed to disk. For example, if you are loading a dump from a
4 MB database into a 10 MB database, the remaining 6 MB of pages
will all be initialized and written to disk.
Additional reference words: 1.10 1.11 4.20 transaction checkpoint cache