DROP/Truncate Table Hangs

_{Thursday, June 13, 2019 5:39 PM}

Hi

When I'm trying to drop or truncate certain empty table, it is hanging for ever. But I'm able to delete the same table.

I'm also able to drop/truncate other tables in same db and schema.

How to identify, what is causing the lock on this table?

Thanks

Royal Thomas

_{Thursday, June 13, 2019 6:06 PM ✅Answered}

It seems to me that table is locked!

You can check what is blocking your drop/truncate command looking as sys.sysprocess or you can use sys.dm_tran_lock to find out locks on your tables.

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

_{Thursday, June 13, 2019 7:10 PM ✅Answered}

Sch-M means, a transaction has held the table for Schema-Modification. That's the reason you are not able to drop the table. Try to find out from which process held this lock (sys.sysprocess) and what is going on? (try to check sys.dm_exec_input_buffer(<session id>))

_{Thursday, June 13, 2019 10:11 PM ✅Answered}

How to release this Sch-M lock?

Note that this lock has status WAIT. That is, it has not been granted.

Then again, there is an IX lock on the table as well, and this lock is liekly to be what is blocking the process that wants the Sch-M operation.

An IX lock is an "intent lock". That's a silly name, it really means "I'm down here somewhere". That is, a process holds a lock on one more rows in the table. That lock does not show up in the query that Loskeh posted, as the lock is on an index key and not on the entire table.

You can kill the blocking process, but you first want to find out what is is actually doing. You find the spid for this process in the query output; that is the colunm request_session_id.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Thursday, June 13, 2019 6:03 PM}

When you say hangs did you checked whether it was blocked ? Drop table would need schema modification lock on table so if any one is accessing the table the alter command would be blocked, OTOH delete takes row level locks ( assuming no lock escalation to table level) so other queries might not be blocking it

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

_{Thursday, June 13, 2019 6:33 PM}



Royal Thomas

_{Thursday, June 13, 2019 6:56 PM}

Hi Lokesh

How to release this Sch-M lock?

Royal Thomas