Sunday, November 21, 2010

SQL Server Transaction log

Here I am describing the t-log architecture at very high level related to transactions only.
for complete details refer the following link  :

  1. maintaining ACID through WAL(write ahead logging)
    1. 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.
    2. 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)
    3. 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
  2. Transaction log is in a file only, not organized as pages as in data file
  3. Transaction log writes all the transactions (mostly modifications)  sequentially.
  4. every transaction in the transaction log will contain a  LSN(log sequence number) which is auto incremented value.
  5. 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
  6. it also records the “begin tran” and “commit/rollback tran” for the same tranID with next lsn
  7. irrespective of whether the transactions are committed or not , it writes the log and modified data pages to the disk during every checkpoint
  8. 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
  9. 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.
  10. SQL Server wont read the transaction log except during a recovery.
  11. 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.

Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...