Home > Mobile >  Unable to drop/delete table in SQL Server Management Studio
Unable to drop/delete table in SQL Server Management Studio

Time:06-08

I'm unable to delete the table, as it shows the error below:

Drop failed for Table "dbo.CustomerTest"

enter image description here

Please advise what should I do to delete this table?

CodePudding user response:

There is lock happening on the table and you can find out the session locking the table using sp_lock.

Here, We are trying to mimic lock scenario.

SELECT * FROM dbo.test(a INT) 

insert into test values(1),(2);

BEGIN TRANSACTION

update TEST set a = 3 where a =1

Now, if we call sp_lock in another session, we will come to know the session, which is apply lock.

declare @LOCKS table(
spid smallint, dbid smallint, objid int, indid smallint, type nchar(4), resource nchar(32),
mode nvarchar(8), status nvarchar(5))

insert into @locks
exec sp_lock
select spid, db_name(dbid) as databasename, OBJECT_NAME(objid) as tablename,type,resource,mode, status from @locks
spid databasename tablename type resource mode status
60 WideWorldImporters NULL DB S GRANT
60 WideWorldImporters test RID 3:8432:0 X GRANT
60 WideWorldImporters test PAG 3:8432 IX GRANT
60 WideWorldImporters test TAB IX GRANT

If you want to kill the session, you have to kill it accordingly.

kill 60 -- session holding lock on the table
  • Related