ms sql 2008 lock request time out period exceeded

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. 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. I found that another SPID from SQL Server Management Studio was holding onto the table I was trying to alter. Where object_name(a.rsc_objid) is not null Select distinct object_name(a.rsc_objid), a.req_spid, b.loginameįrom a (nolock) join 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: Now I needed to find out what is blocking my Alter Table command from running. R.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_levelĬROSS APPLY sys.dm_exec_sql_text(r.sql_handle) sĪnd this in the particular row in question that made me realize my query was waiting on something else: SELECT r.session_id, r.status, r.start_time, r.command, s.text, This is the sql query I used to see the currently executing requests (one of which was mine): 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. While there may be a short delay, DBeaver is able to display all objects even when SQL Server is not able to display anything at all. When I encounter the issue described in the question in SSMS, I find DBeaver doesn't get blocked. 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. SSMS is great for SQL Server while DBeaver is great for PostgreSQL and passable for SQL Server.

I also tried dropping the foreign key manually using:ĪLTER TABLE MyTable DROP CONSTRAINT fk_MyForeignKeyīut this time the query was just sitting there running and running. ()Īn exception occurred while executing a Transact-SQL statement or batch. TITLE: Microsoft SQL Server Management Studioĭrop failed for ForeignKey 'fk_MyForeignKey'. 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: SQL Server SQL Server 2008 SQL Server Management Studio

Ms sql 2008 lock request time out period exceeded