时间：2021-07-01 10:21:17 帮助过：14人阅读
这篇文章会列出导致事务日志过度增长的常见的问题和错误管理形式，包括：
当然，如果增长没检查，日志文件会扩展直到吞没所有可用磁盘空间或日志文件的最大大小，在这个时候你会收到该死的9002错误（事务日志已满），数据库会变成只读。这篇文章会谈到处理日志不断增长和9002错误的正确方法，还会解释下通常建议截断或收缩事务日志为什么是危险的。
最后，我们会谈下保证你日志文件是平稳和可预见增长的措施，还有日志碎片的最小化问题。在忙碌的数据库里，大型的事务日志会是一个简单的生活事实，如果管理妥当的话，这并不是件坏事，即使大部分时间日志文件空间不可用。
任何时候日志文件需要增长，额外的空间被分配，这个空间平均分到VLS里，基于被分配空间数。
例如，日志文件默认会有2MB的初始大小，10%的自动增长率（来自model数据库的配置）。这就是说，日志文件开始至少会很小的增长，因此会有大量的小VLF。
当我们在很大的块上分配额外空间时，例如当一次操作初始16GB的大小，结果事务日志会有很小数量的VLF。
太高数量的小VLF，这样的情况称为日志文件碎片，会影响到性能，尤其在故障恢复，还原和备份，特别是日志备份。换句话说，它会影响读取日志文件的操作性能。我们会在第8篇详细讨论这个问题。
事务日志VLF——太多还是太少？
SQL Server MVP的Kimberly Tripp在他的文章里讨论了VLF大小的影响，并提供了如何恰当管理VLF大小的指导——事务日志VLF—太多还是太少？
相反，如果日志文件只有几个很大的VLF，我们有长时间占用大块日志的风险。每个VLF都有很大数量的日志记录，SQL Server不能截断VLF直到它没有包含活动日志。这个情况下截断会因某些原因延迟（在缺少日志空间重用部分会详细谈到），这会导致日志的快速增长。例如，我们假设每个VLF是1GB大小且日志满了。你进行了一次日志备份，但是所有的VLF包含活动日志的一部分，SQL Server不能截断日志。它没有别的选择只能增加更多VLF，如果日志的增长率设置为同等大小，那么日志增长会很快，直到有VLF变成可截断。
因此，正确设置日志初始大小非常重要，那它的增长才会是合适的大小步骤，最小化日志碎片也避免了过快增长。
正确设置初始大小且可控制它的增长的第2个原因是：对于日志文件，每个增长是相对昂贵的操作。数据和日志文件增长超时是正常的。SQL Server可以优化增加新数据文件和扩展现有数据文件的过程，通过即时文件初始化（instant file initialization）（在SQL Server 2005引入，允许在磁盘上分配空间给数据文件，而不需要进行填零）。遗憾的是，对于日志文件是不一样的，对于日志文件创建或增长的空间分配，还是需要初始化且填零。
为什么事务日志不能使用即时初始化？
进一步关注事务日志填零，看下Paul Randal的文章：http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-24-Why-cant-the-transaction-log-use-instant-initialization.aspx
如果你经历事务日志的不可控增长，它由于要么是活动日志频率太高，要么是有因素阻止日志文件里的空间重用，或者两者都有。
如果增长的主要原因是活动日志过多，你要检查下是否可以避免这个活动，例如调整处理大容量数据和索引维护的数据库模式，这样的话这些操作不会完整记录（例如针对这些操作使用大容量日志恢复模式）。但是，如果日志备份里包含有任何的最小化日志操作，大容量操作会立即阻止数据库到时间点的恢复（可以阅读下第6篇文章来获得更多详细信息）。如果这是不可接受的，你必须直接接受大日志的事实，根据具体情况计划它的增长和管理（例如日志备份频率），在接下来的妥当的日志管理会介绍。
如果增长原因是缺少日志空间的重用，你要找出什么阻止这个重用并采取措施来修正这个问题。
索引维护操作是个很常见的导致事务日志使用率过度和增长的原因，尤其数据库运行在完整恢复模式里。进行索引维护需要日志空间量取决于下列因素：
当重建索引时，不管在线还是离线，使用ALTER INDEX REBUILD，或是已经废弃的SQL Server 2000里的DBCC DBREINDEX，SQL Server新创建一个索引的副本，然后一旦重建完整，删除老的副本（这是为什么你至少需要数据文件里索引大小一样的可用空间）
日志记录和在线索引重建
在SQL Server 2008和后续版本，在线索引重建是完整日志操作，在SQL Server 2005里是最小化日志。因此，在后续SQL Server版本上进行这样的操作本质上需要更多的事务日志空间，可以看下：https://support.microsoft.com/zh-cn/kb/2407439 还有Kalen Delaney的日志，对于完整和大容量日志2个恢复模式，验证下在在线和离线索引重建期间的日志记录。
在完整恢复模式里，索引重建可以是非常占资源的操作，需要事务日志里的很多空间。在简单或大容量日志恢复模式里，重建索引是最小化日志操作，这意味着只有分配被记录，实际的页并没改变，因此通过这个操作减少了日志空间量。
如果你切换到简单模式进行索引重建，LSN链会立即中断。你只能恢复你的数据库到刚才事务日志备份里的包含的时间点。为了重新开始日志链，你需要切换回完整恢复模式并立即进行一次完整或差异数据库备份。
如果你切换到大容量日志模式（看下第6篇），LSN链还是连续的，但还会影响到进行时间点的恢复，因为包含最小化日志操作的日志备份不能用来恢复到时间点。如果能恢复到时间点的要求是至高无上的，那么索引重建或任何最小化日志操作不要使用大容量日志恢复模式。除非在数据库里没有同时发生的用户活动，你可以使用。不然的话，在可能的情况下考虑在完整恢复模式里进行索引重建。
如果使用的是大容量日志模式，尽可能使时间点恢复的时间最小，这样可以最小化暴露数据丢失风险。为了做到这一点，在完整模式里进行日志备份，切换到大容量日志，进行索引重建，然后切换回完整进行另一个日志备份。
最后一个重点要记住的是ALTER INDEX REBUILD操作是在一个单独事务里。如果索引很大，事务的执行时间会很长，在期间，这会阻止日志里的空间重用。这就是说，即使你在简单模式里重建索引，你也要想到自检查点（CHECKPOINT）操作后日志应该保持很小，重建是最小化日志，在剧烈的重建期间，日志文件还会快速扩展。
和重建索引相比，使用ALTER INDEX REORGANIZE或者SQL Server里的DBCC INDEXDEFRAG(已废弃)重组（碎片整理）索引都是完整记录操作，不管是任何恢复模式，因此实际的页修改总被记录。但是，通常索引重组比索引重建需要更少的日志空间，尽管这是索引里降低碎片的一个功能；比起轻度碎片，重度碎片索引会需要更多的日志空间来重组。
另外，ALTER INDEX REORGANIZE操作是通过多个更短的事务完成的。因此，当与定期的日志备份相结合（或在简单恢复模式里）时，在此操作期间，日志空间可以被重用，因此要求操作期间日志空间最小化。
例如，对于重建操作，重建20GB的索引会需要超过20GB的空间，因为它发生在一个单独的事务里。但是，重组20GB的索引会需要更少的空间，因为在重组里每个页分配修改是个单独的事务，因此日志记录可以用定期日志备份截断，让日式空间可以重用。
如果你的组织对任何潜在数据丢失不能容忍的，那么你没有选择，只能让所有的数据库运行在完整恢复模式里，并且妥当计划你的日志大小和增长。因此索引重建是作为一个单独线程发生的，日志至少会和你重建的索引一样的大小。如刚才所说，索引重组会需要更少的空间，且允许在操作期间通过日志备份来截断日志。这样的话，为了同时避免日志暴涨，可行的话，你可以用日志重组。
如果你的SLA和操作级别协议（Operational Level Agreements（OLAs））允许一些潜在的数据丢失，那么在索引重建前l切换到大容量日志恢复模式可以为重建索引最小化空间需求量。但是，要在最小化数据丢失的方式下进行，例如已经讨论确认过了。
不管使用的恢复模式，你可以通过重组索引而不是重建索引来在日志上最小化索引维护操作的影响。可以的话，可以看下微软的指导方针，为了最小化索引维护操作的影响，对于绝大数情况，并不是所有情况，决定什么时候进行索引重建，什么时候进行索引重组（查看索引重组和重建）。他们也声明：对于碎片级别大于5%且小于30%，你应该重组索引，对于碎片级别大于30%，你应该重建它。
但是，在索引维护期间，在保护日志过度增长里，最有效的武器是维护那些真正需要的索引。使用SSMS维护计划向导，索引维护是个孤注一掷的操作：要么重建（或重组）数据库里（维护计划里的所有数据库）的所有索引，要么全不维护。一个更好的方法是使用sys.dm_db_index_physical_stats的DMV来看下碎片程度根据需要来决定索引重建/重组策略。
Ola Hallengren的免费维护脚本
Ola Hallengren提供一个综合的免费维护工具，它展示了如何使用sys.dm_db_index_physical_stats进行索引分析来进行智能维护，它可以用来代替SSMS里向导创建的数据库维护计划（https://ola.hallengren.com/）。
但是最好的方法，是计划只维护那些可以在查询上提供真正持久影响的索引。逻辑碎片（在乱序中的索引页）挫败了SQL Server的预读机制（https://msdn.microsoft.com/zh-cn/library/ms191475%28v=sql.105%29.aspx），且使在磁盘上读取连续页I/O-效率更低。但是，这只真正影响从磁盘的大范围扫描。即使对非常大碎片的索引，如果你不扫描表，重建或重组索引不会提高性能。降低页深度（通过页分裂或删除造成的很多缺口）会带来更多的页占用磁盘空间，且在内存里，会需要更多的I/O带宽来传输数据。再说一次，这个碎片格式不会真正影响不频繁修改的索引，因此重建它们不会有帮助。
计划索引维护前，问下自己什么性能标准从维护受益？它会大会减少I/O？它会提高你最昂贵查询的多少性能？它是持久正面影响么？如果这些答案是“否”或“不知道”，那么定期索引维护可能不是个长远的答案。最后，值得注意的是对小索引维护是不值得的。通常引用的阈值是近1000页。在微软，当Paul Randal管理存储引擎开发团队时，建议这些值作为参考，在在线帮助里记录了。注意，尽管这只是个建议并不对所有环境合适，如Paul在他的博客文章里谈到的：“在线帮助的索引碎片阈值来自哪里？”
sys.dm_tran_database_transactions的DMV提供在事务日志上事务活动影响的有用内部信息。在他们的书里，《使用SQL Server动态管理视图进行性能调优》，得到他们的允许后，复制在这里，作者Louis Davidson和Tim Ford，演示了如何使用这个DMV和一些其他的，来调查可能造成事务日志过度增长的事务。
在代码7.1里的例子重用来自第6篇的FullRecovery数据库和PrimaryTable_Large表。在一个显性事务里，它重建了聚集索引然后调查日志增长。
1 USE FullRecovery 2 GO 3 BEGIN TRANSACTION 4 5 ALTER INDEX ALL ON dbo.PrimaryTable_Large REBUILD 6 7 SELECT DTST.[session_id], 8 DES.[login_name] AS [Login Name], 9 DB_NAME (DTDT.database_id) AS [Database], 10 DTDT.[database_transaction_begin_time] AS [Begin Time], 11 DATEDIFF(ms, DTDT.[database_transaction_begin_time], GETDATE()) 12 AS [Duration ms] , 13 CASE DTAT.transaction_type 14 WHEN 1 THEN ‘Read/write‘ 15 WHEN 2 THEN ‘Read-only‘ 16 WHEN 3 THEN ‘System‘ 17 WHEN 4 THEN ‘Distributed‘ 18 END AS [Transaction Type], 19 CASE DTAT.transaction_state 20 WHEN 0 THEN ‘Not fully initialized‘ 21 WHEN 1 THEN ‘Initialized, not started‘ 22 WHEN 2 THEN ‘Active‘ 23 WHEN 3 THEN ‘Ended‘ 24 WHEN 4 THEN ‘Commit initiated‘ 25 WHEN 5 THEN ‘Prepared, awaiting resolution‘ 26 WHEN 6 THEN ‘Committed‘ 27 WHEN 7 THEN ‘Rolling back‘ 28 WHEN 8 THEN ‘Rolled back‘ 29 END AS [Transaction State], 30 DTDT.[database_transaction_log_record_count] AS [Log Records], 31 DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used], 32 DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd], 33 DEST.[text] AS [Last Transaction Text], 34 DEQP.[query_plan] AS [Last Query Plan] 35 FROM sys.dm_tran_database_transactions DTDT 36 INNER JOIN sys.dm_tran_session_transactions DTST 37 ON DTST.[transaction_id] = DTDT.[transaction_id] 38 INNER JOIN sys.[dm_tran_active_transactions] DTAT 39 ON DTST.[transaction_id] = DTAT.[transaction_id] 40 INNER JOIN sys.[dm_exec_sessions] DES 41 ON DES.[session_id] = DTST.[session_id] 42 INNER JOIN sys.dm_exec_connections DEC 43 ON DEC.[session_id] = DTST.[session_id] 44 LEFT JOIN sys.dm_exec_requests DER 45 ON DER.[session_id] = DTST.[session_id] 46 CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST 47 OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP 48 WHERE DB_NAME(DTDT.database_id) = ‘FullRecovery‘ 49 ORDER BY DTDT.[database_transaction_log_bytes_used] DESC; 50 -- ORDER BY [Duration ms] DESC; 51 COMMIT TRANSACTION
（代码7.1：调查重日志写入事务）
（插图7.1：索引重建后日志活动结果）
顺便提下，如果我们用ALTER INDEX...REORGANIZE来运行这个例子，那么在Log Bytes Used列的值会从近159M降为近0.5M。
如果你怀疑缺少日志空间重用造成了日志增长，你的第一个任务是找出什么阻止了重用。开始通过查询如代码7.2所示的sys.databases
1 SELECT name , 2 recovery_model_desc , 3 log_reuse_wait_desc 4 FROM sys.databases 5 WHERE name = ‘FullRecovery‘
（代码7.2：检查下log_reuse_wait_desc的列值）
log_reuse_wait_desc的列值会展示为什么当前空间不被重用的原因。如果你已经执行刚才的例子（代码7.1），那么很可能FullRecovery数据库在这列会显示LOG_BACKUP值（下面会详谈）。
阻止日志重用不止一个。sys.databases视图只显示其中一个原因。因此它是解决问题的一个可能方法，再次查询sys.database会看到log_reuse_wait不同的原因。
在在线帮助里列出了log_reuse_wait_desc所有可能值，但在这里我们只谈最常见的原因，解释如何安全确保那个空间可以被重用。
如果从sys.databases查询，log_reuse_wait_desc的返回值是LOG_BACKUP，那么你很可能遭受完整或大事务日志的最常见原因，即在完整恢复模式里的数据库（或次之，大容量日志恢复模式），没有进行事务日志备份。
在SQL Server的很多版本里，model数据库默认是完整恢复模式。因为model数据库是创建所有新SQL Server用户数据库的模板，新的数据库继承自model的配置。
对于大多数生产数据库，使用完整恢复模式是推荐的做法，因为它允许数据库的时间点恢复，最小化灾难事件的数据丢失。但是，接下来的常见错误是调整备份策略是只有完整备份（或者有差异备份）而没有定期的事务日志备份。这个策略有2个大问题：
为了进行时间点的恢复并控制日志大小，我们必须用数据库完整或完整和差异备份连同事务日志备份。对于我们的FullRecovery数据库，我们可以进行日志备份，如代码7.3所示，然后再次查询sys.databases。
1 USE master 2 GO 3 BACKUP LOG FullRecovery 4 TO DISK = ‘D:\SQLBackups\FullRecovery_log.trn‘ 5 WITH INIT 6 GO 7 8 SELECT name , 9 recovery_model_desc , 10 log_reuse_wait_desc 11 FROM sys.databases 12 WHERE name = ‘FullRecovery‘
（代码7.3：解决日志备份问题）
如果缺少日志备份是日志增长问题的原因，首先要做的是验证问题数据库是否真的需要运行在完整恢复模式。如果必须要能恢复数据库到任意时间点或到灾难事件前的一个时间点，则是必须的，或者必须要用完整恢复模式的另一个原因（例如数据库镜像）。如果在SLA里目标恢复点（ Recovery Point Objective (RPO) ）为最大15分钟的数据丢失，那么很可能你不能只进行完整数据库备份和差异数据库备份，必须要进行日志备份。
但是，如果因为不需要而没有进行日志备份，那么数据库不应该运行在完整恢复模式；我们可以切换数据库到简单恢复模式，那事务日志的不活动部分会自动标记为可重用，在检查点的时候。
如果数据库需要运行在完整恢复模式，那么开始日志备份，或调查下备份需要的频率。事务日志的备份频率取决于很多因素，例如数据修改的频率，还有在灾 难中，SLA上可接受的数据丢失程度。另外，你应该采取措施保证日志增长是可控的，在将来是可预见的，在这篇文章里的妥当的日志管理部分会介绍。
如果log_reuse_wait_desc的返回值是ACTIVE_TRANSACTION，那么你受到来自SQL Server里完整或大的事务日志的第二个常见原因：长时间运行或未提交的事务。重新执行下来自代码7.1的事务，但不提交，在重新执行下代码7.3，你 会看到这个值返回（不要忘记回去提交这个事务）。
如在第2篇日志截断和空间重用部分介绍的，事务日志里的VLF只有在不包含活动日志部分时才会被截断。如果数据库试用完整或大容量日志恢复模式，只 有日志备份操作才可以进行截断。数据库里长时间运行的事务延迟包含事务开始后生成的日志记录的VLF的截断，包括其它并发事务对数据库里的数据修改产生的 日志记录，甚至当这些改变还没提交时。另外，长时间运行的事务的空间需求量会通过对“补偿日志记录”保留的空间增加，如果在系统里事务回滚的话，这些日志 记录就会产生。这些保留是需要的，保证在回滚期间，这些事务可以成功恢复而不会用完日志空间。
另一个常见对log_reuse_wait_desc值的活动事务值是“孤立”的显式事务，它莫名其妙的从不提交。允许用户在事务里输入的应用程序就特别容易是这类问题。
造成长时间运行的事务的最常见操作，也是在数据库里生成大量日志记录，是从数据库里归档或清除数据。数据保持往往是数据库设计里事后的想法，经常是数据库已经活跃一段时间后才考虑，是在服务器接近可用存储的容量限制。
通常，当需要归档时，第一个反应是从数据库里使用简单的DELETE语句删除不需要的数据，如代码7.4所示。为了生成一些简单的测试数据，这个脚本使用Jeff Moden的随机数据生成器的简化版本，简单修改来生成日期到2012。
USE FullRecovery ;
GO
IF OBJECT_ID(‘dbo.LogTest‘, ‘U‘) IS NOT NULL
DROP TABLE dbo.LogTest ;
SELECT TOP 500000
SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 37534.0 AS DATETIME)
INTO dbo.LogTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2 ;
-- delete all but the last 60 days of data
DELETE dbo.LogTest
WHERE SomeDate < GETDATE() - 60
（代码7.4：大容量数据删除）
取决于要删除的在日期范围内存在的行数，这会变成引起日志增长问题的长时间运行的事务，即使数据库运行在简单恢复模式。外键串联约束的出现或审计触 发器会恶化问题。如果其它表引用目标表，通过外键约束来级联删除，那么SQL Server页通过级联约束来记录删除的行的细节。如果表上有DELETE触发器，在触发器执行期间，SQL Server也会记录进行的操作。
为了最小化在事务日志上的影响，数据清理应该简化为更短，独立的事务。有很多方法中断长时间运行的事务为小的批处理。如果表存在级别约束或DELETE触发器，我们可以在循环内进行删除操作，在一个时间删除一天的数据，如果代码7.5所示。注意，在这个简单的例子里，在我们的表里没有足够的行来验证这个技术的使用，简单的DELETE；清理几百万行数据更合适。也注意批量删除的主要关心的是并不是速度（代码7.5会比代码7.4运行更慢）。最要关心的是避免日志过度增长和锁升级。
DECLARE @StopDate DATETIME , @PurgeDate DATETIME SELECT @PurgeDate = DATEADD(DAY, DATEDIFF(DAY, 0, MIN(SomeDate)), 0) , @StopDate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 60, 0) FROM dbo.LogTest WHILE @PurgeDate < @StopDate BEGIN DELETE dbo.LogTest WHERE SomeDate < @PurgeDate SELECT @PurgeDate = DATEADD(DAY, 1, @PurgeDate) END
（代码7.5：将数据清除拆散为小的事务）
使用这个方法清除数据，每个删除事务的持续时间是从表里删除一条记录的时间，加上任何触发器或级联约束进行它们操作的时间。如果数据库使用简单恢复模式，下个检查点会截断这些清除产生的日志记录，只要在VLF里没有相关数据清理的活动日志存在。
当在处理过程中级联约束或审计触发器不是要考虑的，我们可以使用不同的方法来清理表上的数据，同时最小化事务。不是进行一点的DELETE操作，它会影响多少的数据，取决于指定日期里存在的行数，在DELETE语句里使用TOP运算符会限制每个循环操作影响的行数。使用@@ROWCOUNT来捕获DELETE操作影响的行数，运算符会在小的批处理语句里清除数据，直到@@ROWCOUNT的值小于DELETE语句里TOP子句里指定的行数，如代码7.6所示。
这个方法只有在没有触发器和级别约束时使用有效，不然的话@@ROWCOUNT的结果不是实际表删除的行数，而是触发器执行或通过强制级联约束影响的行数。
1 DECLARE @Criteria DATETIME , 2 @RowCount INT 3 SELECT @Criteria = GETDATE() - 60 , 4 @RowCount = 10000 5 WHILE @RowCount = 10000 6 BEGIN 7 DELETE TOP ( 10000 ) 8 FROM dbo.LogTest 9 WHERE SomeDate < @Criteria 10 SELECT @RowCount = @@ROWCOUNT 11 END
（代码7.6：对于数据清理在DELETE语句里使用TOP运算符）
这些方法在SQL Server 2000,2005,2008的任何版本都可以使用，在数据清理期间最小化事务。
但是，如果你的数据库是SQL Server 2005或2008企业版，且经常清理数据，那么清理数据的更好方法是表分区，在列上筛选要删除的数据。这会更小影响事务日志，因为分区包含的数据会从表转出并清理，对SQL Server只是记录区重新分配的操作。
管理存档
这已经是这个系列文章讨论范围之外了，自动归档方案。但是，一个可能的归档过程涉及分区，表之间的架构复制，允许一个表的一个分区可以转出到另一个。在主要的OLTP表最小化数据的活动部分，但只减少修改的元数据的归档过程。Kimberley Tripp已经写了一份具体的白皮书，叫做SQL Server 2005里的表和索引分区，它谈了划窗技术（sliding window technique）。
默认情况下，SQL Server会在隐性事务里包裹任何数据修改语句来保证，在灾难事件里，SQL Server可以回滚在故障点已经做出的修改，返回数据到一致的状态。如果修改成功，隐性事务会提交到数据库。和自动发生的隐性事务相比，我们创建显性事务，在代码包裹多个修改在一个事务里，来保证所有的修改通过ROLLBACK命令可以撤销，或者通过COMMIT命令提交让它持久。
当恰当使用时，显性事务可以保证多个表之间的数据修改作为一个单位成功完成，或者全部都不修改。当使用不当时，不管怎样，在数据库里孤立的事务还是活跃的，阻止事务日志的截断，这会导致事务日志增长或填满。在SQL Server里有很多孤立事务的原因，这超出了这篇文章详细介绍的范围。但是，一些常见的原因有：
一旦一个事务开始，它会保持活动直到创建的连接，事务触发COMMIT或ROLLBACK语句，或者连接从SQL Server中断（当使用绑定的链接，会允许会话共享锁，这是个异常）。
现在的应用程序通常会使用连接池，在池里保持与SQL Server的连接让程序重用，即使当程序代码在连接上调用Close()方法。当对孤立事务进行故障排除是理解最后一点非常重要，因为即使连接在加入或返回到应用程序连接池前被重置，数据库里打开的事务还是继续存在的，如果它们没有正常结束的话。
事务相关的DMV提供大量的额外信息，不管当前事务的状态和进行的操作。但是，一些DBA还是使用DBCC OPENTRAN作为识别是否为孤立事务（或只是长时间运行的）为造成日志增长的根源的最快方法。
在DBCC OPENTRAN(DatabaseName)格式里会接受数据库名称作为输入参数，数据库名称是用作检查打开事务的数据库名。如果数据库里有活动事务存在，命令会输出类似如下的信息。
1 DBCC OPENTRAN (FullRecovery)
Transaction information for database ‘FullRecovery‘.
Oldest active transaction:
SPID (server process ID): 56
UID (user ID) : -1
Name : user_transaction
LSN : (897:15322:1)
Start time : Sep 18 2012 1:01:29:390PM
SID : 0x010500000000000515000000fd43461e19525f12828ba628ee0a0000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
（代码7.7：来自DBCC OPENTRAN的输出信息范例）
DBCC OPENTRAN只输出最早的活动事务，但主要表示事务是否为活动的疑问是开始时间。一般来说，未提交的事务是打开很长时间才会是造成事务日志增长的原因。
另一个重要的信息是SPID（server process ID；在DMV里这用session_id代替），这用来标识创建打开事务的会话。我们可以通过SPID判断事务是真的孤立还是只是长时间运行的，通过查询sysprocesses视图（在SQL Server 2000里）或者SQL Server 2005及后续版本里的sys.dm_exec_sessions和sys.dm_exec_connections的动态视图，如代码7.8所示。注意sysprocesses视图在SQL Server 2005及后续版本还是可用的，保持向后的兼容性。在运行代码7.8时，在每个查询里，直接用你看到的会话值替换session_id值（我们注释了几列，只是为了简化输出的可读性）。
1 USE master 2 GO 3 SELECT spid , 4 status , 5 -- hostname , 6 -- program_name , 7 -- loginame , 8 login_time , 9 last_batch , 10 ( SELECT text 11 FROM :: 12 fn_get_sql(sql_handle) 13 ) AS [sql_text] 14 FROM sysprocesses 15 WHERE spid = 53 16 17 USE FullRecovery 18 GO 19 SELECT s.session_id , 20 s.status , 21 -- s.host_name , 22 -- s.program_name , 23 -- s.login_name , 24 s.login_time , 25 s.last_request_start_time , 26 s.last_request_end_time , 27 t.text 28 FROM sys.dm_exec_sessions s 29 JOIN sys.dm_exec_connections c ON s.session_id = c.session_id 30 CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t 31 WHERE s.session_id = 53
（代码7.8：使用DMV来识别孤立的还是长时间运行的事务）
如果回话是runnable，running或suspended状态，那么可能问题的根源是长时间运行，而不是孤立的事务。但是，只有进一步的调查才能确认。很有可能刚才的事务失败且连接重置，使用连接池，当前运行的语句不是打开事务所关联的。
在SQL Server 2005和后续版本，我们可以使用sys.dm_tran_session_transactions和sys.dm_tran_database_transactions对打开的事务收集信息，包括事务开始事件，打开事务使用的日志数，以及日志空间使用字节数，如我们刚才代码7.1所见。代码7.9展示了一个简单的版本，带有范例输出。
（代码7.9：收集打开事务的信息）
除非应用程序设计来检查，处理孤立的事务，清除事务的唯一方法是KILL会话，它会造成事务回滚，和连接中断一样，在下一次日志备份期间，允许日志里的空间是可以被重用的。但是，回滚的执行后果必须要理解的。
除了刚才提到的原因之外，还有其他一些问题阻止日志里空间重用，导致日志过度增长。这里我会谈其中的一些，这个问题的更多信息，可以看下Gail Shaw的文章，为什么我的事务日志满了？
REPLICATION在事务复制期间，日志读取代理的任务是读取事务日志， 查找关联修改的日志记录，复制到订阅者（例如，“待定的复制”）。一旦修改被复制，会标记日志为“已复制”。缓慢或延迟的日志读取活动会导致记录剩为“待 定的复制”很长时间，在此期间它们还是活动日志的一部分，因此母VLF不能被截断。对于通过变更数据捕获（ Change Data Capture (CDC)）功能需要的日志记录也有类似的问题存在。
不管任何情况，sys.databases的 log_reuse_wait_desc列会显示REPLICATION作为问题根源。在事务磁盘阵列的输出性能里，这个问题本身也暴露了瓶颈。尤其是， 在并发写加载下的延迟读取操作。写入日志文件会持续发生，但用日志读取代理相关的和日志备份文件读取的读操作也要持续的。同一时间有持续的读和写发生，取 决于系统中的日志活跃级别和活动日志部分的大小，会导致磁头随机的I/O活动，因为磁头需要改变位置来读取活动日志的头，然后活动日志的尾。我们可以使用性能监视器（PerfMon）里磁盘计数器 Physical Disk\Disk Reads/sec 和 Physical Disk\Disk Writes/sec来故障排除这类问题，看下SQL Server的故障排除的免费电子书的第2章来进一步了解这个问题的细节：https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/