pooyan_pdm (2/14/2014)
1-Is there any other process writing to the Log file other than1-checkpoints
2-transactions getting committed
3-Lazy writer ?
Yes. Think about what other processes use the log for any reason.
2- As soon as a transactions gets committed it's data are written from the buffer pool (Log cache) to the log file , but could the dirty pages for a long running transaction get flushed to the log file before the transaction gets committed assuming neither a checkpoint nor a lazy writer has been applied at the time?
Dirty pages don't go to the log file. Dirty pages are data pages and go to the data file. The dirty data pages for a transaction can be written to the data file at any time before or after the transaction commits, by the lazy writer or checkpoint process.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability