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:[vbcol=seagreen]
> 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...|||> 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 a
nd not committed to the
database data files. This can happen by both the lazywriter and the checkpoi
nt process. SQL Server
makes sure that all log records (which can be cached) up to that point are w
ritten first, however.
This is called the Write Ahead protocol (WAL). See
http://www.microsoft.com/technet/pr...ver/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:
>|||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:[vbcol=seagreen]
> 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 checkp
oint 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/pr...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...|||Data is always viewed and modified in cache. Checkpoint has nothing to do wi
th 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 pro
cess.
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:
>|||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...
>|||> 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 remov
ed 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 S
QL 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 i
n message
> news:%2350BLy3IHHA.4376@.TK2MSFTNGP03.phx.gbl...
>