?
Solved

Can not drop table

Posted on 2006-05-31
13
Medium Priority
?
230 Views
Last Modified: 2007-12-19
Apprantly a table has become corrupt my SQL Server 2000 database. I can't delete rows out of it, drop it, rename it... nothing. Any ideas how I can get rid of this sucker?
0
Comment
Question by:davidsanderson
  • 7
  • 5
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16799043
I think you dont have enough permissions to do this, What error message you are getting
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799046
How do you know this table is corrupt? Are you receiving an error message indicating that? have you done a DBCC CHECKDB () within the database that table is in?

Can you select data from the table?

0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799057
I am with aneesh on that one. That is where I was heading once he suggested the error message. It may very likely be a permissions issue. It could be an integrity constraint issue.. It could be a few things, but I doubt it is a "corrupt table"
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:davidsanderson
ID: 16799170
I just dropped some other related tables that I created along with the one that I can not delete. When I try to select rows from it, delete it, rename it, it just hangs and I have to end the sql server manager task.

DBCC CheckDB:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'SeaGateContracts'

DBCC CheckTable:
There are 0 rows in 4114 pages for object 'TL_tblInvoiceJob'

0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799346
I doubt you have corruption. How many rows are in this table? (Select count(*) from table).. do you have an foreign/primary key integrity setup with this table?
0
 

Author Comment

by:davidsanderson
ID: 16799397
Mike, nothing happens, it just hangs when I try to select rows.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799451
How long does it hang for? Run "SELECT COUNT(*) FROM table" and wait for results and report them back here.

Stop and Restart SQL if nothing critical is running on the server as well. Verify through enterprise manager that you have proper permissions on the table also.
0
 

Author Comment

by:davidsanderson
ID: 16799463
I think I waited up to 15 minutes so far with no results. It hangs when I try to look at current activity too.... maybe I just need to reboot the server.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799488
I would strongly reccomend that. What SP are you on also? You should be on the latest. If you are in SQL Server 2000 that is SP4.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799497
At this point, it seems like corruption is not likely your problem. I wonder if you have some locking/blocking going on causing issues. I would reboot and restart..

You can also run "SP_WHO2" from query analyzer and look at the blocking column..
0
 

Author Comment

by:davidsanderson
ID: 16799516
Actually the current activity eventually came up.
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 500 total points
ID: 16799551
ok.. it sounds like you are having issues with your server, not your table. I would suggest the reboot, and then look at your activity and then try and drop the table.
0
 

Author Comment

by:davidsanderson
ID: 16799599
Well I feel dumb now, there was just a block on the drop table command. As soon as I cleared the block, the table dropped. Thanks for helping me anyway Mike.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 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