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.
Leave a comment

1 Comment

  1. Bhuvana

     /  July 17, 2013

    plz specify, what is execution plan , what exactly mean by that
    and if any failure occurs in datafile, why it goes to log file.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: