Friday, February 21, 2014

What is Virtual Log File, Reduce VLFs, Too many VLFs

Virtual Log Files in SQL Server

The transaction log file is used to guarantee the data integrity of the database and for data recovery. It is a reusable circular file which captures transaction details to recover the database incase of disaster, each transaction log file is logically divided into segments called virtual log files. VLF’s are created by SQL Server internally when a transaction log file is created / altered to manage the transaction log space efficiently (ie., reuse of VLF’ space).

SQL Server writes transaction log sequentially that means it writes into VLF’s one by one, after reaching to end it starts from beginning and searches for inactive VLF and start writing it.

Active VLF – The VLF’s that are currently using by SQL Server, it contains the transactions that are completed but are not written to disk.

InActive VLF – The VLF’s that are not currently using by SQL Server, once the log records are being written to data file then that VLF become inactive and come to reusable state. Whenever Log Backup happened the VLF file becomes inactive.
If LDF size is <64 MB then 4 VLF’s are created, if LDF File size is between 64 MB and 1 GB then 8 VLF’s are created if LDF size is >1 GB then 16 VLF’s are created.

How many VLF’s are currently exists on the LOG file
Dbcc loginfo -- This command displays the rows, each row indicates one VLF.

 Let see the example how many VLF’s are created at the time of Database Creation.
CREATE DATABASE [VLFTest] ON PRIMARY
( NAME = N'VLFTest', FILENAME = N'C:\VLFTest.mdf' , SIZE = 100MB , FILEGROWTH = 100MB )
LOG ON ( NAME = N'VLFTest_log', FILENAME = N'C:\VLFTest_log.ldf' , SIZE = 50MB , FILEGROWTH = 10%)

 use VLFTest
dbcc loginfo -- It displays 4 files

Use master
drop database [VLFTest]

CREATE DATABASE [VLFTest] ON PRIMARY
( NAME = N'VLFTest', FILENAME = N'C:\VLFTest.mdf' , SIZE = 100MB , FILEGROWTH = 100MB )
LOG ON ( NAME = N'VLFTest_log', FILENAME = N'C:\VLFTest_log.ldf' , SIZE = 65MB , FILEGROWTH = 10%)

use VLFTest
dbcc loginfo -- It displays 8 files

Use master
drop database [VLFTest]
CREATE DATABASE [VLFTest] ON PRIMARY
( NAME = N'VLFTest', FILENAME = N'C:\VLFTest.mdf' , SIZE = 100MB , FILEGROWTH = 100MB )
 LOG ON ( NAME = N'VLFTest_log', FILENAME = N'C:\VLFTest_log.ldf' , SIZE = 2GB , FILEGROWTH = 10%)

use VLFTest
dbcc loginfo -- It displays 16 files

Use master
drop database [VLFTest]

Let see the example how too many VLF’s are created at the time of Database Alteration.
Points to remember:
1. Too many VLF’s are created if there is Auto Growth value is less
2. We need to perform capacity planning initially to avoid creating too many VLF’s
3. Too many VLF’s can cause impact in crash recovery process, It will take more time in discovery process and more time while doing crash recovery or attaching database from disk.

Demo:
If Autogrow property is wrongly set it majorly impacts the VLFs.
CREATE DATABASE [VLFTest] ON PRIMARY
( NAME = N'VLFTest', FILENAME = N'C:\VLFTest.mdf' , SIZE = 100MB , FILEGROWTH = 100MB ) LOG ON
( NAME = N'VLFTest_log', FILENAME = N'C:\VLFTest_log.ldf' , SIZE = 64MB , FILEGROWTH = 10%)

use VLFTest
dbcc loginfo -- Intially 4 files are created for size 64 MB, If you create a log file with 64Mb size then 4 VLF’s will get created.

Use VLFTest
go
sp_helpfile
As Autogrowth is set as 10% So the file size is altered to 71 Mb,
ALTER DATABASE VLFTest MODIFY FILE ( NAME = N'VLFTest_log', SIZE = 71MB )
use VLFTest
dbcc loginfo -- Here it created 8 files as the file size is less than 1 GB so it created 8, so far everything seems good.
We will run Alter command one more time with 77 MB
ALTER DATABASE VLFTest MODIFY FILE ( NAME = N'VLFTest_log', SIZE = 77MB )

use VLFTest
dbcc loginfo -- Now the VLF count is increased to 12, If autogrowth happened again the VLF’s count will get increased abnormally. Here for every 10% autogrowth 4 VLfs are created, so we need to be very careful in deciding the autogrowth factor.

How to reduce VLF’s

Using the command verify the log used space of VLFTest Database
DBCC SQLPerf(Logspace) -- If the Log Space Used(%) is very less it means many VLFs are inactive, we can decrease the VLF count by using the below command,

NOTE – But it is a blocking operation you should take downtime if you do this in production.
Use VLFTest
dbcc shrinkfile(VLFTest_log,truncateonly) -- This command truncates all the inactive VLFs

use VLFTest
dbcc loginfo

TruncateOnly – Truncate removes the inactive virtual log files, even after truncating the physical size of log remains same and it won’t break the LSN chain.

No comments: