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?

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#=101248
8
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 mor
e
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?sql

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 and recreate all indexes

Sql server 2005
There are a couple of administrators who have repeatedly said they
dropped and recreated indexes
and performance spiked after a migration and it would be nice to leave
no stone unturned in a bid to better performance.
Has anyone come across a script or has a way to do this
Your input as usual is greatly appreciated
Mike
There are many flavors out there... but this is a pretty common way to
reindex your tables.
The DBCC DBREINDEX statement is the key!
!UNTESTED SQL!
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161357089.891287.7040@.e3g2000cwe.googlegroup s.com...
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>
|||Hi
Check out ALTER INDEX ALL in Books Online, example D in the topic
sys.dm_db_index_physical_stats shows you how you can call this for multiple
tables http://msdn2.microsoft.com/en-us/library/ms188917.aspx
John
"Massa Batheli" wrote:

> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>
|||Thank you so much Immy.
Just ran something similar and the next step was to run a drop index
...
and recreate index ...
That is what is help is needed with ,again thank you for your time and
I appreciate more ideas
|||Hi
DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
you are writing production code you should consider using ALTER INDEX.
John
"Massa Batheli" wrote:

> Thank you so much Immy.
> Just ran something similar and the next step was to run a drop index
> ...
> and recreate index ...
> That is what is help is needed with ,again thank you for your time and
> I appreciate more ideas
>

Drop and recreate all indexes

Sql server 2005
There are a couple of administrators who have repeatedly said they
dropped and recreated indexes
and performance spiked after a migration and it would be nice to leave
no stone unturned in a bid to better performance.
Has anyone come across a script or has a way to do this
Your input as usual is greatly appreciated
MikeThere are many flavors out there... but this is a pretty common way to
reindex your tables.
The DBCC DBREINDEX statement is the key!
!UNTESTED SQL!
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161357089.891287.7040@.e3g2000cwe.googlegroups.com...
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Hi
Check out ALTER INDEX ALL in Books Online, example D in the topic
sys.dm_db_index_physical_stats shows you how you can call this for multiple
tables http://msdn2.microsoft.com/en-us/library/ms188917.aspx
John
"Massa Batheli" wrote:
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Thank you so much Immy.
Just ran something similar and the next step was to run a drop index
...
and recreate index ...
That is what is help is needed with ,again thank you for your time and
I appreciate more ideas|||Hi
DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
you are writing production code you should consider using ALTER INDEX.
John
"Massa Batheli" wrote:
> Thank you so much Immy.
> Just ran something similar and the next step was to run a drop index
> ...
> and recreate index ...
> That is what is help is needed with ,again thank you for your time and
> I appreciate more ideas
>|||As said earlier John the purpose is to completely drop and rebuild
indexes
Not sure why that has to be done but still looking for ways to do that
on instructions
Reason for this post
.....
John Bell wrote:
> Hi
> DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
> you are writing production code you should consider using ALTER INDEX.
> John
> "Massa Batheli" wrote:
> > Thank you so much Immy.
> > Just ran something similar and the next step was to run a drop index
> > ...
> > and recreate index ...
> > That is what is help is needed with ,again thank you for your time and
> > I appreciate more ideas
> >
> >|||DBCC DBREINDEX and ALTER INDEX with the REBILD option will execute the same code internally as if
you do DROP INDEX and then CREATE INDEX.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161806390.411989.59800@.i42g2000cwa.googlegroups.com...
> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> .....
> John Bell wrote:
>> Hi
>> DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
>> you are writing production code you should consider using ALTER INDEX.
>> John
>> "Massa Batheli" wrote:
>> > Thank you so much Immy.
>> > Just ran something similar and the next step was to run a drop index
>> > ...
>> > and recreate index ...
>> > That is what is help is needed with ,again thank you for your time and
>> > I appreciate more ideas
>> >
>> >
>|||Hi
Rebuilding indexes should certainly be done post upgrading to SQL 2005, and
you should periodically rebuild your indexes to remove fragmentation to make
sure that will perform efficiently. You should also look at updating
statistics and usage. Check out the view sys.dm_db_index_physical_stats in
books online, which will give you an example script for rebuilding indexes if
they are fragmented by a certain amount.
John
"Massa Batheli" wrote:
> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> ......
> John Bell wrote:
> > Hi
> >
> > DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
> > you are writing production code you should consider using ALTER INDEX.
> >
> > John
> >
> > "Massa Batheli" wrote:
> >
> > > Thank you so much Immy.
> > > Just ran something similar and the next step was to run a drop index
> > > ...
> > > and recreate index ...
> > > That is what is help is needed with ,again thank you for your time and
> > > I appreciate more ideas
> > >
> > >
>

Drop and recreate all indexes

Sql server 2005
There are a couple of administrators who have repeatedly said they
dropped and recreated indexes
and performance spiked after a migration and it would be nice to leave
no stone unturned in a bid to better performance.
Has anyone come across a script or has a way to do this
Your input as usual is greatly appreciated
MikeThere are many flavors out there... but this is a pretty common way to
reindex your tables.
The DBCC DBREINDEX statement is the key!
!UNTESTED SQL!
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161357089.891287.7040@.e3g2000cwe.googlegroups.com...
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Hi
Check out ALTER INDEX ALL in Books Online, example D in the topic
sys.dm_db_index_physical_stats shows you how you can call this for multiple
tables http://msdn2.microsoft.com/en-us/library/ms188917.aspx
John
"Massa Batheli" wrote:

> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Thank you so much Immy.
Just ran something similar and the next step was to run a drop index
...
and recreate index ...
That is what is help is needed with ,again thank you for your time and
I appreciate more ideas|||Hi
DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
you are writing production code you should consider using ALTER INDEX.
John
"Massa Batheli" wrote:

> Thank you so much Immy.
> Just ran something similar and the next step was to run a drop index
> ...
> and recreate index ...
> That is what is help is needed with ,again thank you for your time and
> I appreciate more ideas
>|||As said earlier John the purpose is to completely drop and rebuild
indexes
Not sure why that has to be done but still looking for ways to do that
on instructions
Reason for this post
.....
John Bell wrote:[vbcol=seagreen]
> Hi
> DBCC DBREINDEX may be removed in future versions of SQL Server, therefore
if
> you are writing production code you should consider using ALTER INDEX.
> John
> "Massa Batheli" wrote:
>|||DBCC DBREINDEX and ALTER INDEX with the REBILD option will execute the same
code internally as if
you do DROP INDEX and then CREATE INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161806390.411989.59800@.i42g2000cwa.googlegroups.com...
> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> .....
> John Bell wrote:
>|||Hi
Rebuilding indexes should certainly be done post upgrading to SQL 2005, and
you should periodically rebuild your indexes to remove fragmentation to make
sure that will perform efficiently. You should also look at updating
statistics and usage. Check out the view sys.dm_db_index_physical_stats in
books online, which will give you an example script for rebuilding indexes i
f
they are fragmented by a certain amount.
John
"Massa Batheli" wrote:

> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> ......
> John Bell wrote:
>