Drop table error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Drop table error

Author

Topic

Kleber
Yak Posting Veteran

67 Posts

Posted - 2003-11-14 : 15:34:58

Hi all

I have MSSQL 7.0 with a corrupted table. When i try to drop the table i receive this error:
Server: Msg 3624, Level 20, State 1, Line 1

Location: alloc.cpp:8658
Expression: start_pg != PageId_NULL
SPID: 15
Process ID: 150

Connection Broken

What is really happening and how may i drop it?

Regards
Kleber

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-14 : 15:38:08

It sounds like you've got a corrupted database. Hopefully it is isolated to that one table. Before you do anything else, run DBCC CHECKDB to see what the extent is. DBCC CHECKDB also has some repair options that you can run. Check out BOL for details.

Also check out MS' knowledge base. Search on "Server: Msg 3624, Level 20, State 1, Line 1"

http://support.microsoft.com/

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-14 : 15:39:02

Did you try DBCC CHECKDB or DBCC CHECKTABLE?

Did you look in the error log?

Brett

8-)

Kleber
Yak Posting Veteran

67 Posts

Posted - 2003-11-14 : 15:43:52

When i run DBCC CHECTABLE i receive the following:
Server: Msg 7965, Level 16, State 2, Line 1
Table corrupt: Could not check object ID 1229351544, index ID 0 due to invalid allocation (IAM) page(s).
Server: Msg 8968, Level 16, State 1, Line 1
Table Corrupt: IAM page (1:2668) (object ID 1229351544, index ID 0) is out of the range of this database.
DBCC results for 'Tbl_Rel_Acumulador1'.
There are 0 rows in 0 pages for object 'Tbl_Rel_Acumulador1'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Tbl_Rel_Acumulador1' (object ID 1229351544).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DtbSaldo.dbo.Tbl_Rel_Acumulador1 ).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-14 : 15:46:04

Also run DBCC CHECKDB. The corruption might be in other tables.

If it is isolated to that table, run DBCC CHECKTABLE with repair_allow_data_loss as the message mentions. Keep in mind that you could lose data with this option. If you are not willing to do that, you might have to restore from a full backup and then restore to a point in time where the corruption does not exist. The problem is that you know what time the corruption started, so you'll have to do multiple restores until you find the time closest to the problem.

Have any hardware changes been made? Do you have plenty of free disk space on the database server?

Tara

Kleber
Yak Posting Veteran

67 Posts

Posted - 2003-11-14 : 15:48:45

I don't need this table anymore. I want to drop it. And my precious SQL Server do not allow me to do this.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-14 : 15:50:58

You have bigger problems than just wanting to drop a table...

Do you have a backup?

Brett

8-)

Kleber
Yak Posting Veteran

67 Posts

Posted - 2003-11-14 : 15:56:11

Well.

I don't have backup. This server is 600 miles away from me now. And i must correct this anyway.
So...
any sugestions?

Kleber

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-14 : 16:17:02

Are you sa on the box?

Can you create a new db?

Can you do this

SELECT * INTO newTable FROM OldTable

Probably not..

did you do DBCC CHECKDB?

How many tables in the database in total?

How big is the database?

Brett

8-)

Kleber
Yak Posting Veteran

67 Posts

Posted - 2003-11-14 : 16:29:07

After DBCC CHECKDB i received it:

Server: Msg 8906, Level 16, State 1, Line 1
Page (1:2665) 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'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:2667) 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'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:3692) 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 80_PCT_FULL'.
DBCC results for 'dtbsaldo'.
...
...
CHECKDB found 4 allocation errors and 1 consistency errors in database 'DtbSaldo'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DtbSaldo ).
DBCC execution completed.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-14 : 16:37:38

Read up on DBCC CHECKDB in Books online...

There are options to assist you in repairing the database...

But

quote:

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE

I don't think I've seen that before...

btw, I've only seen 1 corrupt database before, and it was 6.5

You should take a dump of the database now.

Always be able to at least get back to where you are...even if it's corrupt..

Anyone know if he restore a corrupt db if things get out of hand...

I thinkl you can...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-14 : 16:54:00

I have run repair_allow_data_loss on a corrupted database where we could afford to lose the data because we could get a copy of the lost data from a different data source. I can't remember how many rows that we lost, but it wasn't that many. Without a backup, I hope you've got some other source for your data. Now there your database is hosed and without a backup, you'll need to export your rows into a different database. This is very time consuming and tedious.

Do not just let the corruption remain in the database just because it is for a table that you don't need.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources