SQL server, SQLServerPedia Syndication

Log file and VLF fragmentation.

In a SQL server, having multiple log files does not have any performance benefits as the log files are written sequentially. The log files can have external (disk level) fragmentation or internal (VLF, Virtual Log File) fragmentation. The external fragmentation can be solved by de-fragmenting the disk on which the log file resides on, followed by properly sizing the log files, to prevent further fragmentation.

Internal fragmentation is caused by the growing and shrinking of the log files. There is a nice article by Thomas LaRock on how to use powershell to get an output of the number of VLFs for your DBs. A basic rule of thumb that I use is, if there are more than 50 VLFs for a DB, it is time to truncate it. Using dbcc loginfo(‘db_name‘) with no_infomsgs will return one row for each VLF for db_name. To ‘de-fragment’ the VLFs, do the following:

  1.  Backup the log file (when in full mode), or issue a checkpoint (when in simple mode).
  2. Truncate the log file: DBCC SHRINKFILE (‘db_logfile_logical_name‘). Should be run in the context of the DB concerned.
  3. Size the log file appropriately. ALTER DATABASE ‘db_name‘ MODIFY FILE (‘db_log_file_logical_name‘,new_size_as_int)  

Do a DBCC loginfo again to see the reduced number of VLFs you now have.


Thinking about someting? 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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s