none
如何解释这个现象。 RRS feed

  • 问题

  • 发现一个数据库的日志文件达到33 GB。

    执行
    select log_reuse_wait_desc from sys.databases where name='dbname'
    返回的结果为ACTIVE_TRANSACTION

    但是执行下面却有返回
    USE DBName
    go
    DBCC OPENTRAN()
    返回结果集为
    No active open transactions.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    大家如何看待这个问题呢。

    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2013年10月26日 8:44

答案

  • 我觉得可以查一下session,虽然效率低了一点

    SELECT  s.text
    FROM    sys.dm_exec_connections c
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_Handle) s

    --SQLSERVER中查找长时间未提交事务   
    --http://wenku.baidu.com/view/12b7a7dff61fb7360b4c659e.html
     
    --无论是有意无意,如果事务在数据库中保持打开,则它会阻塞其他进程对修改后的数据进行操作。
    --同样,对事务日志进行备份也只会截断不活动事务的那部分事务日志,
    --所以打开的事务会导致日志变多(甚至达到物理限制),直到事务被提交或回滚。
     
    --要找到最早的活动事务,可以使用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 initaialied yet'
              WHEN 1 THEN 'initaialiaed but have not started'
              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确定事务被打开了多少时间、事务的类型和事务的状态。
    --使用这个技巧可以回到应用程序去查明调用的被抛弃的事务(打开但从未提交)
    --以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。
    

    2013年10月27日 5:18

全部回复

  • 可用空间还有多少?

    Please Mark As Answer if it is helpful.

    2013年10月26日 12:56
  • What's db recovery model?
    2013年10月26日 21:02
  • 我觉得可以查一下session,虽然效率低了一点

    SELECT  s.text
    FROM    sys.dm_exec_connections c
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_Handle) s

    --SQLSERVER中查找长时间未提交事务   
    --http://wenku.baidu.com/view/12b7a7dff61fb7360b4c659e.html
     
    --无论是有意无意,如果事务在数据库中保持打开,则它会阻塞其他进程对修改后的数据进行操作。
    --同样,对事务日志进行备份也只会截断不活动事务的那部分事务日志,
    --所以打开的事务会导致日志变多(甚至达到物理限制),直到事务被提交或回滚。
     
    --要找到最早的活动事务,可以使用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 initaialied yet'
              WHEN 1 THEN 'initaialiaed but have not started'
              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确定事务被打开了多少时间、事务的类型和事务的状态。
    --使用这个技巧可以回到应用程序去查明调用的被抛弃的事务(打开但从未提交)
    --以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。
    

    2013年10月27日 5:18
  • 有没有replication, mirror, log shipping之类的? 另外,你shrinkfile收缩不下来是吧?

    想不想时已是想,不如不想都不想。

    2013年10月28日 14:37
    版主
  • 有没有replication, mirror, log shipping之类的? 另外,你shrinkfile收缩不下来是吧?

    想不想时已是想,不如不想都不想。

    没有replication,mirror或者log shipping。

    的确是不能shrink下来。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2013年10月29日 1:44
  • 可用空间还有多少?

    Please Mark As Answer if it is helpful.

    Log File提示可以收缩。

    但是就是不能收缩。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2013年10月29日 1:44
  • What's db recovery model?
    simple

    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2013年10月29日 1:45
  • 我觉得可以查一下session,虽然效率低了一点

    SELECT  s.text
    FROM    sys.dm_exec_connections c
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_Handle) s

    --SQLSERVER中查找长时间未提交事务   
    --http://wenku.baidu.com/view/12b7a7dff61fb7360b4c659e.html
     
    --无论是有意无意,如果事务在数据库中保持打开,则它会阻塞其他进程对修改后的数据进行操作。
    --同样,对事务日志进行备份也只会截断不活动事务的那部分事务日志,
    --所以打开的事务会导致日志变多(甚至达到物理限制),直到事务被提交或回滚。
     
    --要找到最早的活动事务,可以使用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 initaialied yet'
              WHEN 1 THEN 'initaialiaed but have not started'
              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确定事务被打开了多少时间、事务的类型和事务的状态。
    --使用这个技巧可以回到应用程序去查明调用的被抛弃的事务(打开但从未提交)
    --以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。

    我用了两种方法来确认这个DB里没有活动的事务。

    第一:

    use dbname

    go

    dbcc opentran()

    第二:

    select * from sys.sysprocesses where open_tran<>0

    通过这两种方法都没发现这个数据库下有active transaction


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2013年10月29日 1:47
  • 用DBCC  LOGINFO 看一下虚拟日志的情况,有多少是Active的。

    Please Mark As Answer if it is helpful.

    2013年10月29日 1:57
  • Again, what's recovery model? How do you backup it?
    2013年10月29日 2:11
  • Again, what's recovery model? How do you backup it?

    rmiao大侠,我在上面已经回复你了。

    是simple模式。

    备份,采用了完全备份+差异备份。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2013年10月29日 7:34
  • What's log usage? Saw similar issue before in sql2k but not on newer versions.
    2013年10月29日 13:25
  • 有时候会有孤儿事务的存在。

    查看sys.dm_exec_requests看看。

    简单的方法是restart一下,如果可以停服务的话。


    想不想时已是想,不如不想都不想。

    2013年11月3日 5:44
    版主
  • TRY checking the log_reuse_wait_desc  after running the CHECKPOINT manually.



    Please click the Mark as Answer button if a post solves your problem!


    2013年11月5日 6:59