Here I am describing the t-log architecture at very high level related to transactions only.
for complete details refer the following link : http://msdn.microsoft.com/en-us/library/ms180892.aspx
- maintaining ACID through WAL(write ahead logging)
- during any update , sql server retrieves the page contains the record to buffer from disk and it will update the page in buffer only and write the same transaction in t-log in buffer.
- when the user commit the transaction then it has to write the t-log immediately to the disk to make the transaction to commit before flusing the data page(wal)
- during every checkpoint it will write the data pages back to the disk , so in case of any power failure before the checkpoint , it will still recover the modified records from the transaction log which is physically there in the disk during recovery
- Transaction log is in a file only, not organized as pages as in data file
- Transaction log writes all the transactions (mostly modifications) sequentially.
- every transaction in the transaction log will contain a LSN(log sequence number) which is auto incremented value.
- when you create a transaction explicitly( begin tran) , sql server writes the tranID + “before image” with lsn in the transaction log and tranID with “after image” with lsn
- it also records the “begin tran” and “commit/rollback tran” for the same tranID with next lsn
- irrespective of whether the transactions are committed or not , it writes the log and modified data pages to the disk during every checkpoint
- for the committed transactions – it will apply the after image
for the uncommitted transactions – it will put a lock on it so that no modification will occur
for the rollback transactions – it will apply the before image
- If there is any power failure before the checkpoint , which might have committed or uncommitted transactions recorded in t-log : while recovering , it will start writing the committed transactions permanently to the disk and the transactions which doesn’t have any corresponding “commit tran” will be rolled back.
- SQL Server wont read the transaction log except during a recovery.
- transaction log plays an important role during recovery and to maintain the state of the transaction as per ACID.
Keep the T-Log on a separate drive : As T-Log writes the data sequentially and always writes each and every transaction in transaction log , it better to place it in separate drive to minimize the disk contention to improve the performance.