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

日期： 2009-06-28 作者：Matthew Schroeder翻译：张峰 来源：TechTarget中国 英文

即使请求还没有被执行，此脚本仍可在事务打开后的任意时刻执行。 　　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_t……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

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

　　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脚本来编写的，目的是提供一个完整的图像。基本上讲，它将为你提供关于动态执行事务的相关信息：

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

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

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

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

作者

Matthew Schroeder
Matthew Schroeder

Matt在SQL Server和Oracle这两个领域具有12年的经验。他获得微软MCITP认证、是一名数据库开发人员，他还获得了计算机科学专业硕士学位是SQL Server数据库系统高级软件工程师，范围从2 GB到3+ TB、2k和40+ktrans/sec之间。目前他任职于IGT公司，同样是一名独立的咨询师、专攻覆盖自动化、电子商务、娱乐和银行业的SQL Server、Oracle以及.NET方面。Matt擅长OLTP/OLAP数据库管理系统以及用.NET语言写可升级的处理系统。

翻译

张峰
张峰

相关推荐