SQL Server splits transaction logs into chunks called Virtual Log Files (VLF) and manages them as single units. For example, SQL Server cannot truncate and reuse a VLF if it contains just a single active log record. Pay attention to the number of VLFs in the database. Too few of them will lead to very large VLFs, which make log management and truncation suboptimal. Too many small VLFs will degrade the performance of transaction log operations. Try not to exceed several hundred VLFs in production systems.
You can count the VLFs in the database with sys.dm_db_log_info data management view in SQL Server 2016 and above. In older versions of SQL Server, you can obtain the information by running DBCC LOGINFO.
Categories:
Tags:
No responses yet