0

How we could find tables which are currently under delete operation or locked for delete operation?

I wanted to list such table. Is there any way to list out?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vignesh
  • 1,414
  • 5
  • 19
  • 38
  • See the list of running processes and find the one executing drop table? – marekful Jan 09 '18 at 05:56
  • Have a look at this SO question. Almost answer your question https://stackoverflow.com/questions/8749426/how-to-find-what-is-locking-my-tables – J-D Jan 09 '18 at 06:41

1 Answers1

0

I have used this code for a similar situation, Let me know if it worked fine :

SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )
          and cmd like '%DELETE%'
Hadi Ardebili
  • 99
  • 1
  • 7
  • This wont show sessions which have executed a delete in a transaction but not yet committed if the delete isn't blocking. – Steve Ford Jan 09 '18 at 11:29