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_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Answer 1

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_LOSS) WITH 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.

Answer 2

Thanks, Is there any way to get it back into normal mode? Please let me know.

Answer 3

I get incorrect syntax near emergency when trying to run the above script.

Answer 4

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

Answer 5

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.

Answer 6

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!

Answer 7

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/