SQL Server "not responding" on drop table

[firsttube]

I'm trying to delete a table from SQL Server and when I execute the SQL through Query browser:

drop table dbo.tablename

it says it is executing query batch, but it just sits there. I let it go for 5 min and it did not delete the table. Why would this happen?



Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[fredericofonseca]

it if it a big table, with several indexes, then it will take a long time to do, as it need to keep a rollback point in case it fails.

To have a table "dropped" fast, use TRUNCATE first, then drop.

To use TRUNCATE the user issuing the command must be a member of the SYSADM roles.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mrdenny]

To truncate a table the user needs to be a member of the db_owner fixed database role, not the sysadmin fixed server role.

If a user is using the table for something then you will need to wait until that user is finished and has released there locks before the table can be dropped. If the table is large then it will take time as Frederico said.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com