Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Attach a suspect database Expand / Collapse
Author
Message
Posted Wednesday, February 10, 2010 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 7:09 AM
Points: 18, Visits: 83
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






Post #863241
Posted Wednesday, February 10, 2010 7:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 2,114, Visits: 5,502
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/
Post #863256
Posted Wednesday, February 10, 2010 8:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #863274
Posted Wednesday, February 10, 2010 8:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 2,040, Visits: 1,668
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
Post #863308
Posted Wednesday, February 10, 2010 8:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 3:22 PM
Points: 381, Visits: 359
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!


EnjoY!
Post #863331
Posted Wednesday, February 10, 2010 9:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 2,040, Visits: 1,668
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
Post #863349
Posted Thursday, February 11, 2010 12:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 7:09 AM
Points: 18, Visits: 83
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?
Post #863876
Posted Thursday, February 11, 2010 1:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 7:09 AM
Points: 18, Visits: 83
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
Post #863904
Posted Thursday, February 11, 2010 1:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #863912
Posted Thursday, February 11, 2010 2:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
Thanks Paul...for the article...its well described still easy to understand..
Post #863918
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse