Monday, December 31, 2012

Too Many VLFs

Transaction Log consists of Virtual Log Files. When transaction log is increase virtual log files are crated according to following table. More info here

Log Size # of VLFs
64MB and up to 64MB 4
64MB and up to 1GB 8
larger than 1GB 16

Too many VLFs will lead to some problems like, backups will take long time, replication latency will be high. In fact, I have a bad experience, when one of the database has high replication latency and reason for that is high VLFs.

In SQL Server 2012, there is a new feature when the VLFs are more than 10,000 there will be an warning in the error log once the server startup.

During the CTP3 of Denali, this threshold was set to 1,000 but was increased at the RTM. However, Microsoft has forgot to reset the warning message. Winking smile

Here is how I recreate this.

image

I verified number of VLFs by DBCC LOGINFO(). I verified that there are no warning when log file reached the 1000 VLFs.

After reaching 10,000 I saw the error message but it says database has  more than 1000 virtual log files.

image

No comments:

Post a Comment