Skip to main content

Transaction Log

When crash happens, the recovery of uncommitted and committed transaction is done by using transaction log.

 Transaction Log Architecture

 There are basically two types of transaction log architectures:

 Physical: In physical architecture virtual log files are maintained for different transactions. Virtual log files are maintained for easy management.

 Logical: In logical architecture LSN is maintained.

 Recovery always starts from last uncommitted transaction or checkpoint whichever is less.

 Last uncommitted transaction is known as MIN LSN

 Checkpoint

 Checkpoint occurs in two conditions:

 1. Automatic : It will occur automatically in either of the two following cases(whichever is lower)

  • Recovery Interval
  • 70% log is full

2. When an activity happens in database : It will occur if any of the following case happens

  • When we write explicitly checkpoint in query window
  • When new db is created
  • When backup is created
  • When SQL server shuts down
The recovery is not measured in terms of time, it is measured in terms of no. of transactions between 2 checkpoints.

Virtual Log File

Virtual log file has two portions:
  • Active : Starting from MIN LSN to last written record
  • Inactive : That portion which does not has uncommitted transaction e.g. before active area
Size of the databases are defined for:
  • Initial e.g. 64 MB
  • Max e.g. 2 GB
  • Autogrowth: which can be done in 2 ways: By % or by Size
Properties of transaction log
  • Forward- It moves forward until it gets full. This property is used in Simple recovery model
  • Circular- When forward gets full, it moves to the first virtual log to make it circular
Advantage of Circular transaction log

Size does not have to be increased for transaction log. The virtual log file which is having all committed transactions will be truncated.

dbcc loginfo: It tells the information about database transaction log.
In every transaction, the no. of statements will have different LSN No. 

Comments

Post a Comment

Popular posts from this blog

Data Compression - Row & Page ( Sketch notes)

Just some notes on compression... something helpful for a beginner.
Post a Comment
Read more

SQL Server Upgrade Issues

Pre-Requisites We usually think of SQL Server simply as a database engine, while it is a data platform which supports data warehousing, reporting, data integration and data analytics. An in-depth planning is required when upgrading from one version of SQL Server to a newer version along with the features that SQL Server offers. Upgrading these extensive number of features can be remarkably complicated and time-consuming than just upgrading the database engine alone.   Below mentioned are few of the “lessons learned” that help reduce risks of data losses etc.: 1.        Application compatibility: Before planning for upgrade activity, make sure that you discuss the compatibility of SQL Server and the associated application with your application vendor.  2.        Preparation for migration: In case your upgrade activity fails due to some reason and there is no solution possible other than migration,...
Post a Comment
Read more

Backup & Recovery

In SQL Server, there are three types of backups: Full Backup: Full backup means it will take the backup of the total .mdf file. Differential Backup : Differential backup means it will take the backup of the extent's (8 pages= 1 extent) data which has been changed since last full backup. It is cumulative in nature and so is also called cumulative backup. cumulative means it will take backup from last full backup. T-log Backup: T-log backup means it will backup the transaction log file. It is not cumulative in nature. Now, there are three Recovery models in SQL server which are defined as: Simple Recovery Model: In this recovery model, we can only have full backup and differential backup. If the data needs to be restored for the database having simple recovery model then last full backup will be restored first and after that the last differential backup. There will be no point in time recovery in this case. Full Recovery Model: In this recovery model, we...
Post a Comment
Read more