locked
Database Log file Corrupted while making database Readonly RRS feed

  • Question

  • we have database when trying to make read only throwing below error: with stack dump

    Location:              recovery.cpp:4517

    Expression:         m_recoveryUnit->IsIntendedUpdateable ()

    SPID:                     51

    Process ID:          6448

    Msg 926, Level 14, State 1, Line 1

    Database 'XXXX' cannot be opened. It has been marked SUSPECT by recovery.

    See the SQL Server errorlog for more information.

    Msg 5069, Level 16, State 1, Line 1

    ALTER DATABASE statement failed.

    Msg 3624, Level 20, State 1, Line 1

    A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

    Msg 3313, Level 21, State 2, Line 1

    During redoing of a logged operation in database 'XXXX', an error occurred at log record ID (0:0:0). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

    Msg 3414, Level 21, State 1, Line 1

    An error occurred during recovery, preventing the database 'XXXX' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

    Investigation DONE:

    - DBcc checkdb shown Clean 

    - database is online and able to access

    -Detached database and attached with rebuild log, still could not bring database read_only

    SQL version : SQL server 2008 SP4. 

    Wednesday, June 3, 2015 2:23 PM

Answers

  • Based on my internal research, it looks like an issue with SQL Server 2008 and its fixed in SQL Server 2012. Here is the workaround which I would suggest before marking read_only.

    use <DBName>
    
    declare @prevrowcount int = 0
    declare @rowcount int = 0
    
    select @rowcount = count(*) from sys.dm_tran_commit_table
    
    while @rowcount <> @prevrowcount
    begin
        print @rowcount
        checkpoint
        set @prevrowcount = @rowcount
        select @rowcount = count(*) from sys.dm_tran_commit_table
    end
    
    use master
    ALTER DATABASE <DBName> SET READ_ONLY 


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, June 20, 2015 3:59 PM

All replies

  •  did you do another dbcc checdb after rebuilding the log?? if not, can you do that..

    Hope it Helps!!

    Wednesday, June 3, 2015 2:33 PM
  • Hi

    If I'm understanding this topic correctly; you are trying to set an online database to Read Only.

    Is the database configured for any form of Replication?  E.g. Snapshot Replication?


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, June 3, 2015 2:37 PM
  • how are you trying to make the database readonly? are there open transactions that are holding it up?

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, June 3, 2015 3:53 PM
  • Which database version

    1)    select * from msdb.dbo.suspect_pages  see suspected pages

      share dbcc result.      

    2) you create database without log file

    http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

    also refer

    http://blog.nhaslam.com/2011/08/03/page-corruption-in-a-sql-server-database/


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Wednesday, June 3, 2015 7:41 PM
  • I guess the issue is log corruption and this is not detected completely by dbcc checkdb command. It was trying to rollforward an operation which it could not do because something in log was not consistent. I guess corruption is in active part of log

    You should follow methods to remove log corruption below link would help you

    http://sqlmag.com/blog/transaction-log-corruption-and-backups


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, June 4, 2015 7:00 AM
    Answerer
  • Hi , yes DBCC is clean post rebuild as well
    Thursday, June 4, 2015 2:22 PM
  • Yes your understanding is right i am trying to make database read_only from online.No replication configured for database
    Thursday, June 4, 2015 2:23 PM
  • no open transaction to database, database is free of connections
    Thursday, June 4, 2015 2:23 PM
  • All options mentioned in the link didnot help :(
    Thursday, June 4, 2015 2:26 PM
  • Suspect table have no rows, SQL 2008 Version.

    options mentioned in the link was already tried but had no luck.

    Thursday, June 4, 2015 2:29 PM
  • Ranjeeth, have you made any progress with this?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, June 19, 2015 9:03 AM
  • What is database mdf and log  size ?



    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Saturday, June 20, 2015 9:07 AM
  • Based on my internal research, it looks like an issue with SQL Server 2008 and its fixed in SQL Server 2012. Here is the workaround which I would suggest before marking read_only.

    use <DBName>
    
    declare @prevrowcount int = 0
    declare @rowcount int = 0
    
    select @rowcount = count(*) from sys.dm_tran_commit_table
    
    while @rowcount <> @prevrowcount
    begin
        print @rowcount
        checkpoint
        set @prevrowcount = @rowcount
        select @rowcount = count(*) from sys.dm_tran_commit_table
    end
    
    use master
    ALTER DATABASE <DBName> SET READ_ONLY 


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, June 20, 2015 3:59 PM
  • Hi - i had no progress in this rather keeping it as it is .

    as i said earlier i have no issues when i restore backup in 2012 instance.

    Wednesday, August 26, 2015 12:03 PM
  • Hi Balmukund - will try this

    Thank you.

    Wednesday, August 26, 2015 12:04 PM
  • I had to run the code twice for it to work.

    I noticed the count was around 350k then it went to around 225k then it went to 164 before it worked.

    Thanks for the info.

    Monday, June 13, 2016 5:14 PM