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
 Recovering data from broken tables (latch errors)

Author  Topic 

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-21 : 04:44:19
Hi!

Is it possible to generate a script, that extracts every bit of data it can, from tables which suffers from latch errors;

DBCC CHECKDB ->
Msg 7985, Level 16, State 2, Line 1

System table pre-checks: Object ID 4. Could not read and latch page (1:403) with latch type SH. Check statement terminated due to unrepairable error.

So bad news on that one. There are no backup available that I know off, so all bets are off on that part of the recovery process.

The "good news" part of the problem, is that it is possible to extract some data out of the table, before SQL server 2005 returns the error. So what I want to do, is get every bit out I can, doing it somehow automatically, so I don't have to run bcp on 180 tables :).

Presumeably, this is quite possible, but how?

NB. If I do a complete basic SELECT * FROM tbl1, it returns data, then errors out, but what about the data after the last row SELECT returns? Is it possible, that there might be data in the table that are recoverable after the 'broken row(s)'?

Kristen
Test

22859 Posts
Posted - 2007-09-21 : 05:38:57
Try this first: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How+I+Rescue+Data+from+Corrupted+Databases

Put an "All User Databases" backup in place!!

Check Event Log for hardware errors, and DBCC CEHCKDB all databases on that server. These type of errors are invariably caused by a hardware fault, which will still be there if it hasn't been fixed

Kristen
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts
Posted - 2007-09-21 : 11:35:11
The error from CHECKDB is concerning one of the system tables that stores metadata about all the tables in the database - so some tables you should be able to suck data out of and others you may not be able to.

What's the error you get when the select fails?

(and I second everything Kristen says too)

Paul Randal
Managing Director, SQLskills.com
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-23 : 09:52:10
Thanks for the suggestions, I'll have a stab at it tomorrow, and see where it brings me. Regarding the HW, I don't know the current state of it, I've just been put on the case to try to save as much data as possible.

Here's a select, on a table that fails (but still manages to return some data).

SELECT * FROM MAIL ->

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xb33a1a1e; actual: 0xbb3a2612). It occurred during a read of page (1:403) in database ID 5 at offset 0x00000000326000 in file 'C:\recover\SQL_Data\C5.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.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts
Posted - 2007-09-23 : 12:33:29
So all tables that have metadata stored in that page will be inaccessible and you won't be able to get the data back. Its trial and error for you to see which tables work I'm afraid.

Thanks

Paul Randal
Managing Director, SQLskills.com
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-24 : 03:52:07
Aye indeed, that's why we moved on to salvage what ever data we can :) (or :( really). Anywho, I didn't expect this, but even generating a CREATE TABLE script gives me errors right now. And even though I demand SQL Server to continue after any error, it still manages to stop.

So I've decided to script a bundle of tables (using the wizard). One would think it's a rather simple task, yet the 'generate script wizard' is exceedingly slow. Is there a quicker way, something I forgot to disable?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-24 : 04:32:47
" didn't expect this, but even generating a CREATE TABLE script gives me errors right now"

I recommend that you do not write ANYTHING to the damaged database, as it is highly likely to compound your difficulties in recovering data.

I would set it to READ ONLY, create a new database, copy data from the damaged database to the new database table-by-table, and row-by-row for tables that cannot be read, and then go from there.

"the 'generate script wizard' is exceedingly slow"

IME it takes quite a long time (minutes) to generate a script for a database with a few hundred tables.

However, your problem may be associated with flaky hardware (i.e. the problem that caused the database corruption in the first place) and it is also possible that the corruption in the database is preventing the wizard from reading the system tables to create the script.

Your database is hosed, you can't really blame the software if it is unable to continue after error, or has trouble scripting the database etc!

Kristen
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-24 : 04:58:05
You are misunderstanding me :). First of all, the "Generate script" is slow, even on a completely normal well functioning SQL Server. Which is exactly, what I'm running on now, as I've received a copy of the broken SQL db.

Anyway, thanks to your script, I now know exactly what tables are broken, so that did help me quite a bit.

It seems your script requires a table called 'TEMP_RESCUE_PK', but I can't find any 'CREATE TABLE' for it?
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-24 : 05:18:25
Same with TEMP_RESCUE_BATCH btw.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-24 : 05:44:04
"the "Generate script" is slow, even on a completely normal well functioning SQL Server"

That's my experience too, but as above only taking a few minutes for 100~200 tables. if yours is taking longer than that I suspect that you wither have more tables/constraints or more complexity than I have, or a slower/busier server

"your script requires a table called 'TEMP_RESCUE_PK', but I can't find any 'CREATE TABLE' for it?"

Yeah, when I review that thread earlier this morning it looked like I had mixed up the table names, probably from some project I was working on and used multiple tables. I'll tighten that up when I have time.

Easiest way to create a "working table" i the same "shape" as an existing table is:

SELECT * -- Put a column list of existing/additional new columns, if required
INTO MyNewTable
FROM MyExistingTable
WHERE 1=0 -- Don't copy any rows across

Kristen
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-24 : 06:25:04
Sorry for being daft here, but what existing table is it I have to use as a template? Isn't TEMP_RESCUE_PK only supposed to be a temporary identity table, which lists what rows it can and cannot copy data from? TEMP_RESCUE_BATCH is the temporary table which holds the data succesfully extracted?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-24 : 06:50:31
Yes, you are right. I'm re-reading that thread / post.

TEMP_RESCUE_PK is created by the

SELECT ...
INTO MyRestoreDatabase.dbo.TEMP_RESCUE_PK
FROM MyBrokenDatabase.dbo.MyBrokenTable

syntax (i.e. there is no separate CREATE TABLE statement)

I have referred to MyTempDatabase also as RESTORE_XFER_TEMP and MyRestoreDatabase. I haven't checked it carefully, but I think they should all be the same thing.

Then the script takes just ONE row and uses it to create the RESCUE_BATCH table:

-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
SELECT TOP 1 ...
INTO MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
FROM MyRestoreDatabase.dbo.TEMP_RESCUE_PK

Not really quite sure why I did it like that, because the next thing that happens is that
MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
is truncated. Either way, the TEMP_RESCUE_BATCH is now created.

Then, within a loop, the script attempts to retrieve blocks of data (between @intStart and @intStop, looping in @intBatchSize batches).

These are copied into
MyRestoreDatabase.dbo.MyBrokenTable

An empty table "MyBrokenTable" should have been created on the target database "MyRestoreDatabase" by step 3 ("Run the [build] Script on the TEMP database")

Hope that helps, but ask if you are stuck.

Kristen
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-25 : 05:15:12
I'm starting to get an error when I try a normal select on the broken table:

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

This error started to occur after putting the broken database into read only mode, which makes the error even more strange. How can there be any data movement, when it cannot write to the db? A simple SELECT * FROM BrokenTable is enough to make the error pop up.

What gives?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-25 : 05:27:08
Guessing: the broken links in the database are causing retrieval of the data to look like it has moved. That's a dead record, to all intents and purposes, so start retrieving somewhere further along the PK list ...

Kristen
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-25 : 05:47:57
That was a very good guess indeed :). I've started to reduce the batchsize (with a factor of ten as you suggested), and it worked!

(100 row(s) affected)
1401
Msg 601, Level 12, State 3, Line 32
Could not continue scan with NOLOCK due to data movement.

When I bump into the dead rows, I'll just increase the @intStart with 1, until it gets past the broken rows (pages?) right?
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-25 : 05:57:02
Actually it gets even more strange than that! I started to reduce the batchsize to 10, and after that to 1. But alas, using a batch size of 1 your script actually managed to get past the broken row(s)!

This popped up after stepping with 1:
(1 row(s) affected)
8845
Msg 601, Level 12, State 3, Line 32
Could not continue scan with NOLOCK due to data movement.

Quite a bit distance to 1401!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-25 : 06:31:28
"When I bump into the dead rows, I'll just increase the @intStart with 1, until it gets past the broken rows (pages?) right?"

You may need to use something bigger than that, or a binary-chop, to find the next undamaged one. Although if the corrupted area is quite small, which is likely, then there may only be a few rows lost".

You might want to start working from the other end of the table, from MAX value downwards, to hopefully isolate an area in the middle which is damaged, and then if that's small enough give up at that point and re-create those, or try 1-by-1 or some area in the middle of the damaged area (in case two, or more, non-contiguous blocks are damaged.

Kristen
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-09-25 : 07:21:16
But the strangeness of this, that I can reduce the batchsize to 1, then let it run for ages, the NOLOCK occurs, I update the @intStart and it continues like the error never occured!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-26 : 05:06:11
Yes, but your table is corrupted, there is no telling what strange effects you might get.

SQL Server may be using a TABLE SCAN for ROWCOUNT=1, and an INDEX for ROWCOUNT>1, I don't know what it might be doing, but what I do know is that without proper backups you will be lucky with whatever data you can salvage!

Personally I would not spend any grey matter on why & what, and just want to rescue the data and move on from there

Kristen
Go to Top of Page

northyen.dk
Starting Member

16 Posts
Posted - 2007-10-02 : 09:52:33
I've rescued the data I could, and now I'm starting to wonder how much was rescued.

For example, a table which have 'broken rows', defined as follows:

CREATE TABLE [dbo].[FINPOST](
	[DATASET] [varchar](3) NOT NULL,
	[LXBENUMMER] [int] NOT NULL,
	[SIDSTRETTET] [datetime] NOT NULL,
	[KONTO] [varchar](10) NOT NULL,
	[BUDGETKODE] [int] NOT NULL,
	[AFDELING] [varchar](10) NOT NULL,
	[DATO] [datetime] NOT NULL,
	[BILAG] [int] NOT NULL,
	[TEKST] [varchar](30) NOT NULL,
	[BELXBDKK] [numeric](28, 12) NOT NULL,
	[BELXBVAL] [numeric](28, 12) NOT NULL,
	[VALUTA] [varchar](3) NOT NULL,
	[MOMS] [varchar](10) NOT NULL,
	[MOMSBELXB] [numeric](28, 12) NOT NULL,
	[ANTAL] [numeric](28, 12) NOT NULL,
	[POSTTYPE] [int] NOT NULL,
	[FORFALD] [datetime] NOT NULL,
	[TRANSAKTION] [int] NOT NULL,
	[OPRETTETAF] [int] NOT NULL,
	[KLADDENR] [int] NOT NULL,
	[BELXB2] [numeric](28, 12) NOT NULL,
	[LXSBELXB2] [int] NOT NULL,
	[BXRER] [varchar](10) NOT NULL,
	[FORMXL] [varchar](10) NOT NULL,
	[AFSTEMNINGSNR] [int] NOT NULL
) ON [PRIMARY]

If I do a SELECT * FROM FINPOST the result reports 176195 rows before erroring out with 'Could not continue scan with NOLOCK due to data movement.'. If I do a SELECT dataset,lxbenummer FROM FINPOST the query finishes without any errors (it does finish, because it uses a different index), and the result pane reports 176053 rows. I used the 'dataset,lxbenummer' query to salvage the data btw.

So does that mean, that the index isn't up to date, or what exactly can you conclude about this?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-02 : 10:32:49
Does

SELECT COUNT(*) FROM FINPOST

tell you anything useful? I imagine that it would use a UNIQUE INDEX to solve that one, most likely the PK

Kristen
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site