Let us take a close look at the active VLF in an SQL server. At Bobcares our MSSQL support services can give you a detailed note on the whole process.

Role of Active VLF in SQL server

It is crucial to remember that the VLF, rather than a single log record or log block, is the smallest unit of truncation in the log file. The whole VLF is regarded as active and cannot be shortened if there is just one log entry in a VLF that is still a part of the active log.

A VLF can, in general, be in either an active or an inactive physical state. However, based on the many ‘behaviors’ that a VLF could exhibit, we can distinguish four logical states:

1. Active: The VLF in this state is active because at least one of its log records is a component of the active log. This makes it necessary for rollback or other operations.
2. Recoverable: A VLF is recoverable when it is dormant but not backed up or truncated. Note that the space is not reusable.
3. Reusable: A VLF is inactive in this situation. Space can be utilized again since it has been backed up or truncated.
4. Unused: A VLF in this condition has never had a log entry made in it, making it inactive.

VLF and SQL management

A virtual Log File is referred to as VLF. One or more virtual log files make up the log file in a SQL Server transaction.

The transaction log backup and database restore processes can both be slowed down by having too many virtual log files. There have been instances where inserting, updating, or deleting data from the database has been slowed down.

The following script, when executed, will show the database’s active and inactive VLF in SQL.

SELECT [name] AS 'Database Name',
COUNT(li.database_id) AS 'VLF Count',
SUM(li.vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(li.vlf_active AS INT)) AS 'Active VLF',
SUM(li.vlf_active*li.vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(li.database_id)-SUM(CAST(li.vlf_active AS INT)) AS 'Inactive VLF',
SUM(li.vlf_size_mb)-SUM(li.vlf_active*li.vlf_size_mb) AS 'Inactive VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) li
GROUP BY [name]
ORDER BY COUNT(li.database_id) DESC;

Using the following command, we may quickly decrease the log file if we see a large number of idle VLF. The active VLF in SQL server allows us to have more logfile process control.

For instance, we can execute the following query to reduce the size of the database:

DBCC SHRINKFILE (N'WWI_Log', 10)

After executing the query, you may rerun the first query to confirm that the majority of the database’s inactive VLF has been eliminated.

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude we have now learned how to active VLF in SQL server. We have gone through all of the natures of the VLF and how to activate it in SQL with the support of our MSSQL support services at Bobcares.