Few zeroed pages and outdated backup

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

Few zeroed pages and outdated backup

Author

Topic

mike_ivanov
Starting Member

3 Posts

Posted - 2007-01-17 : 12:08:02

Greetings.

I've got some (standard?) data corruption issue with quite large SQL database (~8GB). Our RAID10 crashed recently with no chances to restore our database from backup (backup copies were saved on the same disk array weekly (complete backup) - quite a dumb idea and very good lesson, I must admit it).
We managed to get almost all data back from our disks and I attached databases at another server. Today I used DBCC CHECKDB which found like 15 corrupt pages in single _very_ large user table (~4GB):

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39664). The PageId in the page header = (0:0).
....
(etc, ~15 times with different page numbers)

DBCC PAGE displayed that they were just filled with 0 (I guess they resided on those few unlucky disk sectors, data recovering company was unable to read).

I restored most recent backup (like 1 week old) to another database and checked these pages again (and few that were before & after them), they were just fine and the pages before and after were the same exactly.

Well... Here is my Big Question :)

Is there any chance I can get these pages from restored database and just write them over my corrupted data with some tool?
I have no idea how to locate the offset of these pages inside .MDF file at this moment.

Can someone help me with it? This place seems like my last hope...

Thanks in advance.

P.S. I'm trying to avoid dropping these pages in any possible way, they contain... uhh, sort of financial information.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-01-17 : 12:52:31

There's no publicly available way to do the direct copy of the page contents.

Does the table have any LOB columns that are stored off-row? If not, then you may be able to allow CHECKDB to delete the pages and then simply do an insert/select between the older database and the repaired on for the rows that are on the 15 zero'd pages. You can even preserve identity column values too if needed.

Can you post the output of CHECKDB?

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

mike_ivanov
Starting Member

3 Posts

Posted - 2007-01-17 : 13:12:03

quote:
Originally posted by paulrandal
There's no publicly available way to do the direct copy of the page contents.

Does the table have any LOB columns that are stored off-row? If not, then you may be able to allow CHECKDB to delete the pages and then simply do an insert/select between the older database and the repaired on for the rows that are on the 15 zero'd pages. You can even preserve identity column values too if needed.

Can you post the output of CHECKDB?

Thanks for your answer and thanks for your blog, Paul :) Your articles were VERY helpful for me today!

It seems the table doesn't contain any off-row data:


#-----Fields-------
# Name                  |Type|Length|Precision
F=PERIOD                |D   |0     |0        
F=SP3613                |C   |9     |0        
F=SP3615                |C   |9     |0        
F=SP3614                |C   |9     |0        
F=SP7786                |C   |13    |0        
F=SP6363                |C   |9     |0        
F=SP6364                |C   |9     |0        
F=SP6365                |C   |9     |0        
F=SP3619                |N   |12    |3        
F=SP6366                |N   |17    |5        
F=SP7787                |N   |17    |5

Is there any way to select data based on page numbers? I'm not that familiar with MS SQL Server yet :)

Just checked it again. Well, it were 2 tables actually. Nevermind, their structures are almost the same.

The output of CHECKDB:

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39664). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39665). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39666). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39667). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39668). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39669). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39670). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:39671). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:563952). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:563953). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:563954). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:563955). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:563956). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:563957). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:563958). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:563959). The PageId in the page header = (0:0).
DBCC results for 'ITRP2007'.
CHECKDB found 0 allocation errors and 16 consistency errors not associated with any single object.

DBCC results for 'RA3612'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 397244470, index ID 0: Page (1:39666) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 397244470, index ID 0: Page (1:39667) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 397244470, index ID 0: Page (1:39668) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 397244470, index ID 0: Page (1:39669) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 397244470, index ID 0: Page (1:39670) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 397244470, index ID 0: Page (1:39671) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1

Object ID 421381066, index ID 0: Page (1:563952) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 421381066, index ID 0: Page (1:563953) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 421381066, index ID 0: Page (1:563954) could not be processed. See other errors for details.

There are 13529669 rows in 221837 pages for object 'RA3612'.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'RA3612' (object ID 397244470).

DBCC results for 'RG20400'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 421381066, index ID 0: Page (1:563955) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 421381066, index ID 0: Page (1:563956) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 421381066, index ID 0: Page (1:563957) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 421381066, index ID 0: Page (1:563958) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 421381066, index ID 0: Page (1:563959) could not be processed. See other errors for details.
There are 280856 rows in 5912 pages for object 'RG20400'.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'RG20400' (object ID 421381066).

CHECKDB found 0 allocation errors and 32 consistency errors in database 'ITRP2007'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ITRP2007 ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

WBR, Mike

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-01-17 : 13:58:43

You're welcome.

No - no way to select based on page number but if the table has a key, you can use that - either from a list of explicit values or key ranges present on the page (from dump style 3 of dbcc page). Alternatively you could just insert the rows one at a time rather than copying them with insert/select.

If you have identity values you want to preserve, do a SET IDENTITY_INSERT <tablename> ON before doing the insert/select.

Let me know if you have any questions (and how you get on)

Thanks

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-01-17 : 19:23:22

Actually, thinking about this some more, it should work even with LOB values. Repair will fixup the orphaned LOB values in the corrupt database and the insert/select from the older copy will pull over the LOB values too.

This scenario will make a good how-to blog post...

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

mike_ivanov
Starting Member

3 Posts

Posted - 2007-01-18 : 11:35:12

It seems that I managed to resolve my problem in a bit different way I ever planned :)

As I started to examine pages from backup database more closely (preparing to fetch them all in some table, then parse and finally insert in corrupted database), I noticed three things:

1) I was wrong about the table name (the structure I posted was from RG3612, not RA3612)
2) Every row in RA3612 contains two important values - date stamp and reference to some document in our business aplication, which runs on this database
3) I'm not too smart, 'cause didn't notice it instantly, though I'm developing and supporting this application for more than 2 years already ("the fear has huge eyes" as we say here, in Russia)

Nevermind.
I examined each page and discovered that they all belong to single document (same DocID field value everywhere), if we look from our business application's point. So I looked up for this DocID to find out what document it represents exacltly. Then I fixed up my database with REPAIR_ALLOW_DATA_LOSS, started our application and regenerated every row document created in RA3612 passing this document through the system again (the data in this table is based on document fields, which were untouched - so it appeared to be not so critical like I thought originally).

The situation with second table was even funnier. Our application stores precalculated totals for each period (performance issues) and the table represented this kind of data. So I used DBCC PAGE to find out that all missed rows belong to one single period too. Then I used application's platform mechanism to recalculate this part of the table and recreate lost rows.
That's all :)

Well, now, when I restored my database completely, I can say that DBCC PAGE is a brilliant tool in situations like this one :) With two versions of database you can do wonders. I was able to learn what data was lost exactly and then interpreted it from business application's perspective. The rest was just a formal thing :)

Thank you Paul :)

WBR, Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources