sqlserver中查找长时间未提交事务

sqlserver中查找长时间未提交事务

　　无论是有意无意，如果事务在数据库中保持打开，则它会阻塞其他进程对修改后的数据进行操作。同样，对事务日志进行备份也只会截断不活动事务的那部分事务日志，所以打开的事务会导致日志变多(甚至达到物理限制)，直到事务被提交或回滚。

　　要找到最早的活动事务，可以使用DBCC OPENTRAN命令。

　　给出一个示例：

　　代码如下：

　　CREATE TABLE T_Product(PKID int, PName Nvarchar(50));

　　GO

　　BEGIN TRAN

　　INSERT INTO T_Product VALUES (101, '嫦娥四号');

　　GO

　　DBCC OPENTRAN;

　　ROLLBACK TRAN;

　　GO

　　DROP TABLE T_Product;

　　GO

　　执行结果：

　　代码如下：

　　/*

　　(1 row(s) affected)

　　数据库 'Testdb' 的事务信息。

　　最早的活动事务：

　　SPID (服务器进程 ID)： 54

　　UID (用户 ID)： -1

　　名称 : user_transaction

　　LSN : (295:6687:1)

　　开始时间 : 12 24 2010 2:50:15:607PM

　　SID : 0x0105000000000005150000007fe010d31cba1ab1566ac5dff4010000

　　DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

　　*/

　　结果显示了最早活动日志的相关信息，包括服务器进程ID、用户ID、和事务的开始时间。关键是SPID和Start Time.

　　拥有这些信息后，可以使用动态管理视图(DMV)来检验正在执行的T-SQL,以及在必要时关闭这个过程

　　DBCC OPENTRAN对于孤立连接(在数据库中是打开的，但与应用程序或客户端已经断开的连接)是非常有用的，并能帮助我们找出遗漏了COMMIT或ROLLBACK的事务。该命令也返回在指定数据库内存在最早的活动事务和最早的分布式和非分布式复制事务。如果没有活动事务，则显示信息性消息，而不返回会话级数据。

　　我们看一个实例：

　　代码如下：

　　SET Transaction isolation level serializable

　　BEGIN TRAN

　　select * from T_Product

　　Insert into T_Product

　　select 'OATest' union all

　　select 'OAPlay'

　　这是一个未提交的事务，在另一个查询窗口执行如下：

　　代码如下：

　　select session_id,transaction_id,is_user_transaction,is_local

　　from sys.dm_tran_session_transactions

　　where is_user_transaction=1

　　执行结果：

　　代码如下：

　　/*返回结果

　　session_id transaction_id is_user_transaction is_local

　　54 489743 1 1

　　*/

　　返回会话ID后，可以通过sys.dm_exec_connections和sys.dm_exec_sql_text来挖掘最近执行的查询的详细信息。

　　代码如下：

　　select s.text from sys.dm_exec_connections c

　　cross apply sys.dm_exec_sql_text(c.most_recent_sql_Handle) s

　　where session_id=54

　　这个查询返回最后执行的语句。也可以使用sys.dm_exec_requests.

　　因为也从sys.dm_tran_session_transactions的第一个查询中得知事务ID,所以可以使用sys.dm_tran_active_transactions来了解更多事务本身的内容

　　代码如下：

　　select transaction_begin_time,

　　case transaction_type

　　when 1 then 'Read/Write transaction'

　　when 2 then 'Read-Only transaction'

　　when 3 then 'System transaction'

　　when 4 then 'Distributed transaction'

　　end tran_Type,

　　case transaction_state

　　when 0 then 'not been comoletely initaialiaed yet'

　　when 1 then 'initaialiaed but ha notstarted'

　　when 2 then 'active'

　　when 3 then 'ended (read-only transaction)'

　　when 4 then 'commit initiated for distributed transaction'

　　when 5 then 'transaction prepared and waiting resolution'

　　when 6 then 'commited'

　　when 7 then 'being rolled back'

　　when 0 then 'been rolled back'

　　end transaction_state

　　from

　　sys.dm_tran_active_transactions

　　where transaction_ID=455520

　　代码如下：

　　/*结果：

　　transaction_begin_time tran_Type transaction_state

　　2010-12-24 14:05:29.170 Read/Write transaction active

　　*/

　　小结：这里演示了使用DMV 排除故障和调查长时间的活动事务的一般技巧。基本步骤如下：

　　1、查询sys.dm_tran_session_transactions获取会话ID和事务ID之间的映射。

　　2、查询sys.dm_exec_connections和sys.dm_exec_sql_text查找会话最新执行的命令(most_recent_sql_Handle列)

　　3、最后，查询sys.dm_tran_active_transactions确定事务被打开了多少时间、事务的类型和事务的状态。

　　使用这个技巧可以回到应用程序去查明调用的被抛弃的事务(打开但从未提交)以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。