CHECKPOINT

wonder if data modified in the buffer data cache
are written to disk in the database file only at the end of the transaction
(commit/rollback) or if portion are written at each checkpoint ?
and next eventually rollback
example:
checkpoint1 checkpoint2 checkpoint3
T1 --!--!--!--commit
does data modified by T1 are partially written to disk at each checkpoint
or only when it is committed ?
In the doc i read
"A SQL Server 2000 checkpoint performs these processes in the current
database:
. Writes to the log file a record marking the start of the checkpoint.
...
Writes to disk all dirty log and data pages."
AT
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003When you issue a COMMIT, the log buffer is flushed and this guarantees
modified data are permanently persisted. The associated data pages may or
may not have been written to disk at the time of the commit because these
are written asynchronously by worker threads, the lazy writer and the
checkpoint process. A checkpoint writes all dirty pages to disk so, in your
example, any data modified by T1 is written during each of the 3
checkpoints.
If the server were to crash immediately after the commit in your example,
SQL Server would start forward recovery from the log at checkpoint 3 until
the end of the log was reached and them rollback any uncommitted
transactions. The end result is that the data modified by T1 after the last
checkpoint would be in the database and no committed data are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <t@.t.fr> wrote in message
news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> wonder if data modified in the buffer data cache
> are written to disk in the database file only at the end of the
transaction
> (commit/rollback) or if portion are written at each checkpoint ?
> and next eventually rollback
> example:
>
> checkpoint1 checkpoint2 checkpoint3
> T1 --!--!--!--commit
>
> does data modified by T1 are partially written to disk at each checkpoint
> or only when it is committed ?
>
> In the doc i read
> "A SQL Server 2000 checkpoint performs these processes in the current
> database:
> . Writes to the log file a record marking the start of the checkpoint.
> ...
> Writes to disk all dirty log and data pages."
>
> AT
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
>|||Alan
You also posted this in .setup, where I provided an answer, although not
quite as detailed as Dan's.
In the future, please do not post the same question to multiple groups, in
order to only have one thread of responses to follow,
and so people who see your question will know it has been aswered and won't
waste time answering it again.
One comment to Dan... you say that 'any data modified by T1 is written
during EACH of the the 3 checkpoints'. This is not true.
Once the data is written at the first checkpoint, it is no longer dirty, and
will not be written again at subsequent checkpoints. As you say,
only the dirty data is written to disk, but the process of writing to disk
during checkpoint makes that data no longer dirty.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eypIt4exDHA.4060@.TK2MSFTNGP11.phx.gbl...
> When you issue a COMMIT, the log buffer is flushed and this guarantees
> modified data are permanently persisted. The associated data pages may or
> may not have been written to disk at the time of the commit because these
> are written asynchronously by worker threads, the lazy writer and the
> checkpoint process. A checkpoint writes all dirty pages to disk so, in
your
> example, any data modified by T1 is written during each of the 3
> checkpoints.
> If the server were to crash immediately after the commit in your example,
> SQL Server would start forward recovery from the log at checkpoint 3 until
> the end of the log was reached and them rollback any uncommitted
> transactions. The end result is that the data modified by T1 after the
last
> checkpoint would be in the database and no committed data are lost.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Alan" <t@.t.fr> wrote in message
> news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> > wonder if data modified in the buffer data cache
> > are written to disk in the database file only at the end of the
> transaction
> > (commit/rollback) or if portion are written at each checkpoint ?
> > and next eventually rollback
> >
> > example:
> >
> >
> > checkpoint1 checkpoint2 checkpoint3
> >
> > T1 --!--!--!--commit
> >
> >
> >
> > does data modified by T1 are partially written to disk at each
checkpoint
> > or only when it is committed ?
> >
> >
> > In the doc i read
> > "A SQL Server 2000 checkpoint performs these processes in the current
> > database:
> > . Writes to the log file a record marking the start of the checkpoint.
> > ...
> >
> > Writes to disk all dirty log and data pages."
> >
> >
> >
> > AT
> >
> >
> >
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
> >
> >
>|||> Once the data is written at the first checkpoint, it is no longer dirty,
and
> will not be written again at subsequent checkpoints. As you say,
> only the dirty data is written to disk, but the process of writing to disk
> during checkpoint makes that data no longer dirty.
Thanks for the clarification, Kalen. I should have made that point clearer
in my response.
--
Dan Guzman
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%239BALRfxDHA.2064@.TK2MSFTNGP10.phx.gbl...
> Alan
> You also posted this in .setup, where I provided an answer, although not
> quite as detailed as Dan's.
> In the future, please do not post the same question to multiple groups, in
> order to only have one thread of responses to follow,
> and so people who see your question will know it has been aswered and
won't
> waste time answering it again.
> One comment to Dan... you say that 'any data modified by T1 is written
> during EACH of the the 3 checkpoints'. This is not true.
> Once the data is written at the first checkpoint, it is no longer dirty,
and
> will not be written again at subsequent checkpoints. As you say,
> only the dirty data is written to disk, but the process of writing to disk
> during checkpoint makes that data no longer dirty.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:eypIt4exDHA.4060@.TK2MSFTNGP11.phx.gbl...
> > When you issue a COMMIT, the log buffer is flushed and this guarantees
> > modified data are permanently persisted. The associated data pages may
or
> > may not have been written to disk at the time of the commit because
these
> > are written asynchronously by worker threads, the lazy writer and the
> > checkpoint process. A checkpoint writes all dirty pages to disk so, in
> your
> > example, any data modified by T1 is written during each of the 3
> > checkpoints.
> >
> > If the server were to crash immediately after the commit in your
example,
> > SQL Server would start forward recovery from the log at checkpoint 3
until
> > the end of the log was reached and them rollback any uncommitted
> > transactions. The end result is that the data modified by T1 after the
> last
> > checkpoint would be in the database and no committed data are lost.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >
> > "Alan" <t@.t.fr> wrote in message
> > news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> > > wonder if data modified in the buffer data cache
> > > are written to disk in the database file only at the end of the
> > transaction
> > > (commit/rollback) or if portion are written at each checkpoint ?
> > > and next eventually rollback
> > >
> > > example:
> > >
> > >
> > > checkpoint1 checkpoint2 checkpoint3
> > >
> > > T1 --!--!--!--commit
> > >
> > >
> > >
> > > does data modified by T1 are partially written to disk at each
> checkpoint
> > > or only when it is committed ?
> > >
> > >
> > > In the doc i read
> > > "A SQL Server 2000 checkpoint performs these processes in the current
> > > database:
> > > . Writes to the log file a record marking the start of the checkpoint.
> > > ...
> > >
> > > Writes to disk all dirty log and data pages."
> > >
> > >
> > >
> > > AT
> > >
> > >
> > >
> > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
> > >
> > >
> >
> >
>