The transaction log in a database maps over one or more physical files.
Conceptually, the log file is a string of log records. Physically, the sequence
of log records is stored efficiently in the set of physical files that implement
the transaction log. The SQL Server Database Engine divides each physical log
file internally into a number of virtual log files.
Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. With few examples we will look at the number of VLFs created for a given database. In the first example I create a database with the log file size of 512 KB (minimum possible). I will be using the same script for creation of the everytime with constantly changing the log file size
USEmaster
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name ='VLF')
DROP DATABASE VLF;
GO
CREATE DATABASE VLF
ON PRIMARY
( NAME='VLF',
FILENAME= 'D:\VLFP.mdf',
SIZE=4MB,
FILEGROWTH=1MB)
LOG ON
( NAME='VLFL',
FILENAME='D:\VLFL.ldf',
SIZE=1025MB,
FILEGROWTH=1MB);
GO
Now that the database is created we will see how many virtual log files are created. This can be checked using the DBCC LOGINFO command as shown below. For a 512KB of log file there are 2 VLF files created with FSeqNo as 41 and 0. FseqNo when 0 means that the VLF is unused since creation. On the other hand Status 0 means VLF is not being used currently, but may have been used previously. In our case VLF with FseqNo has status 2, which means this is an active VLF (holding active transactions.
The question now remains is what is the CreateLSN field for ? This field has 0 for all the VLFs that were created when the database was created (which exist from the beginning). In our case both existed since the database creation time.
USE VLF
GO
DBCC LOGINFO
Now we will increase the database log file size to 1MB and re-execute the above database creation statement. Once the database has been created, lets see the VLFs using DBCC LOGINFO. As can be seen this time 4 VLFs has been created. Till we reach the 64MB mark there will be only 4 VLFs created
USE VLF
GO
DBCC LOGINFO
Increasing the size of the database log file to 65MB will created 8 VLFs and going beyond 1GB will create 16 as visibile from the snap shot below respectively.
USE VLF
GO
DBCC LOGINFO
This is how a typical log file should be logically organized into VLFs. The diagram has been taken from BOL
Lets not talk about checkpoint. A checkpoint performs the following processes in the database:
Let me show you some of this using a small example by creating a table called CheckPt
CREATE TABLE CheckPt(a int);
Before I do any other operation I want to fire a checkpoint in order to clear the existing log
CHECKPOINT
Now we will insert a single row in this table, however we would not commit the transaction.
BEGIN TRANSACTION T1
INSERT INTO Checkpt VALUES (10);
Lets take a look at the transaction log using the command DBCC LOG (databasename, 4). Couple of things to focus here is allocation in IAM, blocking of PFS (Page Free Space) and LOP_INSERT_ROWS (row 20), where we do the actual insert.
DBCC LOG(VLF,4)
Interesting thing is to look at the Log_Record column for this record LOP_INSERT_ROWS. This column is not there in the snapshot, however I have pasted the content for it below. At the offset 51-52 is the page no where the row has been inserted (in this case 4F (hex) = 79 decimal. This is confirmed below using the DBCC IND command as shown below
0x00003E00290000007100000001000200E7020000000002014F000000010000001D00000029000000710000000F00000100000D000000000100000000410003000B00000014000000100008000A000000010000000101000C0000E7A4787D00000102000402030004
DBCC IND(VLF, Checkpt, 1)
Now we fire the check point again. We know that one of the transactions is active (Insert).
CHECKPOINT
Lets look at the LOG using the DBCC LOG
DBCCLOG(VLF,4)
In the snapshot in the highlighted 3 rows you can see the row with LOP_XACT_CKPT is for the transation with LSN 00000029:00000071:0001 (row 1). This is the reason that the Log Record column has this LSN embedded in it. I leave it to you to find out
Finally I will rollback this transaction to show you how the undo information gets added to the log. Currently we have only 25 rows in the table. After rollback we will have few more as can be seen in the snapshot below. Look at the operation LOP_DELETE_ROWS which rollsback the insert statement.
ROLLBACKTRAN T1
DBCCLOG(VLF, 4)
Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. With few examples we will look at the number of VLFs created for a given database. In the first example I create a database with the log file size of 512 KB (minimum possible). I will be using the same script for creation of the everytime with constantly changing the log file size
USEmaster
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name ='VLF')
DROP DATABASE VLF;
GO
CREATE DATABASE VLF
ON PRIMARY
( NAME='VLF',
FILENAME= 'D:\VLFP.mdf',
SIZE=4MB,
FILEGROWTH=1MB)
LOG ON
( NAME='VLFL',
FILENAME='D:\VLFL.ldf',
SIZE=1025MB,
FILEGROWTH=1MB);
GO
Now that the database is created we will see how many virtual log files are created. This can be checked using the DBCC LOGINFO command as shown below. For a 512KB of log file there are 2 VLF files created with FSeqNo as 41 and 0. FseqNo when 0 means that the VLF is unused since creation. On the other hand Status 0 means VLF is not being used currently, but may have been used previously. In our case VLF with FseqNo has status 2, which means this is an active VLF (holding active transactions.
The question now remains is what is the CreateLSN field for ? This field has 0 for all the VLFs that were created when the database was created (which exist from the beginning). In our case both existed since the database creation time.
USE VLF
GO
DBCC LOGINFO
Now we will increase the database log file size to 1MB and re-execute the above database creation statement. Once the database has been created, lets see the VLFs using DBCC LOGINFO. As can be seen this time 4 VLFs has been created. Till we reach the 64MB mark there will be only 4 VLFs created
USE VLF
GO
DBCC LOGINFO
Increasing the size of the database log file to 65MB will created 8 VLFs and going beyond 1GB will create 16 as visibile from the snap shot below respectively.
USE VLF
GO
DBCC LOGINFO
This is how a typical log file should be logically organized into VLFs. The diagram has been taken from BOL
Lets not talk about checkpoint. A checkpoint performs the following processes in the database:
- Writes a record to the log file, marking the start of the checkpoint.
- Stores information recorded for the checkpoint in a chain of checkpoint log records.
- One piece of information recorded in the checkpoint is the log sequence
number (LSN) of the first log record that must be present for a successful
database-wide rollback. This LSN is called the Minimum Recovery LSN (MinLSN).
The MinLSN is the minimum of the:
- LSN of the start of the checkpoint.
- LSN of the start of the oldest active transaction.
- LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.
- The checkpoint records also contain a list of all the active transactions that have modified the database.
- If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.
- Writes all dirty log and data pages to disk.
- Writes a record marking the end of the checkpoint to the log file.
- Writes the LSN of the start of this chain to the database boot page.
Let me show you some of this using a small example by creating a table called CheckPt
CREATE TABLE CheckPt(a int);
Before I do any other operation I want to fire a checkpoint in order to clear the existing log
CHECKPOINT
Now we will insert a single row in this table, however we would not commit the transaction.
BEGIN TRANSACTION T1
INSERT INTO Checkpt VALUES (10);
Lets take a look at the transaction log using the command DBCC LOG (databasename, 4). Couple of things to focus here is allocation in IAM, blocking of PFS (Page Free Space) and LOP_INSERT_ROWS (row 20), where we do the actual insert.
DBCC LOG(VLF,4)
Interesting thing is to look at the Log_Record column for this record LOP_INSERT_ROWS. This column is not there in the snapshot, however I have pasted the content for it below. At the offset 51-52 is the page no where the row has been inserted (in this case 4F (hex) = 79 decimal. This is confirmed below using the DBCC IND command as shown below
0x00003E00290000007100000001000200E7020000000002014F000000010000001D00000029000000710000000F00000100000D000000000100000000410003000B00000014000000100008000A000000010000000101000C0000E7A4787D00000102000402030004
DBCC IND(VLF, Checkpt, 1)
Now we fire the check point again. We know that one of the transactions is active (Insert).
CHECKPOINT
Lets look at the LOG using the DBCC LOG
DBCCLOG(VLF,4)
In the snapshot in the highlighted 3 rows you can see the row with LOP_XACT_CKPT is for the transation with LSN 00000029:00000071:0001 (row 1). This is the reason that the Log Record column has this LSN embedded in it. I leave it to you to find out
Finally I will rollback this transaction to show you how the undo information gets added to the log. Currently we have only 25 rows in the table. After rollback we will have few more as can be seen in the snapshot below. Look at the operation LOP_DELETE_ROWS which rollsback the insert statement.
ROLLBACKTRAN T1
DBCCLOG(VLF, 4)
