AllocationErrors - 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

AllocationErrors

Author

Topic

MCCBasis
Starting Member

2 Posts

Posted - 2007-03-07 : 03:21:28

Hi,
we have some trouble with AllocationErrors in a SQL2K-Database.
Cause may be a Hardware Error (relating to a SCSI-Driver), that occured while performing Database Maintenance (Optimizations).

DBCC Check on Database gives now following result.

DBCC results for 'CB_MCC_PROD'.
Meldung 8939, Ebene 16, Status 5, Zeile 1
Table error: Object ID 0, index ID 0, page (1:137496). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Meldung 8939, Ebene 16, Status 6, Zeile 1
Table error: Object ID 0, index ID 0, page (1:137496). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Meldung 8939, Ebene 16, Status 7, Zeile 1
Table error: Object ID 0, index ID 0, page (1:137496). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Meldung 8998, Ebene 16, Status 1, Zeile 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 9 pages from (1:137496) to (1:145583). See other errors for cause.
CHECKDB found 4 allocation errors and 0 consistency errors not associated with any single object.

By checking the first faulting page 1:137496) we could see that every thing ist nulled out.

Meldung 8909, Ebene 16, Status 1, Zeile 1
Table error: Object ID 0, index ID 0, page ID (1:137496). The PageId in the page header = (0:0).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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

BUFFER:
-------

BUF @0x01C2A940
---------------
bpage = 0x730BA000 bhash = 0x00000000 bpageno = (1:137496)
bdbid = 7 breferences = 1 bstat = 0x809
bspin = 0 bnext = 0x00000000

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

Page @0x730BA000
----------------
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) = NOT ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:137496) = 0x0 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:
-----

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Meldung 8968, Ebene 16, Status 1, Zeile 1
Table error: DBCC PAGE page (1:145583) (object ID 0, index ID 0) is out of the range of this database.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Unfortunatly this error occured a few weeks ago and our client noticed this error now. So the restoring the Database from a backup does not work (because Backups of Database and Log are kept on System for two Weeks only).

Exporting the database into an empty one does not work, because there occurs an error while copying the Server objects.

I've found the following article
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/01/18/fixing-damaged-pages-using-page-restore-or-manual-inserts.aspx , but it deals with consistency errors...

What could be done here to fix the Problem? Any ideas?

Thanks and regards.
MCCBasis

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-07 : 04:37:04

You've got an unrepairable corruption in the database - PFS page errors cannot be repaired (see https://blogs.msdn.com/sqlserverstorageengine/archive/2007/02/04/checkdb-part-8-did-repair-fix-everything.aspx).

Unfortunately your only choices are to restore from a backup or to export the data into a new database. Call Customer Support if you need assistance with that.

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

Kristen
Test

22859 Posts

Posted - 2007-03-07 : 06:10:17

See How I Rescue Data from Corrupted Databases

Kristen

MCCBasis
Starting Member

2 Posts

Posted - 2007-03-09 : 03:00:31

Hi Kristin,
thanks for posting your "rescue-procedure".
But i still have got a question ...
- the scripting of the database under 1 - has it got to be done into one file (including tables (with or without keys?), viewes and procedures) or would it be better to create one file for tables, one for views and one for procedures?
Thanks for your reply.
MCCBasis

Kristen
Test

22859 Posts

Posted - 2007-03-09 : 04:26:15

Probably easier to re-run the script if its in one file.

You need to segregate the FKs though - but they are all together - so the DROPs and the CREATEs can easily be cut&pasted out from the single original script.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources