Corrupt SQL 2000 Database - Error 823

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

Corrupt SQL 2000 Database - Error 823

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-09 : 08:50:19

Robert writes "Is it possible to recover any data from a database that is throwing the following error in query analyzer?

select * from table1

server: msg 823, level 24, atate 2, line 1
I/O error (bad page ID) detected during read at offset 0x00000007b7e000 in file '... dbname.mdf'

I can select top n from some tables with no error, if n is too large, I get the same error. Other tables give this error even for top 1.

After setting the db in single_user mode, I tried dbcc checkdb with repair_allow_data_loss option and received the following:

msg 8966, level 16, state 1, line 1
could not read and latch page ( 1:15807 ) with latch type SH. sysindexes failed.

I tried dbcc checktable on sysindexes with repair_allow_data_loss option and received the following:

msg 8966, level 16, state 1, line 1
could not read and latch page ( 1:15807 ) with latch type SH. sysindexes failed.
msg 8966, level 16, state 1, line 1
table error: object id 0, indexid 0, page ID (1:15807) the pageid in the page header = (0:0)
The error has been repaired.

I get the same message each time I run the DBCC command.

I can select * from sysindexes with no problem.

If I select * from sysindexkeys, I get the following:

msg 601, level 12, state 3, line 1
could not continue scan with nolock due to data movement.

I'm wondering if sysindexkeys is corrupt and causing this problem?...

I have also tried to bcp data out of the database with no success.

Any help or advice would be greatly appreciated.

Thanks in advance.

Robert"

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-06-09 : 12:39:46

First question, why did you go straight to repair? (Check out my recent blog post on bad advice during disaster recovery https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/621060.aspx)

The answer to your question is yes, even though its a sysindexes page that is corrupt. Repair isn't going to help you at all - repair can't fix pages in the sysindexes clustered index. Do you have any backups? These are your best bet.

Can you give more details on bcp failing? What exactly did you try to do and how did it fail?

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

pricer
Starting Member

1 Post

Posted - 2006-06-20 : 21:43:21

Paul,

First, I apologize for letting this thread go stale. My department has been going through a move and reorganization for the last week...

I know, excuses are for those who need them...

I am trying to help out one of our customers. They did not backup the database before it became corrupted... big surprise. They sent me a backup of the corrupted database and asked me to try and recover any data I can.

Repair wasn't my first choice, it was just the latest in many attempts to get any useful data out of the database. I agree with you, it doesn't work for this problem...

btw, I did read your post on bad advice and found it very helpful.

Here is the bcp command:

C:\>bcp databaseName..sample out "C:\bcp-sample.csv" -c -t, -r\n -S serverName -E

When I execute it, I get the following error:

SQLState = S1000, NativeError = 823
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]I/O error (bad page ID) detected during read at offset 0x00000007b7e000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\databaseName.mdf'.

This is basically the same error I get when I try a select statemnt against the table in query analyzer.

I really appreciate your help on this.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-06-20 : 21:54:29

The page that's bad in sysindexes (15807) is the one that the 823 is complaining about (15807 x 8192 converted to hex = 7b7e0000). It looks like it contains the metadata for the 'sample' table so there's no way you can get this table data out without manually poking about in the database using DBCC PAGE. CSS can't do this and its beyond the scope of this forum to do so you'll need to tell the client that the data is irretreivable unless they want to go to a data recovery firm (and they need a better backup strategy).

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Davidpoul
Starting Member

14 Posts

Posted - 2009-07-09 : 03:43:07

Yes it is possible to recover any data from a database that is throwing the error in query analyzer? Not only this you are able to recover your deleted data also. To recover your data from database you need to take the help of SQL Recovery software from stellar. It helps you to recover your data just in few clicks. You can download it from http://www.mssqldatabaserecovery.com

David Poul

adomlinks
Starting Member

1 Post

Posted - 2010-01-21 : 08:02:37

If the sysindexkeys gets corrupted and also shows these errors after running bcp command or query analyzer, you need to take the help of a third-party data recovery tool. One such suggested software product for recovering SQL database contents is SysTools SQL Rcovery software. You can download this software from http://www.sqlrecoverytool.com or mssqlrepair.org

Adom Watson

View SQL Recovery & MS SQL Repair for SQL Diaster Recovery.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources