none
REPAIR_ALLOW_DATA_LOSS

    Question

  • Hi,

     

    AM trying to run this command on a SQL 2000 database but getting the error below. What are my options now?

     

    Database 'DatabaseNAme' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.


    DBCC CHECKDB (Databse, REPAIR_ALLOW_DATA_LOSSWITH NO_INFOMSGS, ALL_ERRORMSGS;

    Tuesday, October 19, 2010 3:24 PM

Answers

  • I agree with what SQLCraftsman said, but just in case

    when i say ALTER DATABASE DB_NAME, I meant you to replace it with your database name. Can you also post the exact message that you are getting..


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.


    ALTER DATABASE isn't used for this in SQL Server 2000, you have to update the system tables manually to change the database status. 

    IF YOU USE THIS CODE YOU CAN BREAK YOUR SERVER/DATABASE.  ITS USE IS AT YOUR OWN RISK!!

    use [master]
    go
    execute sp_configure 'allow updates',1
    reconfigure with override
    go 
    update sysdatabases 
    set status = 32768 
    where name = '<database name>'
    go
    

    At this point restart SQL and the database will come online in Emergency Mode.  Run DBCC CHECKDB without using REPAIR_ALLOW_DATA_LOSS first.  You need to know what you are potentially losing before you just decide to run a command that will cause you to lose information.

    To bring the database back online after doing whatever you do:

    -- Bring database back online
    use [master]
    go
    update sysdatabases 
    set status = 0 
    where name = '<database name>'
    go
    execute sp_configure 'allow updates',0
    reconfigure with override
    

    Why don't you have a backup of the database to recover from?  Using REPAIR_ALLOW_DATA_LOSS is a last ditch effort to recover from a problem.  Backups should always be used first.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, October 19, 2010 6:01 PM
    Moderator

All replies

  • Try running this,

    ALTER DATABASE DB_NAME SET EMERGENCY;
    GO
    ALTER DATABASE DB_NAME SET SINGLE_USER;
    

    and then run

    DBCC CHECKDB (Databse, REPAIR_ALLOW_DATA_LOSSWITH NO_INFOMSGS, ALL_ERRORMSGS

    The reason you are getting this error is, becuase your database is marked as suspect and you cannot get into the database. The best bet is to set your db status to emergency and try to run the check.


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, October 19, 2010 4:22 PM
  • Thanks, Is there any way to get it back into normal mode? Please let me know.
    Tuesday, October 19, 2010 4:37 PM
  • I get incorrect syntax near emergency when trying to run the above script.
    Tuesday, October 19, 2010 4:43 PM
  • First, you have to figure out why the database was marked SUSPECT.  It could be as simple as some of the data files were unavailable during SQL startup.  It could be due to major database corruption.  The action to fix one breaks the other and vice versa.  Don't run anything until you understand what the goal is and why you are running it.

    Check the SQL and applicaiton logs to see why your system decided the database is SUSPECT.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Tuesday, October 19, 2010 4:45 PM
    Moderator
  • I agree with what SQLCraftsman said, but just in case

    when i say ALTER DATABASE DB_NAME, I meant you to replace it with your database name. Can you also post the exact message that you are getting..


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, October 19, 2010 5:39 PM
  • I agree with what SQLCraftsman said, but just in case

    when i say ALTER DATABASE DB_NAME, I meant you to replace it with your database name. Can you also post the exact message that you are getting..


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.


    ALTER DATABASE isn't used for this in SQL Server 2000, you have to update the system tables manually to change the database status. 

    IF YOU USE THIS CODE YOU CAN BREAK YOUR SERVER/DATABASE.  ITS USE IS AT YOUR OWN RISK!!

    use [master]
    go
    execute sp_configure 'allow updates',1
    reconfigure with override
    go 
    update sysdatabases 
    set status = 32768 
    where name = '<database name>'
    go
    

    At this point restart SQL and the database will come online in Emergency Mode.  Run DBCC CHECKDB without using REPAIR_ALLOW_DATA_LOSS first.  You need to know what you are potentially losing before you just decide to run a command that will cause you to lose information.

    To bring the database back online after doing whatever you do:

    -- Bring database back online
    use [master]
    go
    update sysdatabases 
    set status = 0 
    where name = '<database name>'
    go
    execute sp_configure 'allow updates',0
    reconfigure with override
    

    Why don't you have a backup of the database to recover from?  Using REPAIR_ALLOW_DATA_LOSS is a last ditch effort to recover from a problem.  Backups should always be used first.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, October 19, 2010 6:01 PM
    Moderator
  • 32768 will mark it as emergency the moment its set ....no need to restart SQL Server ...you can cross check the status by refreshing the databases .....

    @SAM : Once the repair is done (In 2000 as well as 2005 <not sure about 2008 though> you might have to run repair a few time if you see the repair is showing you different numbers for allocation and consistency errors...so do not give up hope if it does not comes out clean at first time ...)

    @SAM : Once the repair is done you will see that your database will show you the status of DBO USE ONLY ...you can do

    alter database <dbname> set read_write


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, October 20, 2010 5:52 AM