Recovery of Suspect Database

I have a large database (>150 million records) that is marked "Suspect" and I would like to recover this if possible. Our IT group did an unexpected reboot of the server during a data import process implemented in multiple nested DTS packages. After the
reboot, the database was marked as Suspect.
SQL Server won't let me dbcc checkdb, restore datbase with recovery or even back up the log file (DB recovery mode is SIMPLE). After running sp_resetstatus and restarting SQL Server, a new error log and dump file are generated and the database is reset to
Suspect. A two-day-old backup is available and I can replace all the data by reimporting the missing days, but this will take days and I'm hoping to be able to recover the existing DB.
A couple of snippets from the error log are below. I also have a 18+ MB dump file. What's the best approach to fixing this?
<snip />
2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-05-04 13:51:46.54 server SQL Server is ready for client connections
2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDump0005.txt
* ************************************************** *****************************
*
* BEGIN STACK DUMP:
* 05/04/04 13:52:06 spid 11
*
<snip />
* ----
2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is 0x37EAFC8F
2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File: <pageref.cpp>, line=4454
Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 11
Process ID: 2180
2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in database 'IDD'. Error at log record ID (7480:19322:383)..
2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not recover. Contact Technical Support..
2004-05-04 13:52:42.95 spid3 Recovery complete.
Some good info can be found here.
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>
|||Yeah, I saw that site. Those steps aren't working. If I can't find a way to recover otherwise, I may use emergency mode and bcc the data out as a fail-safe before attempting a restore from backup. Not being able to even force it out of Suspect mode preclu
des a number of things I might try. Thanks Jeff! -- Scott
-- Jeff Duncan wrote: --
Some good info can be found here.
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.[vbcol=seagreen]
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.[vbcol=seagreen]
dump file. What's the best approach to fixing this?
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
><snip />>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>
|||You should open a case with PSS. This definitely sounds like it is worth
more than $250.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>