3

if i apply a sql query statement to this table "select * from context.tablename", all i see is the "Executing query.." message and nothing else hapens. I also can not truncate or drop this table. It prevents me from even dropping vhole database.

Is it possible, that this problem is caused by inserted data into this table???

I'm using MS Sql server 2005

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
dani
  • 469
  • 3
  • 12
  • 23
  • I think database structre is corrupted. I mean mdf file. – x2. Sep 14 '09 at 07:11
  • how can i fix this? PS: The data in database is unimportant. – dani Sep 14 '09 at 07:14
  • There are some programs for recover corrupted files but i'm not sure it can help. If you want to delete database, you can try to detach database and then delete files. – x2. Sep 14 '09 at 07:20

4 Answers4

2

Have you tried restarting the SQL Server service?

Dimi Takis
  • 4,924
  • 3
  • 29
  • 41
0

Are there any open connections to the database while trying to drop the table? Have you tried closing all connections before dropping it?

J.R. Garcia
  • 639
  • 5
  • 20
  • My next guess would be restarting the service like Greco suggested. If that doesn't work, I would try running DBCC CHECKDB and see if the database is corrupt. It very well may be the data. Also, is this SQL Server being replicated or mirrored? – J.R. Garcia Sep 14 '09 at 07:42
0

Use SSMS to detach the database. Delete the MDF files manually.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • if i try to detach i get this error message: "Process ID -2 is not a valid process ID. Choose a number between 1 and 2048" – dani Sep 14 '09 at 07:35
0

I had this same exact problem with a table that only contained 3 records. I had several query tabs open in MS SQL Server Mgmt Studio that each referenced the target table in some way, but none of which had any queries executing.

Selecting from this table would just execute endlessly as would Dropping the table. I could run sp_spaceused and see it was only using 86k of space so it wasn't huge.

I disconnected all of the tabs including the one I was using to Select from and to Drop the table. I then reconnected just that window and ran 'Select * from' my table and it worked. I was also able to then Drop the table successfully.

It seems there was an open connection holding the table hostage somehow and preventing my Select and Drop from working.

Will Knight
  • 1