Remove Excessive VLFs

VLF is SQL server internal logical structures of log files. Too many VLFs can cause major performance bottle neck. Excessive VLF can cause slow restores, excessive locking, affects HA (high availability) groups and reduces transaction log backup performance. Detecting and resolving the VLF issue is very simple by pre-sizing, using MB or GB interval for sizing.

Any transaction written to the log file has a start time and an end time, and during this written process if the log is backed up then the running transaction is not going to truncate. Once transaction is done, the next log backup will truncate all the committed transaction, and the space used by the VLF would be empty for reuse.

What happens if the transaction is bigger than the log file size? The bigger the transaction size the more VLFs would be created.

pic1

So think if a transaction size is 5 GB and your logfile size is set to 1 GB, it will create 64 VLF (4×16).

To see any excessive VLF and to remove them, follow the below steps:

Step 1: Run DBCC loginfo ()

pic2

Step 2: Take transaction log backup to truncate all the commented transactions.

Step 3: Shrink the log file DBCC shrinkfile (logfile logical name)

pic3

If you see the used pages and estimated pages has a huge gap between the numbers, it means active transactions exist.

select log_reuse_wait_desc

from sys.sysdatabases

  where database_id = 2

(get the database_ID from select * from sys.sysdatabases where name =’your database’)

Then Run the DBCC loginfo () again to check the current status. If you see what is in the below picture, a log status of 2 means it is an active transaction. And you can not shrink the previous ones (status 0) because they have already been committed, at this point you might need to run transaction log backup again. And shrink the log file again.

pic4

 

-By : MD Ullah

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s