SQL Server中已满事务日志原因的检测

对于SQL Server数据库管理员来讲，已满事务日志是一个琐碎的，但又很常见的问题。它能引发事务的提前终止，甚至通过阻止所有事务的引入，从而引起系统的崩溃。对于数据库管理员来说，关键是理解将要发生的情况，以便他们可以追踪引起问题的原因。 事务日志填充方式

　　以下是一些可能引起事务日志填满的原因：

　　填满的，细节的，或者没有在已满恢复模式下进行的日志备份，都会引起日志逐渐地填充。

　　进程中有活动的备份(备份被作为事务一样来处理)，它会填充部分日志，而事务将填充剩余部分。

　　长时间运行的活动事务，例如从来都不会产生提交的SPID，以及暂停或高速运行数据库镜像都会引发延迟。前者会引起事务不发送，如果在发送事务到镜像服务器之后的较长时间内，才进行高速运行，则后者才会发生。

　　对于事务复制，如果复制延迟或失败，事务日志将不会被清除，因为除非事务与日志都提交并发送至分布式数据库，否则事务都不能被清除。

　　如果进程中有一个数据库快照，当它创建时，所有的事务都堆积在它后边。

　　简单的响应方法

　　需要解决已满事务日志的问题时，你可以从以下几个选择入手：

　　·你可以执行备份来消减日志。事务日志备份是最快的，但也可能是最慢的，这取决于系统性能以及日志的大小。通常不推荐填满的或细节的备份，这取决于在大小合适的系统中完成这些备份所需的时间。

　　·你可以向数据库中添加额外的事务日志文件并执行以上备份方法中的一种。当你在进行必要的备份时， 额外的事务日志文件的添加可以为你赢得额外的时间。当然，它也可在稍后被删除。

　　·你可以将数据库的模式切换到简单恢复模式，它将自动清除日志。但要记住，你将会丢失自最近一次已满的/细节的事务日志备份之前的事务历史。

　　·正在填充日志的活动事务可以连同一些系统进程被终止，以至不会被重新执行并填充日志。当问题源被追踪到，它将提供一些缓解，但它不应该被考虑为一种解决方案。

　　·在查找系统缓慢的原因时，数据库镜像/复制可以关闭。

　　根本原因的检测

　　当微软的人员在讨论减少已满事务日志的问题时，他们经常从问题的返回信息来解决问题。微软通常不会帮助你学习如何找出并解决代码问题，虽然这些代码问题是潜在的根源。

　　我们假定在同一数据库中运行两个事务.m1和n2：

　　 Transaction 1

　　 begin tran m1

　　 update tbl

　　 set f1 = f1 + f1

　　 update tbl

　　 set f1 = f1 + f1

　　 update tbl

　　 set f1 = f1 + f1

　　 -- rollback tran m1

　　 Transaction 2

　　 begin tran n2

　　 update tblm

　　 set txtval = Convert ( varchar ( 4000 )， txtval + txtval)

　　 update tblm

　　 set txtval = Convert ( varchar ( 4000 )， txtval + txtval)

　　 update tblm

　　 set txtval = Convertvarchar( 4000 )， txtval + txtval)

　　 -- rollback tran n2

　　首先，我想让大家注意一下以上事务的名字(m1和n2)。你会发现，如果你为事务命名，在系统中追踪，以及在查找问题原因时会变得更容易。其可读性也会更好。注意，我没有提交或回滚事务——你在应用程序中控制事务时也会领会这个作用。

　　执行以下SQL脚本，它会返回当前活动事务的列表：

　　 select * from sys.dm_tran_active_transactions

　　结果集

　　我们当前运行的两个事务连同它们相应的名称一起出现在列表中，这使得它们很容易就被识别出来。当事务开始时，可以很容易地从屏幕上将它们区别出来，所以你可以了解到它是否过期以及应用程序，调度等在没有提交或回滚时是否被允许挂起。在事务类型列中，1表示：“读/写”，2表示“只读”，3表示“系统”，4表示“分布式”。通常，填充日志的事务是1，但那取决于日志来源的填充。如果日志由用户进程填充，它们将是类型1。

　　sys.dm_tran_session_transactions—将活动事务列表中的事务ID与活动的SPID联系起来。

　　sys.dm_tran_database_transactions—如果你只想查看一个数据库，它会只把与事相关的那个数据库列出来。它也会把声明/状态列出来。数据库事务状态：1、未初始化，3、已初始化，但没有日志记录，4、产生了日志记录，5、事务准备，10、事务提交，11、事务回滚，12、事务处于即将提交的进程中。

　　由于已满事务日志归因于消耗空间的活动日志实体，因此只有状态4或状态12会消耗日志空间。

　　列database_transaction_log_record_count显示将要读取的日志记录，也会显示正在等待被复制的日志记录。Database_transaction_log_bytes_used显示当前使用了多少空间，而Database_transaction_log_bytes_reserved则显示使用提交的事务预留空间(在这种情况下，预留的空间大小比实际使用的更重要)。

　　事务诊断T-SQL脚本—事务不必保持活动状态，只要打开它就行了。

　　以下T-SQL脚本可以在你所关注的数据库中执行。它将向你提供以下内容：

　　占据数据库空间的事务。

　　初始触发事务的T-SQL脚本。

　　事务及事务中系统使用的大小(兆字节和字节)。

　　事务的当前声明或状态。

　　连同日志编号的日志记录条数。

　　即使请求还没有被执行，此脚本仍可在事务打开后的任意时刻执行。

　　 CREATE TABLE #Tmp_Transaction(

　　ID int identity ( 1 ， 1 )，

　　 [ TransactionName ] [ nvarchar ] ( 32 ) NOT NULL ，

　　 [ transaction_id ] [ bigint ] NOT NULL ，

　　 [ transaction_begin_time ] [ datetime ] NOT NULL ，

　　 [ transaction_type ] [ int ] NOT NULL ，

　　 [ transaction_state ] [ int ] NOT NULL ，

　　 [ session_id ] [ int ] NOT NULL ，

　　 [ TranLog_MB_Used ] [ bigint ] NULL ，

　　 [ TranLog_MB_Reserved ] [ bigint ] NULL ，

　　 [ TranLogSys_MB_Used ] [ int ] NULL ，

　　 [ TranLogSys_MB_Reserved ] [ int ] NULL ，

　　 [ database_transaction_type ] [ int ] NOT NULL ，

　　 [ database_transaction_state ] [ int ] NOT NULL ，

　　 [ database_transaction_status ] [ int ] NOT NULL ，

　　 [ database_transaction_status2 ] [ int ] NOT NULL ，

　　 [ database_transaction_log_record_count ] [ bigint ] NOT NULL ，

　　 [ database_transaction_replicate_record_count ] [ int ] NOT NULL ，

　　 [ database_transaction_log_bytes_used ] [ bigint ] NOT NULL ，

　　 [ database_transaction_log_bytes_reserved ] [ bigint ] NOT NULL ，

　　 [ database_transaction_log_bytes_used_system ] [ int ] NOT NULL ，

　　 [ database_transaction_log_bytes_reserved_system ] [ int ] NOT NULL ，

　　 [ database_transaction_begin_lsn ] [ numeric ] ( 25 ， 0 ) NULL ，

　　 [ database_transaction_last_lsn ] [ numeric ] ( 25 ， 0 ) NULL ，

　　 [ database_transaction_most_recent_savepoint_lsn ] [ numeric ] ( 25 ， 0 ) NULL ，

　　 [ database_transaction_commit_lsn ] [ numeric ] ( 25 ， 0 ) NULL ，

　　 [ database_transaction_last_rollback_lsn ] [ numeric ] ( 25 ， 0 ) NULL ，

　　 [ database_transaction_next_undo_lsn ] [ numeric ] ( 25 ， 0 ) NULL ，

　　EventInfo nvarchar ( Max )

　　 CREATE TABLE #inputb (EventType nvarchar ( Max )， Parameters int ， EventInfo nvarchar Max )) -- hold buffer

　　 declare @iRwCnt int

　　 declare @i int

　　 declare @iSPID int

　　 declare @vSPID varchar ( 4 )

　　 set @i = 1

　　 insert into #Tmp_Transaction(TransactionName， transaction_id，

　　transaction_begin_time， transaction_type， transaction_state， session_id，

　　TranLog_MB_Used， TranLog_MB_Reserved， TranLogSys_MB_Used， TranLogSys_MB_Reserved，

　　database_transaction_type， database_transaction_state， database_transaction_

　　status，

　　database_transaction_status2， database_transaction_log_record_count，

　　database_transaction_replicate_record_count， database_transaction_log_bytes_used，

　　database_transaction_log_bytes_reserved，

　　database_transaction_log_bytes_used_system，

　　database_transaction_log_bytes_reserved_system， database_transaction_begin_lsn，

　　database_transaction_last_lsn， database_transaction_most_recent_savepoint_lsn，

　　database_transaction_commit_lsn， database_transaction_last_rollback_lsn，

　　database_transaction_next_undo_lsn)

　　 select at.name [ TransactionName ] ， at.transaction_id， at.transaction_begin_time，

　　at.transaction_type， at.transaction_state， st.session_id，

　　(dt.database_transaction_log_bytes_used / 1048576 ) [ TranLog_MB_Used ] ，

　　(dt.database_transaction_log_bytes_reserved / 1048576 ) [ TranLog_MB_Reserved ] ，

　　(dt.database_transaction_log_bytes_used_system / 1048576 ) [ TranLogSys_MB_Used ] ，

　　(dt.database_transaction_log_bytes_reserved_system / 1048576 )

　　 [ TranLogSys_MB_Reserved ] ，

　　dt. [ database_transaction_type ] ， dt. [ database_transaction_state ] ，

　　dt. [ database_transaction_status ] ， dt. [ database_transaction_status2 ] ，

　　dt. [ database_transaction_log_record_count ] ，

　　dt. [ database_transaction_replicate_record_count ] ，

　　dt. [ database_transaction_log_bytes_used ] ，

　　dt. [ database_transaction_log_bytes_reserved ] ，

　　dt. [ database_transaction_log_bytes_used_system ] ，

　　dt. [ database_transaction_log_bytes_reserved_system ] ，

　　dt. [ database_transaction_begin_lsn ] ，

　　dt. [ database_transaction_last_lsn ] ，

　　dt. [ database_transaction_most_recent_savepoint_lsn ] ，

　　dt. [ database_transaction_commit_lsn ] ， dt. [ database_transaction_last_rollback_

　　lsn ] ，

　　dt. [ database_transaction_next_undo_lsn ]

　　 from sys.dm_tran_active_transactions at

　　 inner join sys.dm_tran_session_transactions st on at.transaction_id =

　　st.transaction_id

　　 inner join sys.dm_tran_database_transactions dt on at.transaction_id =

　　dt.transaction_id

　　 where dt.database_id = DB_ID () and dt.database_transaction_state in ( 4 ， 12 ) and

　　st.is_user_transaction = 1

　　 set @iRwCnt = @@ROWCOUNT

　　 while @i <= @iRwCnt

　　 begin

　　 select @iSPID = t.session_id from #Tmp_Transaction t where t.ID = @i

　　 set @vSPID = Convert ( varchar ， @iSPID )

　　 truncate table #inputb

　　 INSERT #inputb EXEC ( ' DBCC INPUTBUFFER ( ' + @vSPID + ' ) WITH NO_INFOMSGS '

　　)

　　 update t

　　 set t.EventInfo = select top 1 EventInfo from #inputb)

　　 from #Tmp_Transaction t

　　 where t.ID = @i

　　 set @i = @i + 1

　　 end

　　 select TransactionName， transaction_id， transaction_begin_time， transaction_type，

　　transaction_state， session_id， TranLog_MB_Used， TranLog_MB_Reserved，

　　TranLogSys_MB_Used， TranLogSys_MB_Reserved， EventInfo， database_transaction_type，

　　database_transaction_state， database_transaction_status，

　　database_transaction_status2， database_transaction_log_record_count，

　　database_transaction_replicate_record_count， database_transaction_log_bytes_used，

　　database_transaction_log_bytes_reserved，

　　database_transaction_log_bytes_used_system，

　　database_transaction_log_bytes_reserved_system， database_transaction_begin_lsn，

　　database_transaction_last_lsn， database_transaction_most_recent_savepoint_lsn，

　　database_transaction_commit_lsn， database_transaction_last_rollback_lsn，

　　database_transaction_next_undo_lsn

　　 from #Tmp_Transaction

　　 drop table #Tmp_Transaction

　　 drop table #inputb

　　事务T-SQL诊断—事务必须激活执行

　　下一部分代码是基于以上T-SQ脚本来编写的，目的是提供一个完整的图像。基本上讲，它将为你提供关于动态执行事务的相关信息：

　　·初始化T-SQL调用的相关信息。

　　·当前正在执行的初始化T-SQL调用的潜在信息。

　　·当前状态/开始时间，完成百分比。

　　 SELECT st.Session_id， req.Blocking_Session_ID [ Blocker ] ， req.Wait_Type，

　　req.Wait_Time [ WaitTimeMS ] ， req.Wait_Resource， req.open_transaction_count，

　　req.percent_complete， dt.transaction_id， dt.database_transaction_begin_time， case

　　 when dt.database_transaction_type = 1 then ' RW ' when dt.database_transaction_type =

　　 2 then ' R ' whendt.database_transaction_type = 3 then ' Sys ' else ' Unknown ' end

　　 [ TranType ] ，

　　 case when dt.database_transaction_state = 1 then ' Not Initialized ' when

　　dt.database_transaction_state = 3 then ' Initialized， but no logs ' when

　　dt.database_transaction_state = 4 then ' Generated logs ' when

　　dt.database_transaction_state = 5 then ' Prepared ' when

　　dt.database_transaction_state = 10 then ' Committed ' when

　　dt.database_transaction_state = 11 then ' Rolled Back ' when

　　dt.database_transaction_state = 12 then ' In process of committing ' else ' Unknown '

　　 end [ TranState ] ，

　　req. Status， req.Command， stxt.objectid [ ExecObjID ] ，

　　( SUBSTRING (stxt. text ，req.statement_start_offset / 2 ，( CASE WHEN

　　req.statement_end_offset = - 1 then LEN ( CONVERT ( nvarchar ( max )， stxt. text )) * 2 ELSE

　　req.statement_end_offset end - req.statement_start_offset)

　　 / 2 )) [ SubText ] ， stxt. text ，

　　req.statement_start_offset

　　 FROM sys.dm_tran_database_transactions dt nolock)

　　 inner join sys.dm_tran_session_transactions st nolock) on dt.transaction_id =

　　st.transaction_id

　　 inner join sys.dm_exec_requests req (nolock) on st.transaction_id =

　　req.transaction_id

　　 CROSS APPLY sys.dm_exec_sql_text(req. sql_handle) [ stxt ]

　　 where dt.database_id = db_id () and st.is_user_transaction = 1

　　以上为你提供了快速解决事务日志问题的相关知识与工具。既然我们已经讨论了如何检测事务日志填充的来源，我希望你可以更有效地利用事务日志来帮助其他人。