How many VLF's can be bad

I have a database of size 840 GB which has 948 VLF's. On other hand there is one with size of 1.6 TB having 320 VLF's. Does that indicate the smaller database with that high count of VLF's is actually a problem? Also how can we decide that number to be too big or within OK range.

Please suggest

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.d

StackExchange Database Administrators Q#180136, answer score: 16