Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.
No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.
|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:

> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty pages
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
>
>