Concept - SQL SERVER T-LOG ARCHITECTURE : MYSQLDOG

WAL(Write Ahead Logging):

• Before committing in mdf each transaction must write an entry in the log file called Write Ahead Logging(WAL).
• The transaction not reaches mdf directly.

Transaction Log Architecture :

• SQL Server use Log Sequential number(LSN) in discovering the transaction.Every transaction that comes to log file will assign with a LSN number that will be unique.
• Roll back And Roll forward would be done automatically using these Log Sequential numbers only.

Log file Characterised into 2 Parts.........................

• Active portion (or) Physical log
• Inactive portion (or) Virtual log

Active log portion(physical log) :

The user performs transactions at That Time it will have 3 Types of actions.

1. Transaction still running.
2. Committed in log file and wait for checkpoint.
3. Failed in the middle.

• All of the above type 3 transactions will be in the active state of the log file.
• When the checkpoint executes compromised transactions, make a duplicate copy in the active part and move to mdf.

Inactive log portion(Virtual log) :

• Virtual Log part used to take the backup of the registry.
• SQL Server Contains a fully committed transaction in this step.
• we take a log backup,At that part it copies the inactive part and shortern that Part.

We have 2 types of backup for log portions :

1. The log backup takes the backup of the inactive log portion.
2. Full backup takes mdf backup and active log portion.

• When a virtual record is filled up it goes to the next virtual record.
• The inactive part is divided into more virtual records. We have a property called log reuse.
• The size or number of virtual log files can not be configured or configured by administrators.
• Transaction log is a cyclic process of writing write log to the SQL Server virtual log file.
• If it can not grow any more, it will throw a "Transaction log for the database is full and the transaction will fail".
• This portion that we call as a virtual server of log.sql does not use these registers so they call as inactive virtual registers.
• After shortern this space will be released. The backup will not be an active part.
• If all of the virtual log files are filled, the inactive part will grow larger and create more virtual records until we have allocated log space.
• The only way to clear the inactive virtual registry is to take the log backup that truncates the logs.
• Copy backup copy of the inactive part to a file and truncate the data record.the same space can be used several times called record reuse concept.
• The Log Sequence Number (LSN) of this first log record is known as the Minimum Recovery LSN (Minimum LSN).
• The database engine chooses the size of virtual log files dynamically while creating or extending log files.