Understanding the Commit process regarding the transaction Logs

I have a query about transaction logs regarding sql server 2005-08. When is a transaction written to the transaction log. Is it written the the data file before its written to the log file or is it written to the log file first before its commited to the databas开发者_运维问答e.

Thanks

It is written to the log file first. This principle is called Write Ahead Logging.

If the opposite was to occur and data was written to the data file before the log there would be no way to recover from a crash reliably.

As Martin says, it's written to the log as soon as you commit the transaction. Dirty pages (that is, data that has been modified) will be written asynchronously to the datafiles during the checkpoint process.

This is also the reason transactions can increase performance immensely. If you have a tight loop that performs an insert statement by itself on each iteration, each iteration will effectively hit the disk as the log is written. If you instead open a transaction outside of the loop, the disk will only be hit once, as the transaction is committed afterwards.