Access the answers to your technology questions today.
Subscribe Now
30-day free trial. Register in 60 seconds.
What Makes Experts Exchange Unique?
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
Subscribe Now
30-day free trial. Register in 60 seconds.
Join the Community
Give a Little. Get a Lot.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Join the Community
by: alpmoonPosted on 2009-09-07 at 23:58:29ID: 25279194
You should be able to see the actual object name as well. If it is sysprocedures, there is a solved case for 100039 (if it is a user object, it would be easier to fix as mentioned below):
========== ========== ========== ====CR#441 924 "DDLRLC: Extending sp_fixindex to work as 'reorg rebuild' for system catalog."
========== ========== ========== ====
ALLOC when run in 'OPTIMIZED' mode and with the 'FIX' option ========== ========== ========== ====
========== ========== =========
Products : ASE 15
Platform : ANY
Problem : dbcc fault 100039 (Extent OAM page reference is set incorrectly) on sysprocedures.
Tip or Workaround
Resolution
100039 Errors are reported in ASE 15.0 because of an enhancement in checkstorage. See details below on impact & how to fix:
The main impact is for partition/unpartition. These faults are not serious and can safely be ignored or to exclusion list using sp_dbcc_exclusions:
Fault 100039 can be cleared on tables performing reorg rebuild, but this cannot be done on system tables.
=> Enhancements were brought about in ASE 15.0.2 (via CR#441924 - details below) which extended sp_fixindex to work as 'reorg rebuild' for system catalog.
Also, for information, thanks to enhancements in CR#534553 (details below) implemented in ASE 15.0.3 , dbcc checkalloc/tableallloc can fix 100039 Errors.
CR details - for information:
==========================
Sanitized: sp_fixindex procedure is enhanced to work on all the indexes instead of single index. At the same time it will mimic the behavior of 'reorg rebuild' (reclaims the un-used space from the data layer) while rebuilding placement index / clustered index.
Fixed in ASE 15.0.2 IR
==========================
CR#534553 "DBCC: dbcc checkalloc/tableallloc should fix 100039 errors reported by checkstorage"
Workaround: reorg rebuild or Bulk copy the data out and back in
Sanitized: "DBCC TABLEALLOC/TEXTALLOC/INDEX
supports the correction of '100039' faults reported by DBCC CHECKSTORAGE for user tables; this
fault describes an incorrect extent OAM page hint."
=> Fixed in ASE 15.0.3
==========================
To run sp_fixindex on sysprocedures, you can do the following:
1. Put the database in single user mode by doing the following from within the master database:
sp_dboption <database_name>, "single user", true
go
2. Allow updates to system tables:
sp_configure "allow updates", 1
go
3. Checkpoint the database:
use <database_name>
go
checkpoint
go
This is now automated so no longer needed, but still a good idea to do just to be sure.
4. Run sp_fixindex using object_name= "sysprocedures" ---find the table name by object id. and index_ID = 1 :
sp_fixindex <database_name>, syprocedures, 2
5. Clean up after yourself:
use master
go
sp_configure "allow updates", 0
go
sp_dboption <database_name>, "single", false
go
use <database_name>
go
checkpoint
go
6. Now check to see if the 100039 faults have been
resolved.
It is safe to bcp out sysprocedures and back in to resolve this issue, however I would advise trying sp_fixindex first.
NOTE: Before bcp out/in of sysprocedures you would need to take a database dump, in case anything went wrong.
Steps to rebuild using bcp out/in (and by select into)
==========================
1. first enable system table updates
sp_configure "allow updates",1
2. bcp out the sysprocedures table..
you may also do another way : select * into my_procedures from sysprocedures
3. delete sysprocedures
4. bcp in the data
- or insert sysprocedures select * from my_procedures
5. sp_configure "allow updates",0