I cannot drop a table in SQL Server 2008 r2. Also, I cannot restart the physical server

Question

I have a query that has worked perfectly fine the past 8 months. For some odd reason, today the query hangs and eventually times out no matter what constraint I put on it. This is very frustrating, but extremely curious on how this can be worked around.

Query is as follows:

DROP TABLE xxxx.dbo.stackoverflow;

Any help is greatly appreciated!

Can you restart the SQL Server service? Via computer management>services>Sql Server (...), then right-click & restart. — TT., Jan 12 '16 at 20:59
The restart option is grayed out, so unfortunately cannot do that even though I have admin access — Panthersfball234, Jan 12 '16 at 21:02
Well if you can't DROP the table, it must be locked somehow. — TT., Jan 12 '16 at 21:07
Check [this thread](https://stackoverflow.com/questions/8749426/how-to-find-what-is-locking-my-tables) on StackOverflow on "how to find what is locking my tables". — TT., Jan 12 '16 at 21:10
Did that help you solve your issue? Question: what is the reason you need to DROP that table so often? Can you not simply TRUNCATE the table? Or does the table structure change a lot? — TT., Jan 12 '16 at 21:42

score 1 · Accepted Answer · answered Jan 18 '16 at 22:07

UPDATE : The server was restarted from the admin who was out of town. If anyone encounters that issue in the future, I suggest either doing that, OR

type this query :

show full processlist;

after table shows, look for a query under your specific db, then get the ID from the Id column.

While the processlist is visible (in this example, the Id will be 140, type:

kill 140;

After removing all of the processes that have large time displays will definitely speed it up! Good Luck!

Isn't `show full processlist` a MySQL command? Your question is tagged for SQL Server. — James Z, Jan 30 '16 at 14:26

I cannot drop a table in SQL Server 2008 r2. Also, I cannot restart the physical server

1 Answers1