Home » SQL Server » Page 21

SQL Server

Transaction log file VLF management

One of the important things I would recommend is not having transaction log VLFs be too large, with 500MB being recommended maximum, so that it is not to interrupt the clearing/truncating of the log.The Reason for this is that transaction log could only clear a VLF when TLog Backup is taking place only when it’s completely inactive. So that means larger the VLF then bigger VLF to be cleared and for it to inactive.

Other important thing I would recommend is not having too many transaction log VLFs. Having an excessive number of VLFs can negatively impact all transaction log related activities and you may even see degradation in performance when transaction log backups occur. … Read the rest