SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 SQL server 2000 data page corrouption
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JasbirSingh120
Starting Member

USA
3 Posts

Posted - 08/26/2012 :  20:08:13  Show Profile  Reply with Quote
We have a situation with SQL 2000 database, the application is not able to fetch the data from certain tables and give an error with the database not available. No insert statement are working as well. On running the DBCC check DB command we encountered 4 allocation errors and 14 consistency errors.

it gave the follwoing errors

Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:97056) to (1:105143).

We also tried repairing the database with REPAIR_FAST and REPAIR_AllOW_DATA_LOSS but it gave the following error.

Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:97056) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
Server: Msg 8921, Level 16, State 1, Line 2
CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 0, index ID 0, page (1:97056). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 0, index ID 0, page (1:97056). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 0, index ID 0, page (1:97056). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 0, index ID 0, page (1:97056). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 0, index ID 0, page (1:97056). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 0, index ID 0, page (1:97056). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.

:

:.
The system cannot self repair this error.


Please show some directions in how to repair this database.



Jasbir

chadmat
The Chadinator

USA
1974 Posts

Posted - 08/26/2012 :  23:45:07  Show Profile  Visit chadmat's Homepage  Reply with Quote
Restore from backup.

-Chad
Go to Top of Page

JasbirSingh120
Starting Member

USA
3 Posts

Posted - 08/27/2012 :  12:39:43  Show Profile  Reply with Quote
Chad,
The good backup copy of database is not availiable, The Last copy is also corrupted.

Jasbir
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 08/27/2012 :  13:06:08  Show Profile  Visit robvolk's Homepage  Reply with Quote
In that case I'd suggest the following:

1. Script out your database schema
2. Create it in a new, separate database, on a different SQL Server if possible
3. bcp out every table you can from the original database
4. bcp in these files into the new database

Make sure not to modify (write to) the original database files in any way.
Go to Top of Page

JasbirSingh120
Starting Member

USA
3 Posts

Posted - 08/28/2012 :  13:34:57  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

In that case I'd suggest the following:

1. Script out your database schema
2. Create it in a new, separate database, on a different SQL Server if possible
3. bcp out every table you can from the original database
4. bcp in these files into the new database

Make sure not to modify (write to) the original database files in any way.




I tried BCP out ever table in the infected database. For some tables I have received following errors. is it possible to have any other steps

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Any help will be highly appreciated


Jasbir
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 08/28/2012 :  14:03:09  Show Profile  Visit chadmat's Homepage  Reply with Quote
If you have no backups, then you may be out of luck. You can try calling Microsoft Support and having a corruption specialist try to help you, but there would be no guarantees. If you have old backups, and backup the T-Logs, (And retain a certain amount of those backups) you should be able to restore to the point just before the DB became corrupted. It would take some effort, but you could do it. Sounds like you don't have those though, in which case, Lesson learned...The hard way.

-Chad
Go to Top of Page

prett
Posting Yak Master

USA
181 Posts

Posted - 09/03/2012 :  04:16:23  Show Profile  Visit prett's Homepage  Reply with Quote
Your database is get corrupted. Please follow this link to repair corrupt SQL Server database:
http://www.sqlservercentral.com/articles/Corruption/65804/

If the steps does not work then try third party SQL Repair software: https://www.google.com/#hl=en&output=search&sclient=psy-ab&q=sql+repair+software
Go to Top of Page

davegeeit
Starting Member

11 Posts

Posted - 11/28/2012 :  01:59:54  Show Profile  Reply with Quote
Checkout this below mansion utility that will you in repair your sql dfatabase. Good thing about this software is that this company offering a free trial version of software.
unspammed

Thanks
Go to Top of Page

rodyroon
Starting Member

6 Posts

Posted - 04/05/2013 :  08:21:15  Show Profile  Reply with Quote
Hello,

In this situation i want to suggest you for a third party SQL Database Recovery Tool,Kernel for SQL Recovery software such tool that is not only easy to use but its interactive and user-friendly screens makes this tool a favorite among novice computer users as well.You can try this tool.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000