Transaction Log and VLF

Introductions

Transaction log is used for data recovery purpose of the database. A proper understanding of transaction log is very important to managing the transaction log.

Point in focus

In this article I am trying focus on the points are mentioned bellow.

1.    The Physical architecture

2.    What's the Problem

3.    Finding the numbers of VLF in a Database

4.    How we fix it

The Physical architecture

The transaction log in a database maps over one or more physical files. Internally a transaction log consists of multiple blocks, called virtual log files. Every time a transaction log grows, it is done by adding additional virtual log files. The Database Engine maintains the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment.

Note that, the database administrator cannot configure the size and the number of the virtual log file.

If we make a look of the transaction log file it is a wrap-around file. To understand it properly, in our example we are taking a database contains one physical log file which is divided into four virtual log file. When our database is created the logical log file begins at the start point of the physical log file.  If needed the new log are added at the end of the logical log and expand towards the end of the physical log.  Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.

The log file content is logically categorized into three different categories:

• Used portion: This contains log records that are written to the transaction log but can be removed
• Active portion: This is the part of the transaction log which is defined by the oldest active transaction. This information cannot be removed while the transaction is still active
• Unused portion: This is empty space

What's the Problem

When the transaction log file grows very repeatedly, it generates the thousands of virtual log files. The multiple virtual file may be scattered over the disks resulting in a seek every time a write crosses from one file to the next.

At the time the log is growing, each Chunk that is added is divided into Virtual Log File. Taking an example,  if the 10 MB of Log that is extended to the 50 MB, so the 40 MB chunk is added and it divided into 4 Virtual Log File.

Here demonstrating a simple table to understand it.

Chunks	VLF
Chunks <64MB	4
Chunks>=64MB AND <=1GB	8
Chunks>1GB	16

Finding the numbers of VLF in a Database

 Ideally any count of VLF less than 50 is considered as good number, anything beyond 50 might affect the performance.

The bellow scripts helps to find the number of VLF in our entire database.

CREATE TABLE #MyTracker

       (FileID          INT,

            FileSize    BIGINT,

            StartOffset BIGINT,

            FSeqNo            BIGINT,

            [Status]    BIGINT,

            Parity            BIGINT,

            CreateLSN   NUMERIC(38)

);

CREATE TABLE #MyResult

       (Database_Name  sysname,

        VLF_count      INT,

        Log_File_count INT

);

EXEC sp_MSforeachdb N'Use [?];

                      INSERT INTO #MyTracker

                      EXEC sp_executeSQL N''DBCC LogInfo(?)'';

                      INSERT INTO #MyResult

                      SELECT DB_Name(), 
                             Count(*), 
                             Count(Distinct FileID)

                      FROM   #MyTracker;

                      TRUNCATE TABLE #MyTracker;'

SELECT   *

FROM     #MyResult

ORDER BY VLF_count DESC;

Result set are mentioned bellow.

Database_Name              VLF_count          Log_File_count

msdb                                     10                            1

master                                    5                             1

Test                                       4                              1

model                                    3                              1

tempdb                                  2                              1

How we fix it

If you come across a database with more than 50-100 VLFs then we should look to take action in order to increase the transaction log throughput. Here I am taking the example of "AdventureWorks" Database.

Step -1 [ Backup the Transaction Log ]

BACKUP LOG [AdventureWorks]

TO  DISK = N'E:\db.bak'

GO

Step-2 [ Shrink the Transaction Log ]

USE [AdventureWorks]

GO

DBCC SHRINKFILE ('AdventureWorks_Log', TRUNCATEONLY)

GO

Step-3 [ Alter the Database to modify the size of transaction

            log and configure Auto growth ]

USE [master]

GO

ALTER DATABASE [AdventureWorks]

      MODIFY FILE ( NAME = N'AdventureWorks_Log',

                    SIZE = 1024000KB,

                    FILEGROWTH = 1024000KB)

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS