Tuesday, March 27, 2012

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?

No comments:

Post a Comment