February 10, 2010 at 7:40 am
Good day.
I have the following scenario.
We had a hardware failure on one of our drives.
had to send it in to datarecovery.
When recovered all the files, i placed drive in machine in same drive letter, All the databases recovered successfully except for one DB.
The faulty DB showed suspect. So i had to put into emergency mode to do a check db.
----------------------------------------------------------------------------------------
results of checkdb
Msg 926, Level 14, State 1, Line 1
Database 'AIMS200906' 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 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa;
actual signature: 0xaaaa88aa). It occurred during a read of page (1:1022464) in database ID 21
at offset 0x000001f3400000 in file 'F:\AIMS200906\AIMS200906.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.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'AIMS200906' (database ID 21) 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.
------------------------------------------------------
Then i ran
DBCC CHECKDB('aims200906', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS
Warning: The log for database 'AIMS200906' has been rebuilt.
Transactional consistency has been lost.
The RESTORE chain was broken, and the server no longer has context on the previous log files,
so you will need to know what they were.
You should run DBCC CHECKDB to validate physical consistency.
The database has been put in dbo-only mode.
When you are ready to make the database available for use, you will need to reset database options and
delete any extra log files.
Msg 926, Level 14, State 1, Line 1
Database 'AIMS200906' cannot be opened.
It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
DBCC results for 'AIMS200906'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AIMS200906'.
------------------------------------------------------
database was still suspect
but now if a run check db i get the following
Msg 7985, Level 16, State 2, Line 1
System table pre-checks: Object ID 4. Could not read and latch page (1:3480) with latch type SH.
Check statement terminated due to unrepairable error.
DBCC results for 'AIMS200906'.
Msg 5233, Level 16, State 98, Line 1
Table error: alloc unit ID 262144, page (1:3480). The test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. The values are 29362185 and -1.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'AIMS200906'.
According to the forums on this topic,
Seems like no repair for this.
But now i mistakenly detached the database that was in emergency mode.
How do i get the database back even though in suspect mode, so that i can extract the data that i need
when i try to reattch my database i get the following error
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'aims200906'. CREATE DATABASE is aborted.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xaaaa88aa). It occurred during a read of page (1:1022464) in database ID 21 at offset 0x000001f3400000 in file 'F:\AIMS200906\aims200906.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.
Please help??
Imtiaz
February 10, 2010 at 7:49 am
You can rename the originals database’s files. Then create a new database with the exact same name and files (name and location) as the original database. Stop the service delete the new database’s files and rename the original database’s files to their original names. Start the service again, and you’ll have the original database in its suspect mode. One very important noticed – I’ve done it once with SQL Server 2000, but I never tried it with SQL Server 2005.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 10, 2010 at 8:09 am
I wouldn't bother trying to attach it. The damage you have is not repairable in any way. Get out your backups and restore the DB from the latest backup.
The table that's damaged (object ID 4) is sysrowsetcolumns. It's one of the tables that stored data about column structure. It's not likely that you will be able to extract all the data from the DB due to this error.
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
February 10, 2010 at 8:36 am
The original page in the error message described (1:1022464) is an allocation page that the database cannot function without - this page being corrupt is the cause of the original suspect status.
What kind of IO subsystem do you have? It caused the torn-page error - see by blog under the IO Subsystems category for an explanation of what these are and how they occur.
You need to do as Gail says - restore from your backups.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 10, 2010 at 8:52 am
I had experienced same problem, fix was to put the database in emergency mode and extract all the data using SSIS package. Once you had detached database you lost that option too, Gail and Paul are right restore from old back up.
EnjoY!
February 10, 2010 at 9:05 am
No - you can usually always attach a suspect database back into SQL Server. See TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 11, 2010 at 12:21 am
I have backups for recent copies of our database.
But the copy that we had on the server which had a hardware fault was a year end version of the database.
We use yearend for special reporting purposes as at year end of the previous year. and since i dont keep backups of more than 3 weeks old i cant restore?
February 11, 2010 at 1:36 am
Thanx a million to all who helped .
You people are wonderfull.
The hacked way of attaching a suspect databases worked.
The database is still suspect , so i can put into emergency mode and access the data.
Its not a active production database and so thats okay for now.
Thanx once again and may God Bless you.
Imtiaz Mohamed
February 11, 2010 at 1:50 am
imtiazm (2/11/2010)
The database is still suspect , so i can put into emergency mode and access the data.
It's highly unlikely that you're going to get all the data back. Between the damage to the system table and the corrupt allocation page, there's a good chance that some of the data will be inaccessible even in emergency mode.
You also need to look at your backup strategy. Any database, regardless of what it is, that is used by the business must be backed up.
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
February 11, 2010 at 2:02 am
Thanks Paul...for the article...its well described still easy to understand..
September 24, 2010 at 4:46 am
imtiazm (2/11/2010)
I have backups for recent copies of our database.But the copy that we had on the server which had a hardware fault was a year end version of the database.
We use yearend for special reporting purposes as at year end of the previous year. and since i dont keep backups of more than 3 weeks old i cant restore?
I believe that this qualifies as a lesson 😉 --> if you want it...back it up
Other thing you could do is (presuming you have your data ripped to tape somewhat regularly) is request the backup you created this EOY DB from.
Carlton.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply