Suspect Database Recovery (SQL Server 2005)

Question

I have a SQL Serever 2005 database that is marked Suspect.  I executed the following in an attempt to recover the DB:

EXEC sp_resetstatus 'MPV.NET';
ALTER DATABASE "MPV.NET" SET EMERGENCY
DBCC checkdb('MPV.NET')
ALTER DATABASE "MPV.NET" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('MPV.NET', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE "MPV.NET" SET MULTI_USER

The messages returned were:

The suspect flag on the database "MPV.NET" is already reset.
Msg 922, Level 14, State 1, Line 3
Database 'MPV.NET' is being recovered. Waiting until recovery is finished.
Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x5555555a). It occurred during a read of page (1:15) in database ID 7 at offset 0x0000000001e000 in file 'D:\Data\SQL Server\MPV.NET.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

I could REALLY use some quick insights on how to get the database back.  Some data loss is acceptable.  

Thanks in advance…

Answer 1

Hi,

Please confirm what the current state of the database is.  SUSPECT or RECOVERY PENDING - you can run: select state_desc from sys.databases where name = 'MPV.NET'.  What does it say if you try to set it online?

Please post the full output of the DBCC CHECKDB repair_allow_data_loss you ran.

Your safest option is to restore from backups.  Secondly, put it back in emergency mode, which will give you a read-only view of your transactionally inconsistent data, and get it out from there.

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

It is always good Practise is that to restore from the latest full Backup, because if you use the repair allow data loss option you cannot guarntee that

where will be the data will be lossing and you have to keep on repeat untill the issue fixed, so better to do from the latest backup to recover the data

Incase if you donot have the Backup and it will be fine for you to loss the data then you can go thorugh it using your option-

Refer ->Emergency mode repair

http://www.sqlskills.com/BLOGS/PAUL/category/Corruption.aspx

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 3

Thanks for the quick reply, Andrew.

The state_desc is RECOVERY_PENDING and the page_verify_option_desc is TORN_PAGE_DETECTION

When I run it now, the entire output from: DBCC CheckDB ('MPV.NET', REPAIR_ALLOW_DATA_LOSS)

Msg 922, Level 14, State 1, Line 1
Database 'MPV.NET' is being recovered. Waiting until recovery is finished.

I would prefer to recover from backup, however, I am being brought into this at the end.  At this point, it appears no backup is available, but they are still looking.

Thanks again...

---

Phil Christopher

Answer 4

The database is in recovery pending status, so you have to await until it gets finished.

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 5

sorry - I meant to put the database back in emergency mode and run the DBCC CHECKDB.  Recovery Pending means that something is preventing recovery from starting.

http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-4-Using-EMERGENCY-mode-to-access-a-RECOVERY-PENDING-or-SUSPECT-database.aspx

http://msdn.microsoft.com/en-us/library/ms190442.aspx

RECOVERY PENDING

SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 6

I attempted to recover yesterday and the same status was raised.  After the recovery completed the DB was still suspect.  So, I tried to recover again today and the recovery pending status was set again.

---

Phil Christopher

Answer 7

As far as I know, if the Emergency Mode repair fails, you have very limited options.

1) Find that backup and restore it (preferred)

2) Contact Microsoft Product Support

3) Whilst in Emergency Mode, you could get the data out as you have a read-only view of the data.

Would still be interested to see the full output from the DBCC CHECKDB ('mpv.bet', repair_allow_data_loss) command you ran whilst in emergency mode.

Also, any relevent SQL Server error log entries?

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 8

Suspect cases in saved SQL server database cause corruption or damaged in stored database of SQL server. But there is always a solution to overcome all the situations as they can be removed via Running some DBCC Commands or by using some tool. Run Check DBCC Command for getting the type of error causing suspect case in database. 

But exact solution to the problem can be resolved only using a third party application manager, you can get more info about the SQL recovery or tool to recover SQL server database: SQL Suspect Recovery

Answer 9

In case of corruption, it is good to restore database from most recent valid backup.