How does sql server writes the data , how does it enforces ACID property ,  why does  it use a LOG file ? what is write a head? What is a Dirty page ?

Are you looking answers for the above questions , then you don’t need to worry because you came to right place

Before answering the above questions , you need to know what is logical write and physical write

What is Logical write?

When the page is modified in the Buffer cache it is called a Logical write

What is Physical write?

When a page is written from a Buffer cache to a physical disk it is called a Physical write

How it works

when a user runs an update query after it is parsed successfully the query engine finds the efficient way to access the data by comparing the different execution plans, then these keys are passed to the  storage engine which finds the exact pages in the file and brings these pages into the buffer cache(Physical read) , once the pages are in the buffer cache , the data inside the page/s will be updated in the buffer cache(logical write), once these pages inside the buffer cache are updated the data is not directly written into the database file , first the updated pages are marked as dirty pages, a dirty page can under go any number of logical writes and for each logical write the logrecord is sent to the log cache as a log record , the log record is then inserted into the log file , after log record written into the disk(log file) the dirty page is then moved from buffer cache into the disk(data file) this is called flushing the page. SQL Server uses a technique known as write-ahead logging that prevents writing a dirty page before the associated log record is written to disk(data file)

When the buffer manager writes a page, it searches for adjacent dirty pages that can be included in a single gather-write operation. Adjacent pages have consecutive page IDs and are from the same file; the pages do not have to be contiguous in memory. The search continues both forward and backward until one of the following events occurs:

• A clean page is found.
• 32 pages have been found.
• A dirty page is found whose log sequence number (LSN) has not yet been flushed in the log.
• A page is found that cannot be immediately latched.

  In this way, the entire set of pages can be written to disk with a single gather-write operation.