Does Tlog actually contain data ?

Say I update a column affecting a few thousand rows but do not issue a
commit .. I want to know if all these new values for the column, are they in
the Tlog or they actually get checkpointed to the data files as well ?
If they do not go to the data files, would it be true that only committed
data makes it to the data files while uncommitted data gets written to the
Transaction Log files ?
Thank you..Hassan
Yes , they are. In sql server 2000 a virtual tables "inserted" and
"deleted" (within a triggers) are reading the data from LOG file
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Say I update a column affecting a few thousand rows but do not issue a
> commit .. I want to know if all these new values for the column, are they
> in the Tlog or they actually get checkpointed to the data files as well ?
> If they do not go to the data files, would it be true that only committed
> data makes it to the data files while uncommitted data gets written to the
> Transaction Log files ?
> Thank you..
>|||Yes
Only committed data will be written to the data files.
Regards
Amish Shah
http://shahamishm.tripod.com
Uri Dimant wrote:
> Hassan
> Yes , they are. In sql server 2000 a virtual tables "inserted" and
> "deleted" (within a triggers) are reading the data from LOG file
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
> > Say I update a column affecting a few thousand rows but do not issue a
> > commit .. I want to know if all these new values for the column, are they
> > in the Tlog or they actually get checkpointed to the data files as well ?
> >
> > If they do not go to the data files, would it be true that only committed
> > data makes it to the data files while uncommitted data gets written to the
> > Transaction Log files ?
> >
> > Thank you..
> >|||> Only committed data will be written to the data files.
That is not correct, I'm afraid. SQL Server can write pages that are dirty and not committed to the
database data files. This can happen by both the lazywriter and the checkpoint process. SQL Server
makes sure that all log records (which can be cached) up to that point are written first, however.
This is called the Write Ahead protocol (WAL). See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amish" <shahamishm@.gmail.com> wrote in message
news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
> Yes
> Only committed data will be written to the data files.
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Uri Dimant wrote:
>> Hassan
>> Yes , they are. In sql server 2000 a virtual tables "inserted" and
>> "deleted" (within a triggers) are reading the data from LOG file
>>
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> > Say I update a column affecting a few thousand rows but do not issue a
>> > commit .. I want to know if all these new values for the column, are they
>> > in the Tlog or they actually get checkpointed to the data files as well ?
>> >
>> > If they do not go to the data files, would it be true that only committed
>> > data makes it to the data files while uncommitted data gets written to the
>> > Transaction Log files ?
>> >
>> > Thank you..
>> >
>|||Ok got it,
When checkpoint / lazywriter runs it writes dirty pages to disk and
lock that data to prevent committed view of it. User can view that data
only after data is committed. Nice to know...
Thanks Tibor.
Regards
Amish shah
Tibor Karaszi wrote:
> > Only committed data will be written to the data files.
> That is not correct, I'm afraid. SQL Server can write pages that are dirty and not committed to the
> database data files. This can happen by both the lazywriter and the checkpoint process. SQL Server
> makes sure that all log records (which can be cached) up to that point are written first, however.
> This is called the Write Ahead protocol (WAL). See
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for details.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
> > Yes
> > Only committed data will be written to the data files.
> >
> > Regards
> > Amish Shah
> > http://shahamishm.tripod.com
> >
> >
> > Uri Dimant wrote:
> >> Hassan
> >> Yes , they are. In sql server 2000 a virtual tables "inserted" and
> >> "deleted" (within a triggers) are reading the data from LOG file
> >>
> >>
> >> "Hassan" <Hassan@.hotmail.com> wrote in message
> >> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
> >> > Say I update a column affecting a few thousand rows but do not issue a
> >> > commit .. I want to know if all these new values for the column, are they
> >> > in the Tlog or they actually get checkpointed to the data files as well ?
> >> >
> >> > If they do not go to the data files, would it be true that only committed
> >> > data makes it to the data files while uncommitted data gets written to the
> >> > Transaction Log files ?
> >> >
> >> > Thank you..
> >> >
> >|||Data is always viewed and modified in cache. Checkpoint has nothing to do with locking. As soon as
you do, say, a modification, the rows are locked, and the lock is held until end of transaction.
Since other users also read the data from cache (never directly from disk), they will be blocked,
regardless of whether the page is dirty and regardless of the checkpoint process.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amish" <shahamishm@.gmail.com> wrote in message
news:1166538947.256500.58740@.48g2000cwx.googlegroups.com...
> Ok got it,
> When checkpoint / lazywriter runs it writes dirty pages to disk and
> lock that data to prevent committed view of it. User can view that data
> only after data is committed. Nice to know...
> Thanks Tibor.
> Regards
> Amish shah
>
> Tibor Karaszi wrote:
>> > Only committed data will be written to the data files.
>> That is not correct, I'm afraid. SQL Server can write pages that are dirty and not committed to
>> the
>> database data files. This can happen by both the lazywriter and the checkpoint process. SQL
>> Server
>> makes sure that all log records (which can be cached) up to that point are written first,
>> however.
>> This is called the Write Ahead protocol (WAL). See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for details.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
>> > Yes
>> > Only committed data will be written to the data files.
>> >
>> > Regards
>> > Amish Shah
>> > http://shahamishm.tripod.com
>> >
>> >
>> > Uri Dimant wrote:
>> >> Hassan
>> >> Yes , they are. In sql server 2000 a virtual tables "inserted" and
>> >> "deleted" (within a triggers) are reading the data from LOG file
>> >>
>> >>
>> >> "Hassan" <Hassan@.hotmail.com> wrote in message
>> >> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> >> > Say I update a column affecting a few thousand rows but do not issue a
>> >> > commit .. I want to know if all these new values for the column, are they
>> >> > in the Tlog or they actually get checkpointed to the data files as well ?
>> >> >
>> >> > If they do not go to the data files, would it be true that only committed
>> >> > data makes it to the data files while uncommitted data gets written to the
>> >> > Transaction Log files ?
>> >> >
>> >> > Thank you..
>> >> >
>> >
>|||Tibor,
So the dirty pages that are not committed can be written to both the data
and log files. Does that mean that if its written to the data files, it
would get cleared from the log files even though its still not committed ?
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2350BLy3IHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Data is always viewed and modified in cache. Checkpoint has nothing to do
> with locking. As soon as you do, say, a modification, the rows are locked,
> and the lock is held until end of transaction. Since other users also read
> the data from cache (never directly from disk), they will be blocked,
> regardless of whether the page is dirty and regardless of the checkpoint
> process.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1166538947.256500.58740@.48g2000cwx.googlegroups.com...
>> Ok got it,
>> When checkpoint / lazywriter runs it writes dirty pages to disk and
>> lock that data to prevent committed view of it. User can view that data
>> only after data is committed. Nice to know...
>> Thanks Tibor.
>> Regards
>> Amish shah
>>
>> Tibor Karaszi wrote:
>> > Only committed data will be written to the data files.
>> That is not correct, I'm afraid. SQL Server can write pages that are
>> dirty and not committed to the
>> database data files. This can happen by both the lazywriter and the
>> checkpoint process. SQL Server
>> makes sure that all log records (which can be cached) up to that point
>> are written first, however.
>> This is called the Write Ahead protocol (WAL). See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> for details.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
>> > Yes
>> > Only committed data will be written to the data files.
>> >
>> > Regards
>> > Amish Shah
>> > http://shahamishm.tripod.com
>> >
>> >
>> > Uri Dimant wrote:
>> >> Hassan
>> >> Yes , they are. In sql server 2000 a virtual tables "inserted" and
>> >> "deleted" (within a triggers) are reading the data from LOG file
>> >>
>> >>
>> >> "Hassan" <Hassan@.hotmail.com> wrote in message
>> >> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> >> > Say I update a column affecting a few thousand rows but do not
>> >> > issue a
>> >> > commit .. I want to know if all these new values for the column,
>> >> > are they
>> >> > in the Tlog or they actually get checkpointed to the data files as
>> >> > well ?
>> >> >
>> >> > If they do not go to the data files, would it be true that only
>> >> > committed
>> >> > data makes it to the data files while uncommitted data gets written
>> >> > to the
>> >> > Transaction Log files ?
>> >> >
>> >> > Thank you..
>> >> >
>> >
>|||> So the dirty pages that are not committed can be written to both the data and log files.
Correct.
> Does that mean that if its written to the data files, it would get cleared from the log files even
> though its still not committed ?
By cleared, you mean like when you do a log backup and log records are removed from the ldf file?
the log can only be truncated to the oldest open transaction, so log records for uncommitted
transactions will not be removed "prematurely". If that was the case, then SQL Server would be in a
situation where it can't perform a rollback, which isn't acceptable.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <Hassan@.hotmail.com> wrote in message news:eWb9Xu4IHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Tibor,
> So the dirty pages that are not committed can be written to both the data and log files. Does that
> mean that if its written to the data files, it would get cleared from the log files even though
> its still not committed ?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%2350BLy3IHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> Data is always viewed and modified in cache. Checkpoint has nothing to do with locking. As soon
>> as you do, say, a modification, the rows are locked, and the lock is held until end of
>> transaction. Since other users also read the data from cache (never directly from disk), they
>> will be blocked, regardless of whether the page is dirty and regardless of the checkpoint
>> process.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1166538947.256500.58740@.48g2000cwx.googlegroups.com...
>> Ok got it,
>> When checkpoint / lazywriter runs it writes dirty pages to disk and
>> lock that data to prevent committed view of it. User can view that data
>> only after data is committed. Nice to know...
>> Thanks Tibor.
>> Regards
>> Amish shah
>>
>> Tibor Karaszi wrote:
>> > Only committed data will be written to the data files.
>> That is not correct, I'm afraid. SQL Server can write pages that are dirty and not committed to
>> the
>> database data files. This can happen by both the lazywriter and the checkpoint process. SQL
>> Server
>> makes sure that all log records (which can be cached) up to that point are written first,
>> however.
>> This is called the Write Ahead protocol (WAL). See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for details.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
>> > Yes
>> > Only committed data will be written to the data files.
>> >
>> > Regards
>> > Amish Shah
>> > http://shahamishm.tripod.com
>> >
>> >
>> > Uri Dimant wrote:
>> >> Hassan
>> >> Yes , they are. In sql server 2000 a virtual tables "inserted" and
>> >> "deleted" (within a triggers) are reading the data from LOG file
>> >>
>> >>
>> >> "Hassan" <Hassan@.hotmail.com> wrote in message
>> >> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> >> > Say I update a column affecting a few thousand rows but do not issue a
>> >> > commit .. I want to know if all these new values for the column, are they
>> >> > in the Tlog or they actually get checkpointed to the data files as well ?
>> >> >
>> >> > If they do not go to the data files, would it be true that only committed
>> >> > data makes it to the data files while uncommitted data gets written to the
>> >> > Transaction Log files ?
>> >> >
>> >> > Thank you..
>> >> >
>> >
>>
>