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)
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)

I also tried dropping the foreign key manually using:


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
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!  Smile

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!


Comments have been disabled for this content.