Record Locks: suspect

Showing posts with label suspect. Show all posts

Friday, March 30, 2012

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.
>

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 re
boot of the server during a data import process implemented in multiple nest
ed 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_resetsta
tus and restarting SQL Server, a new error log and dump file are generated a
nd 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% c
omplete (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\SQ
LDump0005.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 dat
abase '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 re
cover. 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/in..._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-s
afe 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/in..._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.
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?[vbcol=seagreen]
> 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.
>|||Hi Scott
Did you manage to sort this problem out, because I have the same problem.
SQL Error:
2004-05-10 08:41:31.07 spid10 Error: 3313, Severity: 21, State: 2
2004-05-10 08:41:31.07 spid10 Error while redoing logged operation in data
base 'CBPRD'. Error at log record ID (90312:130277:138)..
2004-05-10 08:41:35.21 spid7 Using 'xpstar.dll' version '2000.28.09' to e
xecute extended stored procedure 'xp_regread'.
2004-05-10 08:42:01.78 spid10 Your transaction (process ID #10) was deadlo
cked with another process and has been chosen as the deadlock victim. Rerun
your transaction.
2004-05-10 08:42:01.78 spid10 Your transaction (process ID #10) was deadlo
cked with another process and has been chosen as the deadlock victim. Rerun
your transaction.
2004-05-10 08:42:01.78 spid10 Error: 3413, Severity: 21, State: 1
2004-05-10 08:42:01.78 spid10 Database ID 8. Could not mark database as su
spect. Getnext NC scan on sysdatabases.dbid failed..
2004-05-10 08:42:01.78 spid10 Process 10 unlocking unowned resource: KEY:
1:30:2 (df0082738c65)
2004-05-10 08:42:01.78 spid10 Error: 1203, Severity: 20, State: 1
2004-05-10 08:42:01.78 spid10 Process ID 10 attempting to unlock unowned r
esource KEY: 1:30:2 (df0082738c65)..
2004-05-10 08:42:01.78 server Using 'sqlimage.dll' version '4.0.5'
Stack Dump being sent to F:\MSSQL7\log\SQL00012.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 05/10/04 08:42:02 spid 0
*
* Exception Address = 77F1D642 (RaiseException + 6a)
* Exception Code = 400042ac P
****************************************
************************************
***
----
--
Short Stack Dump
0x77f1d642 Module(KERNEL32+1d642) (RaiseException+6a)
0x006fe3f0 Module(sqlservr+2fe3f0) (stackTraceException+53)
0x006fe3ad Module(sqlservr+2fe3ad) (stackTrace+255)
0x0078d41e Module(sqlservr+38d41e) (utassert_fail+1a0)
0x005bc618 Module(sqlservr+1bc618) (ExecutionContext::Cleanup+9e)
0x00501daf Module(sqlservr+101daf) (PSS::~PSS+111)
0x00501c9b Module(sqlservr+101c9b) (destroyPssMemory+19)
0x00501a9c Module(sqlservr+101a9c) (freepss+132)
0x004d2f0a Module(sqlservr+d2f0a) (StartDBsInParallel+1b0)
0x41092a60 Module(ums+2a60) (ProcessWorkRequests+102)
0x41093316 Module(ums+3316) (ThreadStartRoutine+139)
0x7800b995 Module(MSVCRT+b995) (beginthread+ce)
0x77f04ef0 Module(KERNEL32+4ef0) (lstrcmpiW+be)
----
--
Dump thread - spid = 1, PSS = 0x411da084, EC = 0x411da23c
Stack Dump being sent to F:\MSSQL7\log\SQL00012.dmp
Please advise'

Friday, March 23, 2012

Recovering DBs with a "Suspect" state

Hello
(SQL Server 2000)
I have databases that appear as "Suspect" in the Enteprise Manager. What can I do to recover them or put them in a normal state?
Thanks a lot.Depends on why it is suspect. If you are lucky, someone simply moved the database files, and you just beed to move them back. if you have average luck, then you are looking at a database restore. What does the errorlog say?|||before you decide what to do, you ought to determine the cause.

but you can either...

a. you might be able to put your database in emergency mode and extract your data using bcp or dts.

or

b. restore from your last good backup.

I practice full recovery and can recover to any point in time, so I usually go with b and I have never done a, but I hear it is possible in some situations.

Wednesday, March 21, 2012

Recover SQL Server v8 database on 2005?

I have managed to attach a database to my new instance of SQL Server 2005. The database orignally went into suspect mode on SQL Server v8.0. I have managed to get it into emergency mode but cannot see any tables. DBCC CHECKDB etc do not work. I get the error saying to upgrade the database to the newest version.

Any ideas? Thanks in advance!

What is the error you get that causes it to go suspect?

Try ALTER DATABASE <dbname> SET ONLINE to see the error.

|||

Part of attaching a database from an older version of SQL on a newer instance involves making any required alterations to the database structure needed by the new version.

If the database was suspect on SQL 2000 before it was detached, that may well prevent those alterations from happening.

Why are you trying to attach this database to a newer version of SQL instead of resolving the suspect status before moving it?

|||The mdf and ldf files were recovered from a failing RAID array before the whole thing died. So there is not actually a way to attempt a repair on the original system.
|||Kevin,
Will attaching these data files to a SQL Server 2000 make difference , could be a different hardware

Thanks
Vishal|||I agree, I would try and get the database back to a SQL2K instance, fix your problems there and then move to 2005.|||

Vishal Gandhi wrote:
Kevin,
Will attaching these data files to a SQL Server 2000 make difference , could be a different hardware
Thanks
Vishal

Yes, because the SQL 2000 instance will not need to perform the upgrade step prior to bringing the database online.

|||Hi,

If you used sp_resetstatus to make the db status to emergency mode, then you should restore the db using the latest backup available.

Recover SQL Database from suspect status

Hi I'm trying to recover database from a suspect status but when I run this
command:
sp_resetstatus webc
sql return me this message:
Prior to updating sysdatabases entry for database 'webc', mode = 0 and
status = 1073741840 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made.
What is it= What can I do to recover DB?
Thanks.
--
--
Filippo MacchiHi
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made.
Have you tried to restart SQL Server? Aren't you still available to see your
data?
It seems you have to set your database in emergency mode
update sysdatabases set status=32768 where name='your name'
"Azkaban" <azkaban74@.libero.it> wrote in message
news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> Hi I'm trying to recover database from a suspect status but when I run
this
> command:
> sp_resetstatus webc
> sql return me this message:
> Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> status = 1073741840 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made.
> What is it= What can I do to recover DB?
> Thanks.
> --
> --
> Filippo Macchi
>|||Hi,
Stop and start the SQL server and try accessing the webc database
use webc
go
select * from sysobjects
If it still gives the error then go thru the below informations:-
Details:-
Suspect database may be due to below reasons.
1. MDF or LDF files may be used during the SQL Server service startup
2. LDF file might be corrupt or immediate power shutdown caused the LDF to
corrupt
3. MDF file - Page allocations issue
For the point 1.
Just Run sp_resetstatus <dbname> and restart SQL server (This you have done
already)
For the point 2. ( LDF file might be corrupt or immediate power shutdown
caused the LDF to corrupt)
a. Start SQL Server in emergency mode
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data.
To get your data, use this script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = 'webc'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
You might be able to use bulk copy program (bcp), simple SELECT commands, or
use DTS to extract
your data while the database is in emergency mode.
After this database will be usable with out transaction log. AFter this
create a new database and use DTS to transfer objects and data
For point 3. Very critical error , try executing DBCC CHECKDB with
REPAIR_REBUILD option. If the problem is not rectified try
with restore from Backup or contact Microsoft support.
Thanks
Hari
MCDBA
"Azkaban" <azkaban74@.libero.it> wrote in message
news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> Hi I'm trying to recover database from a suspect status but when I run
this
> command:
> sp_resetstatus webc
> sql return me this message:
> Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> status = 1073741840 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made.
> What is it= What can I do to recover DB?
> Thanks.
> --
> --
> Filippo Macchi
>|||I run the command and received this message, is it correct?:
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
"Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
news:uWWEfRWQEHA.3660@.tk2msftngp13.phx.gbl...
> Hi
> > No row in sysdatabases was updated because mode and status are already
> > correctly reset. No error and no changes made.
> Have you tried to restart SQL Server? Aren't you still available to see
your
> data?
> It seems you have to set your database in emergency mode
> update sysdatabases set status=32768 where name='your name'
>
> "Azkaban" <azkaban74@.libero.it> wrote in message
> news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> > Hi I'm trying to recover database from a suspect status but when I run
> this
> > command:
> >
> > sp_resetstatus webc
> >
> > sql return me this message:
> >
> > Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> > status = 1073741840 (status suspect_bit = 0).
> > No row in sysdatabases was updated because mode and status are already
> > correctly reset. No error and no changes made.
> >
> > What is it= What can I do to recover DB?
> >
> > Thanks.
> >
> > --
> > --
> > Filippo Macchi
> >
> >
>|||Hi
Sp_configure "allow updates", 1
go
Reconfigure with override
go
Update sysdatabases set status = 32768 where name = 'yourname'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
go
"Azkaban" <azkaban74@.libero.it> wrote in message
news:uyJxIXWQEHA.904@.TK2MSFTNGP12.phx.gbl...
> I run the command and received this message, is it correct?:
> Server: Msg 259, Level 16, State 2, Line 1
> Ad hoc updates to system catalogs are not enabled. The system
administrator
> must reconfigure SQL Server to allow this.
>
> "Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
> news:uWWEfRWQEHA.3660@.tk2msftngp13.phx.gbl...
> > Hi
> > > No row in sysdatabases was updated because mode and status are already
> > > correctly reset. No error and no changes made.
> > Have you tried to restart SQL Server? Aren't you still available to see
> your
> > data?
> >
> > It seems you have to set your database in emergency mode
> > update sysdatabases set status=32768 where name='your name'
> >
> >
> >
> > "Azkaban" <azkaban74@.libero.it> wrote in message
> > news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> > > Hi I'm trying to recover database from a suspect status but when I run
> > this
> > > command:
> > >
> > > sp_resetstatus webc
> > >
> > > sql return me this message:
> > >
> > > Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> > > status = 1073741840 (status suspect_bit = 0).
> > > No row in sysdatabases was updated because mode and status are already
> > > correctly reset. No error and no changes made.
> > >
> > > What is it= What can I do to recover DB?
> > >
> > > Thanks.
> > >
> > > --
> > > --
> > > Filippo Macchi
> > >
> > >
> >
> >
>|||Hi,
Can you go thru the steps specified by me in the previous post. That
contains the detailed information on recovering from the suspect status.
Thanks
Hari
MCDBA
"Azkaban" <azkaban74@.libero.it> wrote in message
news:uyJxIXWQEHA.904@.TK2MSFTNGP12.phx.gbl...
> I run the command and received this message, is it correct?:
> Server: Msg 259, Level 16, State 2, Line 1
> Ad hoc updates to system catalogs are not enabled. The system
administrator
> must reconfigure SQL Server to allow this.
>
> "Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
> news:uWWEfRWQEHA.3660@.tk2msftngp13.phx.gbl...
> > Hi
> > > No row in sysdatabases was updated because mode and status are already
> > > correctly reset. No error and no changes made.
> > Have you tried to restart SQL Server? Aren't you still available to see
> your
> > data?
> >
> > It seems you have to set your database in emergency mode
> > update sysdatabases set status=32768 where name='your name'
> >
> >
> >
> > "Azkaban" <azkaban74@.libero.it> wrote in message
> > news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> > > Hi I'm trying to recover database from a suspect status but when I run
> > this
> > > command:
> > >
> > > sp_resetstatus webc
> > >
> > > sql return me this message:
> > >
> > > Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> > > status = 1073741840 (status suspect_bit = 0).
> > > No row in sysdatabases was updated because mode and status are already
> > > correctly reset. No error and no changes made.
> > >
> > > What is it= What can I do to recover DB?
> > >
> > > Thanks.
> > >
> > > --
> > > --
> > > Filippo Macchi
> > >
> > >
> >
> >
>

Tuesday, March 20, 2012

Recover SQL Database from suspect status

Monday, March 12, 2012

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FHDid you read:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
HTH, jens Suessmeyer,|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribi en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>|||Not sure if you fixed this yet but I just had a db marked to this status and
it simply means that the Log file is knackered. Unfortunately, without ba
ckups you will lose some uncommitted data, but at least the written data wil
l be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF - this can
then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily. I g
uess the problem with the log file is that you never clear it down as a resu
lt of a backup. It makes me sad to see you treating a databas in that way!
:-)
Good luck matey.
Mike

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FH
Did you read:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
HTH, jens Suessmeyer,
|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribi en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>
|||Not sure if you fixed this yet but I just had a db marked to this status
and it simply means that the Log file is knackered. Unfortunately,
without backups you will lose some uncommitted data, but at least the
written data will be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF -
this can then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily.
I guess the problem with the log file is that you never clear it down
as a result of a backup. It makes me sad to see you treating a databas
in that way! :-)
Good luck matey.
Mike
mikeroberts23
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1201952.html

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FHDid you read:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
HTH, jens Suessmeyer,|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribió en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>|||Not sure if you fixed this yet but I just had a db marked to this statu
and it simply means that the Log file is knackered. Unfortunately
without backups you will lose some uncommitted data, but at least th
written data will be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF
this can then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily
I guess the problem with the log file is that you never clear it dow
as a result of a backup. It makes me sad to see you treating a databa
in that way! :-)
Good luck matey.
Mik
-
mikeroberts2
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message1201952.htm

Friday, March 9, 2012

Recover Database File

hi all,

i have SQL 2000 server with SP3, yesterday my database was suddenly goes to suspect mode. then i found log in sql server log. and it shows following text:

I/O error (bad page ID) detected during read at offset 0x00000000030000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MFS_Data.MDF'..

kindly, do needful for recover data from this file...

Tejas Kishanwala

You will need to restore your backups to recover the database.|||

hi,

thanx for giving answer. but i have backup before 1 week. and my all important data is in the database. i have already resotred by backup. but is there any way to read this MDF file Manually and retrive data from it.?

Kindly, do needful for the same.

Tejas Kishanwala

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=616836&SiteID=1

recover crashed database

our sqlserver has crashed and try to attach mdf file to server but sql server
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datas
Hi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )
|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )
|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database
|||Or this
http://support.microsoft.com/default...b;en-us;165918
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database
|||Sorry that's 6.5
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.co m...
> Or this
> http://support.microsoft.com/default...b;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>
|||Open a case with Microsoft Support and see if they have any means of salvaging any data. Of,
assuming that you can access the database at all, export all data to a new healthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>

recover crashed database

our sqlserver has crashed and try to attach mdf file to server but sql server
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datasHi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database|||Or this
http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database|||Sorry that's 6.5
--
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.com...
> Or this
> http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>>i done all your writes but dont work yet.database status change to suspect
>> mode and dont permit to recover database
>|||Open a case with Microsoft Support and see if they have any means of salvaging any data. Of,
assuming that you can access the database at all, export all data to a new healthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>

recover crashed database

our sqlserver has crashed and try to attach mdf file to server but sql serve
r
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datasHi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database|||Or this
http://support.microsoft.com/defaul...kb;en-us;165918
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database|||Sorry that's 6.5
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.com...
> Or this
> http://support.microsoft.com/defaul...kb;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>|||Open a case with Microsoft Support and see if they have any means of salvagi
ng any data. Of,
assuming that you can access the database at all, export all data to a new h
ealthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql ser
ver
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>

Friday, March 30, 2012

Recovery of Suspect Database

Recovery of Suspect Database

Recovery of Suspect Database

Friday, March 23, 2012

Recovering DBs with a "Suspect" state

Wednesday, March 21, 2012

Recover SQL Server v8 database on 2005?

Recover SQL Database from suspect status

Tuesday, March 20, 2012

Recover SQL Database from suspect status

Recover SQL Database from suspect status

Monday, March 12, 2012

RECOVER DATABASE WITH CORRUPT LOG FILE

RECOVER DATABASE WITH CORRUPT LOG FILE

RECOVER DATABASE WITH CORRUPT LOG FILE

Friday, March 9, 2012

Recover Database File

recover crashed database

recover crashed database

recover crashed database

Record Locks

Blog Archive

About Me