SQL Server data mainly in 2 types of files,
Data file stores actual data with .mdf extension. It stores permanent data. Log files stores modified recorded information with .ldf extension. We have another file called secondary data file .ndf file extension. A database may or may not have these secondary data files.
A Transaction is a set of T-SQL statements that read and wt=rite data into the database. There are 2 types of Transactions.
Implicit Transaction: Implicit transactions are these without begin transaction
Explicit Transaction: Explicit transactions are started using begin transaction and are controlled by using T-SQL Command commit transaction (or) Rollback transaction. Any transaction should process ACID properties then only changes more from LDF to MDF.
Atomicity means all the statements of a transaction must complete successfully or rolled back completely means either all (or) None to updated.
Consistency means a transaction never leaves database in half-finished state, whenever any change happen on parent object it should automatically reflect on dependent child object to ensure that database in a consistent state.
Isolation keeps changes of incomplete transactions independent from one another.
Once a transaction is committed, it must be permanent even if there is a system failure means it cannot be rolled back.
SQL Server stores data mainly in two types of files.
MDF – It contains Permanent Data
LDF – LDF contains whatever changes we are performing on the database all the change-related information will be recorded in the LDF file.
Buffer is a ram to perform modifications on a copy of the permanent page. Once it commits record the information will record in LDF and the same changes apply on MDF when checkpoint runs.
SQL Server will not allow doing modifications directly to MDF. SQL Server will make a copy of pages from MDF to buffer. Once the transaction is fully committed it records the information that what type of data he is inserting, Number of pages affecting, what he is performing all these change-related information will record in the same sequential way in Log File. Pages will stay some time in the buffer for faster retrieval read and write operations from the buffer will be very faster comparing to operations from MDF Data. Using recorded information whenever checkpoint runs on the log file. It applies the same changes permanently on the MDF file.
The checkpoint is an internal mechanism performs regular based on the number of transaction (or) number of pages there is no time interval for running this. Checkpoint scans log file, checks how many committed transactions are there, how many failed and how many still running committed transactions more to MDF, failed transactions will be rolled back. Currently, running transactions will not be touched by the checkpoint.
Whenever SQL server restarts checkpoint verifies pending transactions before the restart, the SQL server will perform the recovery process. This process will analyze what is the state of the log file and perform 2 properties.
Once this recovery process complete then only users can able to access the database.
Modified pages will be in buffer some time, whenever the buffer is about to fill with these modified pages, Lazy writer is another internal mechanism usually in sleep mode invokes and clear the buffer pages.
It uses the LRU algorithm in the clearing, LRU stands for L. Recently used pages, on page header of the page there will e reference counter means how many times this page is used, based on counter least used pages will be deleted in the buffer.
Pages commit in the log file and waiting for checkpoint to more MDF, those called dirty pages.
#Ref. Ram Mohan & Rama Krishna notes