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

SQL Server Post Build Configurations

Right from when the environment is built, it is important to setup few parameters so that we do not face issues later when project team starts to use the environment. Since it is a specific documentation which targets the server builds where SQL Server is installed, it will have all the information related to the best practices to be followed right after the server build and SQL Server installation are performed. We have faced many issues in past where due to these small settings or configurations, we could not get system as it is after an outage. Backup availability:  For any database and server, it is important to have backups in place. Application backups and Server backup separately is required to bring up any service to a point in time in case of recovery from an outage.  Resource setting:  If the resources allocated to the system are not properly been configured right after the installation of the software and server builds, it might cause issues later w...
Post a Comment
Read more

Data Compression - Row & Page ( Sketch notes)

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

Best Practices to be followed for SQL Server

1. Memory Capping SQL Server is an application that uses as much memory as is provided to it. Setting memory too high can cause other applications to compete with SQL on the OS and setting memory too low can cause serious problems like memory pressure and performance issues. It is important to provide at least 4 GB memory for OS use, if you are running only SQL Server as an application on the server.  2. Data File Locations SQL Server accesses data and log files with very different I/O patterns. Data file access is mostly random whilst transaction log file access is sequential. Spinning disk storage requires re-positioning of the disk head for random read and write access. Sequential data is therefore more efficient than random data access. Separating files that have different access patterns helps to minimize disk head movements, and thus optimizes storage performance.  3. Auto growth Auto-growth of the database files should be set in MBs as it will al...
Post a Comment
Read more