How many VLF's can be bad

VLF count is only going to be a problem for recovery times if the combination of the count of VLFs and their size means the recovery process will require longer duration than your recovery-time-objective.

There is no single number of VLFs that is good or bad. Very generally, I keep each VLF under 512MB, and keep the number of VLFs well under 1,000.

In order to know the actual recovery time for a given log file, you'll need to have identical hardware setup in a test environment, and test recovery of a full log where the database has been shutdown while active transactions are running. If you have a database with hundreds of thousands of VLFs, be very scared about recovery time. Ask me how I know.

You can view current info about VLFs by looking at DBCC LOGINFO in the context of the desired database. The output from that command looks like:

╔════════════════╦════════╦═══════════╦═════════════╦════════╦════════╦════════╦═══════════╗
║ RecoveryUnitId ║ FileId ║ FileSize  ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬═══════════╬═════════════╬════════╬════════╬════════╬═══════════╣
║ 0              ║ 2      ║ 268369920 ║ 8192        ║ 34     ║ 2      ║ 64     ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 268378112   ║ 0      ║ 0      ║ 0      ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 536748032   ║ 0      ║ 0      ║ 0      ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 805117952   ║ 0      ║ 0      ║ 0      ║ 0         ║
╚════════════════╩════════╩═══════════╩═════════════╩════════╩════════╩════════╩═══════════╝

The Status column indicates the status of the given VLF, where 2 indicates the VLF has active log records and cannot be re-used until those records are backed-up/truncated (or in the case of mirroring, written to the mirror's log file). Shrinking a log file (which is not typically recommended unless you have a problem) cannot reduce the size of the file below the point occupied by the last VLF with a Status of 2. In my example above, shrinking the log file will allow me to get rid of the last 3 VLFs, however, I cannot shrink the 1st VLF since it is status 2. If the 4th VLF had a status of 2, I would not be able to shrink the logfile at all until the log had been backed-up (in full recovery) or truncated (in simple recovery).

You can see file size and growth settings using this query:

SELECT [DB Name] = d.name
    , [File Name] = mf.name
    , mf.physical_name
    , Size = mf.size * 8192E0 / 1048576
    , MaxSize = mf.max_size * 8192E0 / 1048576
    , Growth = mf.growth * 8192E0 / 1048576
    , [Recovery Model] = CASE WHEN mf.type = 1 THEN d.recovery_model_desc ELSE '' END
    , [Log Reuse Wait Reason] = CASE WHEN mf.type = 1 THEN d.log_reuse_wait_desc ELSE '' END
FROM master.sys.databases d
    INNER JOIN master.sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = DB_NAME()
ORDER BY d.name
    , mf.type
    , mf.name;

Sample output for the query above looks like:

╔══════╦══════════╦═════════════════════════╦══════╦═════════╦════════╦══════════╦═════════════╗
║ DB   ║ File     ║ physical_name           ║ Size ║ MaxSize ║ Growth ║ Recovery ║ Log Reuse   ║
║ Name ║ Name     ║                         ║      ║         ║        ║ Model    ║ Wait Reason ║
╠══════╬══════════╬═════════════════════════╬══════╬═════════╬════════╬══════════╬═════════════╣
║ Test ║ MyFile   ║ D:\Data\Test_MyFile.bak ║ 125  ║ 1000    ║ 100    ║          ║             ║
║ Test ║ Test_DB  ║ D:\Data\Test_DB.mdf     ║ 384  ║ 2048    ║ 128    ║          ║             ║
║ Test ║ Test_Log ║ D:\Logs\Test_DB.ldf     ║ 256  ║ 2048    ║ 128    ║ SIMPLE   ║ NOTHING     ║
╚══════╩══════════╩═════════════════════════╩══════╩═════════╩════════╩══════════╩═════════════╝

You can see log space usage for SQL Server 2012+ using this query:

SELECT su.database_id
    , LogSizeMB = su.total_log_size_in_bytes / 1048576E0
    , LogUsedMB = su.used_log_space_in_bytes / 1048576E0
    , LogUsedPercent = su.used_log_space_in_percent
FROM sys.dm_db_log_space_usage su;

The sys.dm_db_log_space_usage output looks like:

╔═════════════╦═════════════╦═══════════╦════════════════╗
║ database_id ║ LogSizeMB   ║ LogUsedMB ║ LogUsedPercent ║
╠═════════════╬═════════════╬═══════════╬════════════════╣
║ 7           ║ 255.9921875 ║ 6.171875  ║ 2.410962       ║
╚═════════════╩═════════════╩═══════════╩════════════════╝

SQL Server 2016+ includes a Dynamic Management Function named sys.dm_db_log_info, which provides details about VLFs.

An example of how to use it:

DECLARE @DatabaseId tinyint = DB_ID('msdb');
SELECT DatabaseName = d.name
    , FileName = mf.physical_name
    , ddli.vlf_begin_offset
    , ddli.vlf_size_mb
    , ddli.vlf_active
    , ddli.vlf_status
FROM sys.dm_db_log_info (@DatabaseId) ddli
    INNER JOIN sys.databases d ON ddli.database_id = d.database_id
    INNER JOIN sys.master_files mf ON ddli.file_id = mf.file_id AND ddli.database_id = mf.database_id
ORDER BY d.name
    , mf.file_guid;

The output looks like:

╔══════════════╦══════════════════════════════╦══════════════════╦═════════════╦════════════╦════════════╗
║ DatabaseName ║           FileName           ║ vlf_begin_offset ║ vlf_size_mb ║ vlf_active ║ vlf_status ║
╠══════════════╬══════════════════════════════╬══════════════════╬═════════════╬════════════╬════════════╣
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         31064064 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         41549824 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         52035584 ║          10 ║          1 ║          2 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         62521344 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         73007104 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         83492864 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         93978624 ║       10.37 ║          0 ║          0 ║
╚══════════════╩══════════════════════════════╩══════════════════╩═════════════╩════════════╩════════════╝

For aggregate details about the log, sys.dm_db_log_stats provides great info:

SELECT *
FROM sys.dm_db_log_stats(@DatabaseId)

Output includes the following columns:

database_id
recovery_model
log_min_lsn
log_end_lsn
current_vlf_sequence_number
current_vlf_size_mb
total_vlf_count
total_log_size_mb
active_vlf_count
active_log_size_mb
log_truncation_holdup_reason
log_backup_time
log_backup_lsn
log_since_last_log_backup_mb
log_checkpoint_lsn
log_since_last_checkpoint_mb
log_recovery_lsn
log_recovery_size_mb
recovery_vlf_count

For further details, I wrote a blog post on SQLServerScience.com about sizing your log file for the optimal number of VLFs.

Whether you use Full Recovery matters massively, of course, because that requires a much larger log file. And it means that your backup isn't your only protection from data loss. Important, when losing any data at all is severely bad for business. Some of us can stand to lose a day's data, very occasionally.

Some relevant reading:

https://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/

https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

The first of these asserts that 1000 virtual log files is reason to plan to take action when convenient, and links to instructions. Well, to an outline of an approach.

The second comments that VLFs can be too few and too large.

The third describes the formula of how many VLFs are created within a given increment size, before and after SQL Server 2014, where the behaviour changed. Why not just let us say how many...

Also you can consider how many physical log files to have...

Here, we have a lot of databases, some modestly large and some less so, and we use Simple Recovery. I think actually the compromise point I chose for log autogrowth was 1 MB (!) initial size and 63 MB increment, constituting 4 virtual log files, whereas 64 MB increment makes 8 more VLFs. For smaller databases, I made the increment 16 MB, expecting that this would be enough total log space for light use when 1 MB wasn't: and no need to give 64 MB to each as the next size step, when, as I say, I've got a lot of databases.

(Updated - added "because that requires a much larger log file".)

In older versions of SQL, the threshold where VLFs started to affect recovery times were lower, but with newer versions of SQL you may not see any ill-effects until you get past the 5k mark or even higher. A lot of it really depends on your hardware configuration and the version of SQL Server you're running.

For example, about 10 years ago I administered a SQL 2005 instance containing a 200+ GB database with 500+ VLFs and after lowering/reconfiguring their amounts/sizes recovery times significantly improved. A similar database with a similar configuration on SQL 2012 years later didn't have any issues, and this was likely because of the newer version of SQL as well as the updated hardware.

For now, I suggest you look at recovery times for dbs with 1000 VLFs to start with. If that number of VLFs doesn't seem to adversely affect recover/startup times, adjust your threshold to 1500-2000 VLFs before you start worrying and further adjust as needed . Really, your configuration is going to dictate the threshold you should be concerned with.