SQL Server hangs on drop foreign key
10-Jul-1414 Leave a comment
This actually happened on Azure during part of a SSIS SQL Server integration package, removal of key before delete and upload of data
Following post was very useful
http://weblogs.asp.net/jeffwids/lock-request-time-out-period-exceeded
To find sessions then
SELECT r.session_id, r.status, r.start_time, r.command, s.text,
r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
then Kill session id, but you cannot kill your own session, so open another session to do the kill
End.