corrupted system table, error 2541

[dting2]

When I execute DBCC NEWALLOC on a large (1.5 GB) database, I get an ERROR 2541 message as follows:

Msg 2541, Level 16, State 1
Table Corrupt: object id does not match between extent in allocation page and Sysindexes; check the following extent: alloc pg#=318976 extent#=319088 object id on extent=440987071 (object name = 440987071) object id in Sysindexes=441052607 (object name = Assessment)

It turns out that alloc pg#318967 is a system table (ALLOCATION). The SQL 6 manual simply says "reload the database from a known good backup" -- but I'm afraid my oldest backup tape post-dates the onset of the problem. Anyway, won't I lose all my recent data if I reload the database from an old backup?

The question: is there any way I can fix this problem in a system table?

Thanks!

 

[dbmsguy]

My first question would be to ask if you have followed the advice in the sql manual.... for 2541, it indicates that you should run checkalloc in single user mode. have you done this?

I do believe that you will find that if the object is 'ALLOCATION', then it is actually an allocation map page (this is from memory). If this is the case, it may not be a huge deal. you might want to run some dbcc pagelinks

use: dbcc traceon(3604)

dbcc page(dbid,pageid)

this will give you before and after links for pages Paul

http://www.dbmsguy.com

 

[dbmsguy]

If you find that your data is truly corrupted, and the data is not accessible in its entirety, you can sometimes do the following:

1. if you have a non-clustered index, you can sometimes use this as a method to access the data so that it does not follow the page chains. One of the characteristics of a non-clustered index is that there is a pointer to every row of the table in the index. By forcing an index, you can sometimes access data in a corrupted table.

the technique is to use a 'select * into newtable from badtable (index whatever) where (insert an appropriate where).

by doing this you can sometimes access the data because it does not follow the page chains. works sometimes, sometimes not. try using ever index possible.

2. try using the command:

dbcc pglinkage(dbid,startpgnbr,number_of_pages,2,0,x)

where x=0 for previous and 1 is for forward.

what you might want to do is to see if the backward page pointers are bad. as well. if backward page pointers are ok you might be able to fix it. Paul

http://www.dbmsguy.com

 

[dting2]

Thanks, Paul. To answer your first question, yes, I did run DBCC NEWALLOC in single-user mode. (Prior to doing that, I got a TON of spurrious error messages.) Now, before I try the dbcc pglinkage trick, could you explain how I will be able to tell if the pointers are good or bad? (Unfortunately, I'm just a simple power-user who happens to find himself stuck in the role of system administrator, because the vendor for our software system went belly-up, and I'm the only one left who has any idea how to work with the SQL server). Thanks in advance for your help!

David

 

[dbmsguy]

the internal structure of the database is that each 2K page has a forward and backward pointer. For example, page 1 points to 2 points to 3 points to 4. as well, 4 points to 2 points to 2 ponits to 1. 1 would be the root page. you can find root pages in sysindexes. each object has a root page.

so, it looks like your page links have been corrupted.

since you don't have a current backup you are in the position to either fix the problem or try and extract all or as much of the data as you can. the select into trick can sometimes get around corruption.

one good thing to know if how bad the corruption really is. doing the pagelinkage will help determine this.

if you do a page linkage forward from before the corrupt page and through it, then do a page linkage from after the corruption backwards. for each page you get back, you do a dbcc page on it and see what object id it belongs to.

the backward page linkage may give you all the same object ids which would mean that the backward pagelinkages are ok.

i am trying to remember all the dbcc commands that can be used. most are undocumented unfortunately. if the backwards linkages are intact, MS support may have an undocuemtned dbcc that will fix it.

other tricks other than above are to bcp out as much as you can (up to the corruption), then update sysindexes so that the root page is after the corruption and the bcp out again. you then take all that data and bcp it in a new table.

I'm sure there is a dbcc command that you can use to update the linkaages and I am trying to find it for you.

hope at least some of this is helpful. Paul

http://www.dbmsguy.com

 

[dbmsguy]

for more info on pglinkage, check:

http://support.microsoft.com/support/kb/articles/q115/5/19.asp

Paul

http://www.dbmsguy.com

 

[dting2]

Wow, thanks for the great explanation. I'll try that procedure and let you know what comes up...
David

 

[dbmsguy]

if you don't already, make sure you have a backup of where you are now... you may need it... specially if you go changing system tables and such.. Paul

http://www.dbmsguy.com

 

[dting2]

I've finally gotten a chance to check some pagelinks. When I run

dbcc pglinkage (7, 318976,10,2,0,1), I get the following result:

Object ID for pages in this chain = 99.
Page : 318976
Page : 3
pprevpg pointer for page 3 does not point to previous page in chain
as scanned. pprevpg pointer = 222024, previous page as scanned =
318976.
2 pages scanned. Object ID = 99. Last page in scan = 3.

DBCC execution completed.

When I try to follow the pagelinks for page #3 going forward, by running:

dbcc pglinkage (7, 3,10,2,0,1), I get the following result:

Object ID for pages in this chain = 1.
Page : 3
Page : 1467
Page : 2068
Page : 1466
Page : 2069
Page : 1468
Page : 4
Page : 1470
Page : 1469
Page : 2070
10 pages scanned. Object ID = 1. Last page in scan = 2070.

DBCC execution completed.

Going backward from page 3 yields:

Object ID for pages in this chain = 1.
Page : 3
Page : 222024
Page : 1465
Page : 222025
Page : 7
Page : 2067
Page : 1464
Page : 2
Page : 2066
Page : 6
10 pages scanned. Object ID = 1. Last page in scan = 6.

DBCC execution completed.

When going backward from page #318976, it seems that page #318976 is the first page in the link.

How do I interpret this? How would you suggest fixing this?
Thanks!

David

 

[dting2]

By the way, does anyone know if the undocumented procedure: sp_fixindex as described in

http://support.microsoft.com/suppor...qry=dbcc&rnk=6&src=DHCS_MSPSS_gn_SRCH&SPR=SQL

is the repair solution I should use?

Thanks,

David

 

[dbmsguy]

From what you have documented, it does not look like there is a problem in your system tables. Obj id 99 is an allocation page not a real table. you have two objid's in the error message, both are not system tables. I would concentrate on data recovery. I would suggest calling MS Support at this point. you could try to just extract data as well and see where you get (backup first). Might try using the 'select into' method I mentioned and see where that gets you. Best bet would be to see where MS support gets you, there are always undocumented tricks that can be used. Paul

http://www.dbmsguy.com

 

[dting2]

Thanks Paul! In retrospect, I realize I was looking up the alloc page# instead of the extent# -- so no wonder the pagelinks were pointing me to the allocation table! I really appreciate your time in looking into this. I'll definitely be contacting MS Support at this point.

Cheers,

David