SQL Server 2008中通过DBCC OPENTRAN和会话查询事务

 SQL Server 2008中SQL应用系列--目录索引

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

要找到最早的活动事务，可以使用DBCC OPENTRAN命令。详细用法见MSDN:http://msdn.microsoft.com/zh-cn/library/ms182792.aspx

给出一个示例：

    
    

     
     
     
     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确定事务被打开了多少时间、事务的类型和事务的状态。
使用这个技巧可以回到应用程序去查明调用的被抛弃的事务（打开但从未提交）以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。

感谢  http://www.cnblogs.com/downmoon/archive/2010/12/24/1912770.html