Lock request time out period exceeded
I was trying to drop a foreign key for a table I was working on and I ran into a time out exception from SQL Server Management Studio:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for ForeignKey 'fk_MyForeignKey'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
------------------------------
BUTTONS: OK
------------------------------
I also tried dropping the foreign key manually using:
ALTER TABLE MyTable DROP CONSTRAINT fk_MyForeignKey
But this time the query was just sitting there running and running. I let it run for some time and then when I checked the currently executing requests I found it was sitting in a suspended state. What was interesting about the request was that the wait_time equaled the total_elapsed_time, so it was just waiting there for something else before proceeding.
This is the sql query I used to see the currently executing requests (one of which was mine):
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
And this in the particular row in question that made me realize my query was waiting on something else:
Now I needed to find out what is blocking my Alter Table command from running. For that I used a query I found on this blog post Error 1222 Lock Request Time Out Period Exceeded When Set up Replication by Andrew Chen:
select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
where object_name(a.rsc_objid) is not null
I found that another SPID from SQL Server Management Studio was holding onto the table I was trying to alter. Using sp_who2 with the SPID showed me the owner and where it was coming from, and also that it had been holding onto the table for 2 hours... and guess what!?! It was me! 
I had been looking at the execution plan and client statistics of a query that I was performance tuning and that SQL Server Management window had a hold of the table I was trying to Alter. As soon as I closed that window (and canceled that transaction) then I could drop the foreign key without a problem.
Hopefully this will help someone else in the future!