table corruption... - SQL Server Forums

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

table corruption...

Author

Topic

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-03 : 13:34:24

I get the following errors when I ran dbcc checkdb..

[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 0, index ID 0, page ID (1:13015). The PageId in the page header = (0:0).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 0, index ID 0, page ID (1:13039). The PageId in the page header = (0:0).
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 18099105, index ID 0: Page (1:13015) could not be processed. See other errors for details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 18099105, index ID 1. Page (1:12777) is missing a reference from previous page (1:12567). Possible chain linkage problem.

and

[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 562101043, index ID 2. The high key value on page (1:1779) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:13072).

What does it mean and how can i resolve them....
Database went into suspect mode , is this the likely cause..

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-03 : 14:32:08

Yes, this is most likely why your database went suspect. From what you've posted, you have some corrupted pages in the clustered index of object 18099105, plus some (possibly unrelated) non-clustered index corruption in table 562101043. The non-clustered index corruption will not cause any data loss, but the clustered index ones may - depending on where the corrupt pages are within the index. I need all the errors to be able to tell.

Your options depend on what any other errors are, plus your answers to the questions below - please post as many answers as you can (preferably inline with the questions for easy cross-reference) and we'll help you further.

Some questions for you:

1) are these the only errors reported? If not, please post the output of DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS
2) is there any evidence of h/w problems in the SQL errorlog or the Windows event logs?
3) are all relevant firmware/drivers up-to-date?
4) do you have an up-to-date backup?
5) can you post the output of DBCC PAGE (yourdb, 1, 13015, 3)?
6) can you post the output of DBCC PAGE (yourdb, 1, 13039, 3)?
7) have you changed anything in your system recently? (e.g. added new hardware)

Thanks

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-03 : 15:48:47

Thanks Paul for your reply!.

Answers to your questions...

1) are these the only errors reported? If not, please post the output of DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS

>> Actually the results are 45 pages long,,,
but those are the errors...

2) is there any evidence of h/w problems in the SQL errorlog or the Windows event logs?
>> no problem !

3) are all relevant firmware/drivers up-to-date?
>> Yes.

4) do you have an up-to-date backup?
>> A day old backup.Restoring it is not an option now.

5) can you post the output of DBCC PAGE (yourdb, 1, 13015, 3)?
6) can you post the output of DBCC PAGE (yourdb, 1, 13039, 3)?

>>
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: DBCC PAGE page (13039:3) (object ID 0, index ID 0) is out of the range of this database.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: DBCC PAGE page (13039:3) (object ID 0, index ID 0) is out of the range of this database.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: DBCC PAGE page (13039:3) (object ID 0, index ID 0) is out of the range of this database.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
----------
Error: -1 - [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: DBCC PAGE page (13039:3) (object ID 0, index ID 0) is out of the range of this database.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: DBCC PAGE page (13039:3) (object ID 0, index ID 0) is out of the range of this database.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Its the same for both pages...

7) have you changed anything in your system recently? (e.g. added new hardware)

>> No..

Awaiting your reply..

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-04 : 12:19:05

Can you send me email with the CHECKDB output please?

You're not executing DBCC PAGE properly - you've missed out the dbname or the '1'

If the dbname is 'mydb', you need to issue:

DBCC TRACEON (3604, 1)
go
DBCC PAGE (mydb, 1, 13015, 3)
go
DBCC PAGE (mydb, 1, 13039, 3)
go

Why can't you restore the backup?

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-04 : 12:33:54

hi Paul..

Can you send me email with the CHECKDB output please?
>> sure..can I have your mail id. I tried to checkout your profile,
every time it says you should be logged on to view a member's profile
even though I have logged in.

DBCC TRACEON (3604, 1)
go
DBCC PAGE (mydb, 1, 13015, 3)
go
DBCC PAGE (mydb, 1, 13039, 3)
go
>>> I restored the backup of the database on my test server.
Do I have to run DBCC PAGE on the prodn server itself??
If not here is the result of DBCC PAGE from my test server for the same database.

DBCC PAGE (mydb, 1, 13015, 3)
go

PAGE: (0:0)
-----------

BUFFER:
-------

BUF @0x18EE6700
---------------
bpage = 0x19588000 bhash = 0x00000000 bpageno = (1:13015)
bdbid = 8 breferences = 1 bstat = 0x809
bspin = 0 bnext = 0x00000000

PAGE HEADER:
------------

Page @0x19588000
----------------
m_pageId = (0:0) m_headerVersion = 0 m_type = 0
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 0 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 0
m_freeCnt = 0 m_freeData = 0 m_reservedCnt = 0
m_lsn = (0:0:0) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0

Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:8088) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DBCC PAGE (mydb, 1, 13039, 3)

PAGE: (0:0)
-----------

BUFFER:
-------

BUF @0x18EE6D40
---------------
bpage = 0x195BA000 bhash = 0x00000000 bpageno = (1:13039)
bdbid = 8 breferences = 1 bstat = 0x809
bspin = 0 bnext = 0x00000000

PAGE HEADER:
------------

Page @0x195BA000
----------------
m_pageId = (0:0) m_headerVersion = 0 m_type = 0
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 0 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 0
m_freeCnt = 0 m_freeData = 0 m_reservedCnt = 0
m_lsn = (0:0:0) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0

Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:8088) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Why can't you restore the backup?
>> "so that we dont loose the current data."
Is there a way to take backup database when in suspect or emergency mode??

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-04 : 22:29:29

You've got hardware problems. I guess you're also the guy on dbforums with the exactly matching set of errors and page IDs? If so, it would have been useful to post all that extra info here.

You need to move to new hardware and restore from your backup or extract as much info as you can from the database. PSS can help you with this but it will cost you $249 for the call (given that its h/w and not a SQL bug).

There's not much point having a backup if you're not prepared to use it - you should get a backup strategy which involves regular log backups between data backups so you can really limit the amount of data/work you'd lose if you have to use it.

Thanks

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-13 : 13:10:19

Hi,

I get the following error when i try to retrieve the data
from the two corrupt tables

"Could not continue scan with NOLOCK due to data
movement."

Is it not possible to retrieve the entire data at all.??

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-13 : 14:18:15

Please either post in dbforums or in sqlteam - I'm not going to answer you in both.

How are you trying to get the data out?

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-13 : 14:58:26

I tried bcp...
Count(*) gives me 96609 rows
whereas bcp gives me 84010 rows...

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-13 : 15:02:18

I also tried Export Data from EM...
same result as bcp...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-13 : 15:08:43

See where the BCP output ends and try to select the second half of the data after the corrupt pages using the clustering key - that should be pretty simple to work out.

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-13 : 17:46:43

Hey Paul,

In QA

The last row returned by bcp is for PK sequence..87360
whereas the max PK seq is 99959
When I try to select records between 87360 and 99959
no rows are returned....

But when I try to select using
WHERE sequence = 99959 I get the record..

when I use bcp
and use -F84011 (since bcp returned 84010 in the 1st try it )
it doesnt give any records...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-13 : 21:10:27

So try selecting from the table with successively lower PKs until you reach the boundary between what you can access and what you can't. Everything between the highest PK you can access from the beginning of the data to the lowest PK you can access from the end has been lost.

Thanks

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-14 : 12:38:43

Hi,

Is there a better approach...??
It seems there are sections where the rows are corrupted...
with a million records its difficult to narrow down ..

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-14 : 12:49:08

quote:
Originally posted by 2lazydba

Hi,

Is there a better approach...??
It seems there are sections where the rows are corrupted...
with a million records its difficult to narrow down ..

Absolutely - have a comprehensive backup and disaster recovery strategy.

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-14 : 12:58:15

point taken..
but now that the damage has been done ..is it possible
to get back the data which is there...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-14 : 13:43:41

Nope - the pages have been trashed so the data has gone for all non-manual methods of recovery.

Its certainly possible for someone with expert knowledge of SQL internals and on-disk formats to piece things together manually but AFAIK there isn't anyone apart from a handful of people in MS who can do that and we don't offer a data recovery service for h/w problems (if only I worked as a consultant..

)

The limit of what you're going to able to recover is what you can select manually or with bcp. There's no point spending time trying any other methods.

Thanks

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-14 : 17:54:30

hey paul,

Can you point out some articles which describe DBCC commands in depth..

ryanston
Microsoft SQL Server Product Team

89 Posts

Posted - 2005-10-15 : 13:58:16

quote:
Originally posted by 2lazydba

hey paul,

Can you point out some articles which describe DBCC commands in depth..

The only documentation out there currently is Books Online. Paul & I are working on some whitepapers based on SQL Server 2005, but we don't have an ETA on those yet.

Thanks,

----------------------
Ryan Stonecipher
Developer, Microsoft SQL Server Storage Engine, DBCC
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-10-17 : 19:03:26

curious to know as to whether it would describe new DBCC commands in SQL 2005 only or those tat are available in SQL 2000 as well...

(can u ask anyone from ur team to help me out in
http://www.dbforums.com/t1198613.html
)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-18 : 12:34:58

For the installation issue, post a question in one of the SQL Server 2005 public newsgroups.

The whitepapers will be SQL2005 focused but the commands are the same as those in SQL2000.

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources