Solved

Database has a Corrupt Table...

Posted on 2001-08-30
13
226 Views
Last Modified: 2008-02-26
Hi All,

   The result of running DBCC NEWALLOC in SQL 6.5 showed the following error message:

***************************************************************
TABLE: syslogs  OBJID = 8
INDID=0  FIRST=868010  ROOT=868010  DPAGES=262146 SORT=0
Msg 2503, Level 16, State 1
Table Corrupt: Page linkage is not consistent; check the following pages: (current page#=868010;  page# pointing to this page=0; previous page# indicated in this page=868011)
 Data level: 0.  0 Data  Pages in 1 extents.
TOTAL # of extents = 0
***************************************************************


The table is corrupt. The option or remeady I found was it said restore the database from the previous known good backup. I dont want to do that!!! Is there anyway I can solve this without resoration. I have important data. If there is an option can sombody tell me a detailed steps repair this table.

Thanks,
Ananth.
0
Comment
Question by:ananthu
13 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6440035
If you have indexes on the table then you can get the data around the corrupt area probably.

try select * from tbl

This will tell you where the corrupt area is.
If you have a clustered index then select from the table using that index you shuold be able to access a page before the corrupt are and a page after it.

Now create two views one selecting the data before the corruption and one after.
Now bcp the data out into files from these views - then bcp back from the files into a new table and you will have all the data except that which is corrupt.
(you could do the first bcp with just batch size 1 and it will stop at the corruption)
0
 

Author Comment

by:ananthu
ID: 6440062
This is a syslogs table no indexs...
0
 

Author Comment

by:ananthu
ID: 6440083
This is a syslogs table no indexs...
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6440255
Oops - sorry, that's the transaction log.
That means that the system can't reliably recover the database - i.e. it won't be able to commit/rollback transactions.

Best bet as it says is to restore the backup - you can restore transaction log backups too if you have them to get clse to the live state, but you won't be able to back up any more transactions since the last backup so that's the best you can do.

You could try creating a new database of the same size on another server and copying the data dat file over it but doubt if it would work - and you would have to be doubtful about the state of the data anyway.
0
 

Author Comment

by:ananthu
ID: 6440352
This is a syslogs table no indexs...
0
 

Author Comment

by:ananthu
ID: 6440365
i dont know how the previous comment was added wait for me thatnks
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:ananthu
ID: 6455477
Hi All,

     Can anybody try on this and let me know if it is possible to recover a corrupt table without restoring the database. The error is 605 severity 21.

Thanks,
Ananth.
0
 

Author Comment

by:ananthu
ID: 6459620
Hi All,

Error 605 severity 21 has occured. Can anybody tell me what are the conditions in which this error occurs. There are NO Dynamic Cursors or sp_cursorfetch and sp_cursoropen used in any of the stored procs in this database.

We are basically trying to replicate this which we could not. The corrupt table was undergoing a continues truncation and mass insertion and updation.

Thanks,
Ananth.
0
 

Author Comment

by:ananthu
ID: 6473979
Nobody has answer to this!!!
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 200 total points
ID: 6476612
If the transaction log is corrupt then the database cannot guarantee it's integrity and you will need to restore a backup.
0
 

Expert Comment

by:CleanupPing
ID: 9281699
ananthu:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9807541
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Award points to nigelrivett

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Anthony
EE Cleanup Volunteer
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now