dropping corrupt tables?

I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?

Drop table / Allocation errors

I am getting this allocation error on sql 7.0 which won't go away with checkdb (repair_allow_data_loss). I had a suspicion about one table. After dropping this particular table CHECKDB(repair) removed all the allocation errors. The application people are dead against dropping the table. I was wondering if there is any way around this problem short of restoring. I have already tried dbreindex. Checktable does not return any error on this table. This table holds all the transaction logs for the application.

Server: Msg 8906, Level 16, State 1, Line 0
Page (1:656) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.

thx,

BXWhy not transfer the data to another table (DTS, BCP, EIEIO), drop the bad table, repair the database, then recreate the table and transfer the data back? Off hours, of course.

Richard

Originally posted by bxmakin
I am getting this allocation error on sql 7.0 which won't go away with checkdb (repair_allow_data_loss). I had a suspicion about one table. After dropping this particular table CHECKDB(repair) removed all the allocation errors. The application people are dead against dropping the table. I was wondering if there is any way around this problem short of restoring. I have already tried dbreindex. Checktable does not return any error on this table. This table holds all the transaction logs for the application.

Server: Msg 8906, Level 16, State 1, Line 0
Page (1:656) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.

thx,

BX