SQLServer事务日志的VLF虚拟文件个数过多的处理

Review某个生产环境的errolog的时候，无意中发现这么一条日志，意思是过多的VLF文件，会影响数据库的启动速度和日志备份效率。
Database *** has more than 10000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times.Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
由于这个库已经存在很久了，猜测可能是当时设置的SQLServer的事务日志文件增长设置不合理导致的，虽然问题不大，这个情况还是简单总结一下处理方式。
 
VLF（虚拟日志文件）的副作用

事务日志中VLF会有一些负面影响，在数据库恢复过程的初始阶段，SQL Server 会发现所有事务日志文件中的所有 VLF，并生成这些 VLF 的列表。 此过程可能需要很长时间，具体取决于特定数据库中存在的 VLF 数量。 VLF 越多，过程越长。 如果遇到频繁的事务日志自动增长或小增量手动增长，数据库最终可能会出现大量 VLF。 当 VLF 数量达到数十万的范围时，你可能会遇到以下部分或大部分症状：在 SQL Server 启动期间，一个或多个数据库需要很长时间才能完成恢复。还原数据库需要很长时间才能完成。尝试附加数据库需要很长时间才能完成。尝试设置数据库镜像时，遇到错误消息 1413、1443 和 1479，表示超时。尝试还原数据库时，遇到与内存相关的错误，如 701。事务复制或变更数据捕获可能会出现明显延迟。

事务日志和VLF（虚拟日志文件）的关系

那么，事务日志和VLF文件的个数是怎么样的一种关系呢？1,在 SQL Server 2014 (12.x) 及更高版本中，如果下一次增长少于当前日志物理大小的 1/8，则创建 1 个 VLF，补偿此增长大小。2,如果下一次增长超过当前日志大小的 1/8，则使用 pre-2014 方法，即：  2.1，如果增长少于 64 MB，则创建 4 个 VLF，补偿此增长大小（例如，增长 1 MB，创建 4 个 256 KB 的 VLF）。    在 Azure SQL 数据库中，从 SQL Server 2022 (16.x)（所有版本）开始，逻辑略有不同。 如果增长小于或等于 64 MB，则数据库引擎只创建一个 VLF 来补偿此增长大小。  2.2，如果增长来自 64 MB（至 1 GB），则创建 8 个 VLF，补偿此增长大小（例如，增长 512 MB，创建 8 个 64 MB 的 VLF）。  2.3，如果增长大于 1 GB，则创建 16 个 VLF，补偿此增长大小（例如，增长 8 GB，创建 16 个 512 MB 的 VLF）。 从2.1中可以看到，如果设置的日志增长值过小，会导致生成多个较小的VLF。那么又如何修复这种情况呢？ VLF（虚拟日志文件）过多的修复方案

如下是一个完整的测试脚本以及验证方案。

1. --建库脚本，刻意将日志文件的增长设置为1MB（FILEGROWTH = 1024KB）
2. CREATE DATABASE [DB01]
3. CONTAINMENT = NONE
4. ON  PRIMARY
5. ( NAME = N'DB01', FILENAME = N'D:\MSSQL\DB01.mdf' , SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
6. LOG ON
7. ( NAME = N'DB01_log', FILENAME = N'D:\MSSQL\DB01_log.ldf' , SIZE = 64MB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )
8. WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
9. GO
11. --新建的数据库默认是完整恢复模式，建库之后做一次完整的备份，否则数据库还是简单恢复模式
12. backup database DB01 to disk = 'D:\MSSQL\DB01.bak';
15. -- 查看 DB 增长信息
16. use DB01;
17. go
19. SELECT
20.     name,
21.     type_desc ,
22.     size/128.0 AS current_size,
23.     -- growth: 8kb page number
24.     CASE is_percent_growth
25.         WHEN 1 THEN CAST(growth AS VARCHAR) + '%'
26.         ELSE CAST(growth*8/1024.0 AS VARCHAR) + ' MB'
27.     END AS 'growth',
28.     physical_name
29. FROM sys.database_files;
33. --创建一个测试表
34. create table t1
35. (
36.         c1 int identity(1,1),
37.         c2 varchar(50),
38.         c3 varchar(50),
39.         c4 varchar(50),
40.         c5 varchar(50),
41.         c6 varchar(50),
42.         c7 varchar(50),
43.         c8 datetime2,
44.         constraint pk_t1_c1 primary key(c1)
45. );
47. --往测试表写入数据
48. insert into t1
49. select newid(),newid(),newid(),newid(),newid(),newid(),GETDATE() from sys.objects a , sys.objects b, sys.objects c;
51. --再多执行几次这个语句，生成足够多的事务日志
52. insert into t1
53. select  newid(),newid(),newid(),newid(),newid(),newid(),GETDATE() from t1;
56. -- sqlserver 事务日志和包含的虚拟日志文件个数
57. SELECT db.name, count(dbl.database_id) as Total_VLF_count, convert(decimal (10,2), avg(dbl.vlf_size_mb)) as Avg_VLF_Size_MB
58. FROM sys.databases db
59. CROSS APPLY sys.dm_db_log_info(db.database_id) dbl
60. where name = 'DB01'
61. GROUP BY db.name
62. ORDER BY Total_VLF_count DESC;
65. --查看数据文件使用比例
66. SELECT file_id, name,type_desc,
67.        CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
68.        CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
69.        CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
70.        CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
71. FROM sys.database_files;
74. -- 事务日志使用情况
75. DBCC SQLPERF(LOGSPACE);
77. -- 使用DBCC LOGINFO查看虚拟日志文件(VLF)状态,(0=空闲,2=活动)
78. DBCC LOGINFO;
81. -- 查看日志截断原因
82. SELECT
83.     name AS '数据库名称',
84.     log_reuse_wait_desc AS '日志重用等待原因'
85. FROM sys.databases;
88. -- DB Size 查看
89. EXEC sp_helpdb 'DB01';
93. --修复包含大量 VLF 的数据库
95. solution1:
96.         1,备份事务日志
97.         backup log DB01 to disk = 'D:\MSSQL\db01.trn2';
98.         2,手动收缩事务日志文件，如下单位是MB
99.         DBCC SHRINKFILE (N'DB01_log' , 64);
100.         --这种方式收缩无效，需要收缩到指定大小才行
101.         DBCC SHRINKFILE (N'DB01_log' , 0, TRUNCATEONLY);
102.         3,使用以下 T-SQL 脚本在一个步骤中手动将文件增长到所需的大小,避免频繁增长导致过多的VLF：
103.         ALTER DATABASE DB01 MODIFY FILE (NAME='DB01_log', SIZE = 1024MB);
104.         请注意：上述直接修改DB01_log的大小的时候，生成的VLF的个数，不受限于上述默认自动增长生成VLF个数的规则
106. solution2:
107.         1，备份事务日志
108.         2，手动收缩事务日志文件。
109.         3，ALTER DATABASE DB01 MODIFY FILE (NAME = 'DB01_log', FILEGROWTH = 512MB);

复制代码

refer：
https://learn.microsoft.com/zh-cn/sql/relational-databases/errors-events/mssqlserver-9017-database-engine-error?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16 
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！