Cant open DB in emergency mode after forced attach

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Cant open DB in emergency mode after forced attach

Author

Topic

ekelmans
Starting Member

7 Posts

Posted - 2008-10-01 : 09:59:50

On a bad tuesday morning is was called out to a hospital where i do some DBA work. They managed to detach a db on one machine, and could not attach it to the new box, or even the old box.

When i forced an attachment (create dummy db, shutdown, swap for files, restart)
sql replies with:
Attached, but it's Suspect

Looking in the log i find this:

Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:17381888; actual 0:0). It occurred during a read of page (1:17381888) in database ID 6 at offset 0x00002127400000 in file 'D:\data\Ezis_EMD_49.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 2
An error occurred during recovery, preventing the database 'Ezis_EMD_49' (database ID 6) 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.

Yeah... i got scared too...

but after reading some blogs of paul randal i tried to open it in emergency mode and try to recover the one table that needed saving.

ALTER DATABASE [Ezis_EMD_49] SET EMERGENCY
And SQL replies:

Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.

But the DB is looks normal in SSMS, but the + sign is missing...

okay... now i'm confused. But lets try pauls baby: dbcc checkdb
And yes, i know full well what it does...

DBCC CHECKDB (Ezis_EMD_49, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
And Sql replies:

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

And i go: "Que?!?!?!"

the log says:
The database 'Ezis_EMD_49' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.

Back to pauls blog....

Ah... SELECT state_desc FROM sys.databases WHERE name='Ezis_EMD_49';
SqlReplies:
RECOVERY_PENDING

Well... it stands to reason; pending = waiting on something or an other...., BUT WHAT ???

ok, lets try to bring it online then:
ALTER DATABASE [Ezis_EMD_49] SET online

Msg 926, Level 14, State 1, Line 2
Database 'Ezis_EMD_49' 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 2
ALTER DATABASE statement failed.
Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:17381888; actual 0:0). It occurred during a read of page (1:17381888) in database ID 6 at offset 0x00002127400000 in file 'D:\data\Ezis_EMD_49.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 2
An error occurred during recovery, preventing the database 'Ezis_EMD_49' (database ID 6) 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.

After this i was stuck in a loop

, suggestions anyone?

Theo Ekelmans

sql info:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-07 : 09:16:04

Dig out the latest backup and restore it.

An an absolute last resort if you don't have a backup, you may try the force attach but without the log file.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources