在这篇文章里，我想详细谈下为什么你要避免事务日志（transaction log）上的自动增长操作（auto growth
operations）。很多运行的数据库服务器，对于事务日志，用的都是默认的日志文件大小和自动增长设置。人们有时会很依赖自动增长机制，因为它们刚
好能正常工作。当然，如果它正常工作的话，你不必太关注它，但很快你会发现会有问题出现。
只依赖于事务日志的自动增长机制总不是个好主意。首先它会导致严重的日志碎片（log fragmentation），在sql server启动期间，在你数据库上执行崩溃恢复（crash recovery）时会有很大的负面影响。另外，在你数据库里写入事务需要等待，只要事务日志触发了自动增长机制。
当事务日志的自动增长机制发生时，sql server总要零初始化新块，这个会在文件末尾加上。这和你的sql server实例是否用即时文件初始化（instant file initialization）特权——事务日志总会零初始化。这上面的原因非常明显：当sql server在过去已经完成事务日志的环绕式处理（wrap-around ），崩溃恢复（crash recovery）需要知道在哪里停。
零初始化的问题是会占用更多的时间（取决与你的自动增长率，还有你的存储速度）。在此期间没有别的事务可以写事务日志记录到事务日志。在事务日志管
理器上会有闩锁造成的阻塞。因此你的写入事务会进入挂起状态（直到它们获得需要的闩锁），它们就等啊，等啊，等啊，直到你的事务日志自动增长完成。让我们
用一个简单的例子演示下。
首先我为这个演示创建一个新的数据库。对于这个数据库，这里我不用默认的设置，对于事务日志，我指定了10gb的自动增长系数。这个的确是个不好的做法，但我只是用它来展示这个设置的副作用。请不要在你的生产数据库里使用这个错误配置！！！
-- create a new database with 10 gb auto growth for the transaction log
create database autogrowthtransactionlog on primary
(
name = n'autogrowthtransactionlog',
filename = n'c:\program files\microsoft sql server\mssql10.mssqlserver\mssql\data\autogrowthtransactionlog.mdf',
size = 5120kb,
filegrowth = 1024kb
)
log on
name = n'autogrowthtransactionlog_log',
filename = n'c:\program files\microsoft sql server\mssql10.mssqlserver\mssql\data\autogrowthtransactionlog_log.ldf',
size = 1024kb,
filegrowth = 10240000kb -- 10 gb auto growth!
go
下一步里我在数据库里创建2个表。第1个表我通过插入一些日志来快速填充我的事务日志。在事务日志自动增长阶段，我们在第2个表里插入新的记录来证明这个事务会被自动增长机制阻塞。
-- create a new table, every records needs a page of 8kb
create table chunk
col1 int identity primary key,
col2 char(8000)
-- another simple table
create table foo
(
bar int not null
现在我们已经创建了必须的数据库对象，因次我可以通过新的没有立即提交的事务来填充事务日志：
-- begin a new transaction, that blocks the 1st vlf in the transaction log
begin transaction
insert into chunk values (replicate('x', 8000))
因为我们现在有了进行中，没提交的事务，sql server不能重用那部分事务日志，即这个事务存储的事务日志。它们有需要回滚的可能。因此现在我通过不同的会话插入66条其他记录来填充事务日志：
insert into autogrowthtransactionlog.dbo.chunk values (replicate('x', 8000))
go 66
最后在第一个会话里提交我们的事务：
commit
这意味着在我们面前有一个几乎满的的事务日志，我们可以通过dbcc loginfo来验证：
dbcc loginfo
现在当我们往表里插入兮的记录时，事务日志已经没有可用空间了，sql server进入事务日志的自动增长。
-- this statement will trigger the auto growth mechanism!
在自动增长期间的同时，为了监控发生了什么，我们可以在ssms里打开新的一个会话窗口，尝试在第2个表插入另外的记录——表foo：
-- this statement is now blocked by the auto growth mechanism.
insert into foo values (1)
go
这个sql 语句会阻塞，因为事务要写入事务日志记录的事务日志，当前不可用。为了进一步分析这个阻塞情形，你可以打开第3个会话窗口，执行下列2个sql语句：
-- analyze the blocking situation
select wait_type, * from sys.dm_exec_requests
where session_id in (54, 55)
select wait_type, * from sys.dm_os_waiting_tasks
（额，俺本机测试失败………………）
从代码里可以看到，我用2个dmv sys.dm_exec_requests 和
sys.dm_os_waiting_tasks对2个会话都进行了跟踪——触发自动增长的会话，和被自动增长机制阻塞的会话。在这里，触发自动增长的会
话里有所谓的抢占等待类型（preemptive wait
type）——preemptive_os_writefilegather。抢占等待类型是由sql server返回的等待类型，当sql
insert语句尝试在foo表里插入新的记录出现latch_ex等待类型。如你从dmv sys.dm_os_waiting_tasks
里的resource_description列所见，在sql
server的日志管理器上需要获得闩锁。你可以通过查询dmv sys.dm_os_latch_stats 限制lactch
class为log_manager再次确认。在那个特定闩锁上你会看到一些等待。那个闩锁是事务获取的，由事务日志的自动增长触发，只要这个闩锁要获
得，每个其他写事务都会被阻塞。因此在系统上有大量等待时间时，这暗示这在事务日志里当前有自动增长问题需要处理。
来源：51cto