Google Groups : microsoft.public.sqlserver.server: ">

Will transaction log affect query speed ?

or it will only write to the data file after perform the transaction
log backup ? '

> No, data is written to the database after the transaction is commited.

Actually, the data is guaranteed to be written to the log file when the
transaction is committed. It is written in the data file only when a
checkpoint is made (checkpoints are performed automatically by SQL
Server, at variable intervals). However, this should not affect the
performance of SELECT queries, because the dirty data pages are kept in
memory (it's not necessary to read the log to reconstruct these pages).
The log is used to reconstruct the data pages that were not written to
disk only in the event of a recovery after an unexpected shutdown.

Some quotes from Books Online:

' A commit operation forces all log records for a transaction to the
log file so that the transaction is fully recoverable even if the
server is shut down. A commit operation does not have to force all the
modified data pages to disk as long as all the log records are flushed
to disk. A system recovery can roll the transaction forward or backward
using only the log records.

Periodically, each instance of SQL Server ensures that all dirty log
and data pages are flushed. This is called a checkpoint. Checkpoints
reduce the time and resources needed to recover when an instance of SQL
Server is restarted. ' "