Transaction Log and VLF

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.

Transaction log and performance

The transaction log of SQL server is a factor for performance matter.

Here I provide you some collection that matter the performance in case of transaction log optimizations.

1) Try to dedicate the transaction log portion of your database to its own phyiscal disk. In high volume OLTP system, isolating the transaction log can allow the disk head to be ready for the next write by not having other files contend for the physical disk resource. If your database already exists, the best way to "move" your transaction log is to detach your database and then reattach it

a. sp_attach_db

sp_attach_db [ @dbname = ] 'dbname' 

             , [ @filename1 = ] 'filename_n' [ ,...16 ]

b. sp_detach_db

sp_detach_db [ @dbname= ] 'database_name'

              [ , [ @skipchecks= ] 'skipchecks' ]

              [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

2) Defrag the disk(s) on which your transaction logs reside. This will get rid of external fragmentation of the transaction log - better known as disk file fragmentation. This will require that your server be taken offline but if your files have had a lot of auto growth and/or they reside on a disk with a lot of other files that have been modified, then all of your files (incl. the transaction log file) are likely to be interleaved and fragmented.

3) Create only ONE transaction log file. Even though you can create multiple transaction log files, you only need one... SQL Server DOES not "stripe" across multiple transaction log files. Instead, SQL Server uses the transaction log files sequentially. While this might sound bad - it's not. If you want to get better performance out of the transaction log, place it on faster disks and/or a disk (RAID) configuration.

4) Not only should you try to isolate the transaction log to its own physical disk but you should make sure that the logical/physical disk configuration is as efficient as possible. Try to use an isolated RAID 1 mirroring set if you don't need significant capacity. If you need a greater capacity OR you want better performance, consider a combination of RAID 0 and RAID 1 (either RAID 0 + 1 or RAID 1 + 0). While RAID 0 + 1 can often offer better performance, RAID 1 + 0 offers better reliability. If you're new to RAID and are interested in learning more.

5) Don't be caught up in nothing but transaction log speed, you'll also want to make sure that your transaction log is always available as this can help you in times of disaster. Even if the data is damaged, if the transaction log is available and you have a series of backups up to and including the last transaction log then you can make a final backup of the transaction log that will represent all of the changes since your last transaction log backup. If this backup is possible (it's called backing up the "tail" of the log), then you can achieve up-to-the-minute recovery. This is only possible when the log is available. By placing the log on mirrored disks you can increase your chances of recovering data and minimize data loss.

6) Create transaction log files with a reasonable initial size. When you create a database it's ideal to PRE-ALLOCATE both your data files and your transaction log file. A little bit of capacity planning goes a long way... Now, if you think that you've got absolutely no idea how to size your transaction log you're going to need, well - here are the things that have the greatest impact:

• Type of activity - transaction processing or decision support
• Frequency of that activity - the more frequent the changes, the faster the transaction log will grow
• Recovery Model - the recovery model of the database
• Frequency of transaction log backups
• Whether or not replication is used (since the log reader relies on the transaction log)

I wish I could give you a rough idea on sizing but if you look only at database size and none of these other factors, you could end up with a transaction log that's seriously oversized or seriously undersized. I've seen recommendations of 10-25% of the size of the data and you can use that but I would also add a bit of common sense. A larger database with very frequent transaction log backups may not need a transaction log that's even 1% of the size of the data... The best way is to setup your development environment similar to that of your production environment (including backup jobs) and then see how the transaction log grows. If you have a lot of auto growth (because your guess was wrong), you can later clean up the fragmentation that has occurred and get back to a reasonable, intact, and optimal transaction log.

7) Don't let auto growth get out of control. As important as capacity planning, you're not likely to be spot-on in your estimates. I don't recommend completely turning off auto growth but in general I also don't like SQL Server 2000's default growth rate (or max size). In general, I would recommend setting the entire transaction log files attributes: initial size, growth rate AND maximum size. For the growth rate, I recommend something that can be allocated somewhat quickly and something of a fixed size. In general, I recommend a value which is less than or equal to 1GB (based on total size) but something that doesn't mean that you're going to auto grow again soon. So, for databases whose transaction logs are under 1GB then you might set auto grow to somewhere between 20 and 100 MB. For those of you who have transaction logs measured in GB, then I'd set the auto growth to 500MB or 1GB. In general, I don't like the percentage because it needs to be calculated (which I realize isn't really all that big of a deal) but the larger the file the larger the auto growth and the longer it takes.

Follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log:

1. Wait for an inactive time of day (ideally, it would be best to put the database into single user mode first) and then clear all transaction log activity through a regular transaction log backup. If you're using the simple recovery model then you don't need to do a log backup... Instead, just clear the transaction log by running a checkpoint.

 BACKUP LOG databasename TO devicename

2. Shrink the log to as small a size as possible (truncateonly)

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

3. Alter the database to modify the transaction log file to the appropriate size - in one step

ALTER DATABASE databasename

MODIFY FILE 

( 

      NAME = transactionloglogicalfilename 

    , SIZE = newtotalsize

)