Trouble dropping a table!

[Ads]

We had a weird problem yesterday. There is this particular table with only about 6000 rows. If I double click on the table in EM – it shows me the total no of rows, but if I do a select count(*) from dbo.teacher, the query would keep running for. I tried to export the data to a text file, & the process would go into sleeping forever! It would not let me drop the table either. I tried doing a Truncate – that process hanged also. What could have been the problem? I could open the table in EM & look at the data, but just any queries to it from QA or the application would hang forever. Finally I had to drop the table from sysobjects! I know that’s not the best way to drop a table, but this was an emergency!…What could I do or check for in such a situation if it happens again?? Thanks in advance !

[rmiao]

Check with sp_who2 to see if it's blocked by other process.

[Ads]

well, i did run sp_who2 & there were no blocked processes.
Is there any way of finding out what activity is going on with a particualar table??

Thanks!

[rmiao]

You can trace it with profiler.

[MAK]

try doing this

select count(*) from dbo.teacher (nolock)

if you can see all the data it means the table has been locked by some open connection.
if it is not production server recycle the sql server service.

[Ads]

hmm.. okay guys - thanks! i hope I dont run into this prob again - but if i do, at least i know where/what to start checking...
Thanks