SQLServer日志配置问题

太多VLFs SQL Server数据库引擎在内部将每一物理日志文件分成多个虚拟日志文件,这样日志管理系统可以轻松的跟踪那些部分是可以被重用的。事务日志文件根据下面的公式决定生成多少个VLFs,不管是自动增长还是手动增长： Up to 1MB 2 VLFs, each roughly 1/2 of

太多VLFs

SQL Server数据库引擎在内部将每一物理日志文件分成多个虚拟日志文件,这样日志管理系统可以轻松的跟踪那些部分是可以被重用的。事务日志文件根据下面的公式决定生成多少个VLFs,不管是自动增长还是手动增长：

Up to 1MB

2 VLFs, each roughly 1/2 of the total size

1MB to 64MB

4 VLFs, each roughly 1/4 of the total size

64MB to 1GB

8 VLFs, each roughly 1/8 of the total size

More than 1GB

16 VLFs, each roughly 1/16 of the total size

举个例子，如果创建一个8GB的事务日志文件，那么会得到16个VLF,每个大约512MB.如果日志一次性增长4GB,那么我们会得到另外16个VLF,每个大约256MB，整个文件具有32个VLF.

一般最好的做法是设置日志的自动增长而不是默认的10%,这样你可以更好控制日志由于zero-initializing操作导致的暂停。比方说，你创建一个256MB的事务日志，并设置自动增长到32MB，然后将日志增长到16GB的稳态大小。根据上述公式，这将导致你的交易记录有4000多的VLF。

这许多的VLF很可能会对需要事务日志的操作（如崩溃恢复，清除日志，日志备份，事务复制，数据库恢复）产生性能问题。这种情况被称为有VLF碎片。一般来说任何数量的VLF超过一千元左右将是有问题的，需要加以解决（我曾经听说过的最多的是154万的VLF在超过1TB的大小事务日志！）。

太多的VLFs可能会导致一些操作在处理日志的时候遇到性能问题(比如崩溃恢复，清除日志，日志备份，事务复制，数据库恢复)。这种情况被称为VLF碎片。一般来说超过1000的VLF是有问题的，需要加以解决（我曾经听说过的1TB的事务日志文件有超过154W的VLF）.

查询VLFs的数量可以使用undocumented（绝对安全）的DBCC LOGINFO命令。输出的行数就是VLF在事务日志中的数量。如果你觉得VLF太多，可以用下面的方式减少：

1. 清除日志（比如通过日志备份等等截断日志）

2. 手动收缩日志文件

3. 重复步骤1和2，直到日志达到小尺寸（在繁忙的生产系统可能会比较麻烦）

4. 手动将日志增长到期望的大小，比如8GB这样VLFS单个VLF不超过0.5GB.

你可以阅读更多有关VLF碎片问题并且如何解决：

· Microsoft KB article that advises reducing VLF numbers

· Can log files growth affect DML?

· 8 steps to better transaction log throughput

Tempdb

Tempdb日志配置应该跟其他数据库一样，，而且也会像其他数据库一样自动增长。但是它有一些潜在的因素会导致问题。当一个SQL Server实例重新启动后，tempdb数据库的数据和日志文件将恢复到初始文件设置的大小，而其他数据库会保持当前大小不变。

这种行为意味着当tmpdb已经增长到合适大小，你必须使用Alter database设置日志文件的固定大小，否则重启之后日志文件需要从设置的初始值增长到合适大小。每当日志增长，新的空间必须要做零初始化并且将导致日志记录暂停，这个会影响性能。所以如果你没有正确的管理tempdb日志文件的大小，那么在实例重启之后你将会付出性能的损失。

定期日志收缩　

很多时候听到人们说他们在发现由于一些些普通的操作（例如每周一次的数据导入）导致数据库日志增长时，会做定期的收缩，这个操作我是不建议的。

正如我上面所解释的，每当事务日志增长或自动增长，都会因为日志文件zero-initialize动作导致停顿。如果事务日志文件经常需要增长到大小x,那么这意味着你的应用将会在日志增长到X的过程中遭受性能影响。

如果你的交易记录不断增加X大小，不要管它！主动将其设置为大小X，按照我们上面提到的方法管理VLF,因为这个大小是数据库正常操作需要的。

多个事务日志文件

一个数据库创建多个日志文件对性能不会有提升。当前的日志空间不足时，可能需要增加第二个日志文件。如果不增加第二个日志文件可以通过将数据库的恢复模式修改为Simple并且执行检查点(这会打破记录备份链)。

经常有人问我是否要除去第二个日志文件还是将它保留在原处。答案是尽快将其移除。

虽然第二个日志文件不会导致性能问题，但是可能影响灾难恢复。如果你的数据库由于某种原因被损坏，你需要从头恢复。还原的第一步是当数据和日志文件不存在的时候创建这两个文件。当你创建数据文件的时候可以启用instantfile initialization参数，这个选项会跳过zero-initialization，但是这个参数不适用于日志文件。这意味着使用完整备份恢复需要创建所有的日志文件（或在事务日志备份恢复期间）并且做zero-initialize。如果创建了第二个日志文件但是没有删除，zero-initialize过程增加了总的停机时间。虽然这不会导致性能问题，但是影响了服务器的可用性。

从数据库快照恢复

最后一个问题其实是在SQL Server中的bug。如果你使用一个数据库快照，以此来快速恢复到某个时间已知点从而避免还原备份（称为从快照恢复），那么你就可以节省大量的时间。然而，有一个很大的缺点。

当数据库从数据库快照恢复，事务日志重新创建了两个0.25MB的VLF。这意味着你需要手动调整日志文件大小到最佳值（或它会??自动增长），否则又会导致前面提到的零初始化并且导致日志暂停的问题，显然这不是我们期望的。

总结：

从这篇文章可以看出，有很多事情可以导致事务日志性能问题，进而导致整个库性能问题。可以利用我们上面提到的方法设置你的日志，这样会拥有健康的日志。除此之外，你还需要监视事务日志，比如由于自动增长和　过度的读取和写入ＩＯ延迟。这些会在将来的文章中解释。