Post reply

Check Statement aborted. Database contains deferred transactions.

  • July 2, 2010 at 1:04 am

    #141153

    Dear all,

    While i running the dbcc reindex, unfortunately system restart with hardware failure, after that we are unable to access the DB,

    if i check with DBCC check db gives the below error

    Msg 7929, Level 16, State 1, Line 1 Check statement aborted. Database contains deferred transactions.

    unable to open the table all so... and any front end software also..

    please tell me to come out from this problem..

  • July 2, 2010 at 1:19 am

    #1188027

    What's the state of the database? (query sys.databases)

    What's the exact version (patch, CU and hotfix) and edition of SQL? (select @@version)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 2, 2010 at 1:25 am

    #1188030

    Hi ,

    Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    DBCC OPENTRAN

    Transaction information for database 'DB'.

    Oldest active transaction:

    SPID (server process ID): 51

    UID (user ID) : -1

    Name : implicit_transaction

    LSN : (412576:18904:9)

    Start time : Jul 2 2010 4:17:57:390AM

    SID : 0x17077e53df9a7346bb0ec67e76740828

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC check db

    Msg 7929, Level 16, State 1, Line 1

    Check statement aborted. Database contains deferred transactions.

  • July 2, 2010 at 1:53 am

    #1188040

    Enterprise edition has a feature called fast recovery. It means that, after an unexpected shutdown, the database will come online after phase 2 of the restart recovery has completed, instead after phase 3 (which the other editions do). Once the DB comes online, the rollback continues until it's complete. These are deferred transactions.

    Be patient, this is a feature, not a problem, once the rollback has finished you'll be able to run checkDB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 2, 2010 at 2:25 am

    #1188059

    Thank u, now recovered but still am getting one transactions like

    Oldest active transaction:

    SPID (server process ID): 123

    UID (user ID) : -1

    Name : implicit_transaction

    LSN : (413051:16337:1)

    Start time : Jul 2 2010 1:55:32:310PM

    SID : 0x17077e53df9a7346bb0ec67e76740828

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply