This month's discount Comprehensive Database Performance Health Check · Testimonials
SQLAuthority with Pinal Dave
Home
  • Consulting
    • Health Check
  • Free Videos
  • All Articles
    • AI
    • SQL Performance
    • SQL Tips and Tricks
    • Interview Questions and Answers
    • SQL Puzzle
    • SQL Video
    • SQLAuthority News
    • Personal
  • Books
  • Consulting
Gouache painting of a bead tray with tiny cells beside a tray of four large compartments, one vermilion

VLF Growth Rules: How Many Virtual Log Files One Growth Adds

February 28, 2023
Pinal Dave
SQL Tips and Tricks
SQL Server 2022, Transaction Log, VLF

The VLF growth rules say how many virtual log files one log growth adds: one up to 64 MB, eight up to 1 GB, sixteen above. This post measures them on a demo database, shows a log with 68 VLFs from 1 MB steps, and repairs it by shrinking and growing once.

Read More
Gouache painting of a row of neat stacked logs, a scatter of old grey rounds and a vermilion sawhorse between them

Active and Inactive VLFs: List Them for Every Database

March 12, 2020
Pinal Dave
SQL Performance
SQL DMV, SQL Scripts, Transaction Log, VLF

To list active and inactive VLFs for every database, count the rows of sys.dm_db_log_info by vlf_active and read the log reuse wait next to them. This post builds a demo log with 24 VLFs, shows how an open transaction and small autogrowth steps add more, and fixes the count with one shrink and one regrow.

Read More
Gouache painting of boats resting on tidal sand with one vermilion boat waiting for the water

SQL Server Cluster Resource in Online Pending Too Long

January 30, 2019
Pinal Dave
SQL Tips and Tricks
SQL High Availability, SQL Log, SQL Server Cluster, VLF

A SQL Server cluster resource that stays in Online Pending for minutes can be waiting for database recovery. This post reads the cluster events and log lines of one case, finds an 8 minute gap, and traces it to a huge number of virtual log files that a log backup and a shrink fixed.

Read More
Gouache painting of two oversized pumpkins sitting on a small wooden cart, one cart wheel painted vermilion

Only Two VLFs: Cannot Shrink the Log File Any Further

December 19, 2018
Pinal Dave
SQL Tips and Tricks
Shrinking Database, SQL Error Messages, SQL Scripts, Transaction Log, VLF

A log file with only two VLFs cannot shrink any further, and DBCC SHRINKFILE tells you so. The message says the number of logical log files cannot be fewer than 2. Here is the script that reproduces it and the DBCC LOGINFO output that explains it.

Read More
Gouache painting of a tall wooden spool wound with cream thread, one short stretch of the thread tied in a vermilion knot

Backup Detected Log Corruption: Context Is Bad Middle Sector

March 31, 2018
Pinal Dave
SQL Tips and Tricks
Database Corruption, SQL Backup and Restore, SQL Error Messages, Transaction Log, VLF

Backup detected log corruption in a client’s database, and every full backup failed at exactly 60 percent. Instead of rebuilding the log, we checked the VLFs and found a log waiting on REPLICATION. Removing the leftover replication fixed the backup.

Read More
Separate glass chambers show log sections of different sizes and activity

How to Get VLF Count and Size in SQL Server? – Interview Question of the Week #161

February 18, 2018
Pinal Dave
SQL Interview Questions and Answers
SQL DMV, SQL Scripts, SQL Server, Transaction Log, VLF

How do you get VLF Count and size in SQL Server? Newer versions add a Dynamic Management Function for it. Here is the query, plus links to my older methods.

Read More
SQL SERVER - PowerShell to Count Number of VLFs in SQL Server

SQL SERVER – PowerShell to Count Number of VLFs in SQL Server

June 23, 2015
Pinal Dave
SQL Tips and Tricks
PowerShell, SQL Scripts, SQL Server, VLF

Too many VLFs can hurt performance. Here is a simple PowerShell script that counts the VLFs in every database on one or many SQL Server instances.

Read More
1 2 Next
  • Books
  • Testimonials
  • Privacy Policy

© 2006 – 2026 All rights reserved. pinal @ SQLAuthority.com