Log File Architecture in SQL Server

 Whenever any query is processed, the data will be passed to Data file. Below is the process how a query is processed in SQL Server/ Importance of Log File Architecture:

• Database has Data file and Log file.
• The query statement is passed to the buffer cache and log cache.
• The data in the buffer cache is called as Dirty Data or Dirty Blocks.
• Buffer cache contains the dirty data (the updated data corresponding to the query given in the application).
• The process of writing the data from buffer cache to the data files of the database in the form of chuncks is
  called as Checkpoint Process.
• Each chunck contains 512 KB.
• The query is written into the log file from the log cache.
• If any type of failure occurs while writing data to the data file, then the query in the log file is executed at the last commit transaction processed (refer commit process down) and the remaining data is written to the database whenever we start the server.
• This process of writing data to the database after a failure from the log file is called as Recovery.
• Procedure Cache contains the execution plan.
• Context Cache contains the data of the stored procedure.
• Server Level Data Structure contains the Server level information.

Commit Process:

• As soon as commit statement is written to the log file it throws a token to the user that commit is completed successfully (Ex. 1 row affected), this process is called as Commit Process.