在这篇文章中，我想详细谈谈为什么你应该避免对事务日志（TransactionLog）进行AutoGrowth操作。许多正在运行的数据库服务器使用默认的日志文件大小和事务日志的自动增长设置。人们有时会严重依赖自动增长机制，因为它们恰好起作用。当然，如果它正常工作，你不必太在意它，但你很快就会发现不对劲。仅依赖事务日志的自动增长机制绝不是一个好主意。首先它会导致严重的日志碎片（LogFragmentation），这会对你的数据库在SQLServer启动时执行崩溃恢复（CrashRecovery）时产生很大的负面影响。另外，在你的数据库中写入事务需要等待，只要事务日志触发自动增长机制即可。当事务日志的自动增长机制出现时，SQLServer总是会零初始化新的块，这个块会被添加到文件的末尾。这与您的SQLServer实例是否使用即时文件初始化权限无关-事务日志将始终进行零初始化。这样做的原因很明显：当SQLServer过去对事务日志进行过回绕时，崩溃恢复（CrashRecovery）需要知道在哪里停止。零初始化的问题是它需要更多的时间（取决于你的自动增长率，和你的存储速度）。在此期间没有其他事务可以将事务日志记录写入事务日志。事务日志管理器上的闩锁会导致阻塞。所以你的写入事务进入挂起状态（直到它们获得所需的锁存器），并且它们等待，等待，等待，直到你的事务日志自动增长完成。让我们用一个简单的例子来演示。首先，我为此演示创建了一个新数据库。对于这个数据库，我这里不使用默认设置。对于事务日志，我指定了10GB的自动增长因子。这确实是不好的做法，但我只是用它来演示此设置的副作用。请不要在您的生产数据库中使用此错误配置！！！--Createanewdatabasewith10GBAutoGrowthfortheTransactionLogCREATEDATABASEAutoGrowthTransactionLogONPRIMARY(NAME=N'AutoGrowthTransactionLog',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog.mdf',SIZE=5120KB,FILEGROWTH=1024KB)AutologGON(NAME',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog_log.ldf',SIZE=1024KB,FILEGROWTH=10240000KB--10GBAutoGrowth!)GO在下一步中，我在数据库。第一张表我通过插入一些日志来快速填充我的事务日志。在事务日志的自动增长阶段，我们在第二张表中插入新的记录来证明事务会被自动增长机制阻塞。--Createanewtable,everyrecordsneedsapageof8kbCREATETABLEChunk(Col1INTIDENTITYPRIMARYKEY,Col2CHAR(8000))GO--AnothersimpletableCREATETABLEFOo(BarINTNOTNULL)GON现在我们已经创建了必要的数据库对象，我可以用不立即提交的新事务填充事务日志：--Beginanewtransaction，即blocksthe1stVLFintheTransactionLogBEGINTRANSACTIONINSERTINTOChunkVALUES(REPLICATE('x',8000))GO因为我们现在有正在进行的、未提交的事务，SQLServer不能重用事务日志的那部分，也就是这个事务存储的事务日志。它们可能需要回滚。所以现在我通过不同的会话插入66条其他记录来填充事务日志：INSERTINTOAutoGrowthTransactionLog.dbo.ChunkVALUES(REPLICATE('x',8000))GO66***CommitourinthefirstsessionTransaction:COMMIT这意味着在我们面前有一个快满的事务日志，我们可以通过DBCCLOGINFO来验证：DBCCLOGINFO现在当我们向表中插入记录时，事务日志没有空闲空间，SQLServer进入事务日志的自动增长。--这条语句会触发AutoGrowth机制！INSERTINTOChunkVALUES(REPLICATE('x',8000))GO在自动增长期间，为了监控发生了什么，我们可以在SSMS中打开一个新的会话窗口，并尝试在第二个表中插入另一个记录——TableFoo：--该语句现在被AutoGrowth机制屏蔽。INSERTINTOFooVALUES(1)GO这条SQL语句将被阻塞，因为事务日志当前不可用，无法将事务写入事务日志记录。为了进一步分析这种阻塞情况，可以打开第三个会话窗口，执行下面两条SQL语句：--AnalyzetheblockingsituationSELECTwait_type,*FROMsys.dm_exec_requestsWHEREsession_idIN(54,55)SELECTwait_type,*FROMsys.dm_os_waiting_tasksWHEREsession_idIN(54,55)GO(amount，本地测试没通过…………）从代码中可以看出，我用了2个DMVsys.dm_exec_requests和sys.dm_os_waiting_tasks来跟踪两个session——触发自动增长的session，和session那是自动被增长机制阻止的会话。在这里，所谓的抢占式等待类型（PreemptiveWaitType）——PREEMPTIVE_OS_WRITEFILEGATHER就是在会话中触发自动增长。抢占式等待类型是SQLServer在调度机制之外执行WIN32API函数时，SQLServer返回的等待类型。这里的自动增长是通过WriteFileGather的WIN32API函数完成的。INSERT语句尝试在Foo表中插入一条具有LATCH_EX等待类型的新记录。从DMVsys.dm_os_waiting_tasks中的resource_description列可以看出，需要在SQLServer日志管理器上获取一个latch。您可以通过查询DMVsys.dm_os_latch_stats并将lactch类限制为LOG_MANAGER来再次检查。您会在那个特定的闩锁上看到一些等待。该闩锁由事务获取，由事务日志的自动增长触发，只要获取该闩锁，其他所有写入事务都会被阻塞。所以当系统上有很多等待时间时，这意味着当前事务日志中存在需要处理的自动增长问题。我希望我已经用这个日志让你相信依赖事务日志的自动增长机制不是最好的解决方案。正如您在这个简单示例中看到的那样，数据库中被自动增长操作阻塞的每个写入事务都会阻塞，这肯定会损害数据库的吞吐量和可伸缩性。为了确保您拥有良好的事务日志性能，您可以***想要练习这篇文章。
