As soon as I closed that window (and canceled that transaction) then I could drop the foreign key without a problem. 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. 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. 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. 'SQLException: Lock request time out period exceeded' errors occur when publishing artifacts to Rule Team Server (RTS) or saving modifications through its console when the database is Microsoft SQL Server. ()Ī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: Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.SQL Server SQL Server 2008 SQL Server Management Studio If the response is helpful, please click " Accept Answer" and upvote it, as this could help other community members looking for similar queries. If you are running this in SQL Server Management Studio, you can close the query windows and it will automatically close the transaction.įor more information, please see this article.To prevent this, make sure every BEGIN TRANSACTION has COMMIT.Run big Transaction in smaller transactions.Modify the Transaction use query hints (use RECOMPILE, MAXDOPhints).If there is no abnormality, you can try the following solution: Maybe you can find the reason for your lock timeout. So you can use sp_who2 to check for all session that are currently established in the database and check for any with blocking, high CPU usage, high I/O usage, or any with multiple entries for the same SPID. In some cases, a query will fail with the lock request time out period error after waiting for more than 10ms. Msg 1222, Lock request time out period exceeded Please see this MS link, which describes the possible scenarios for this error. Msg 50000, Level 16, State 1, Server BIDSSql, Procedure CommandExecute, Line 167
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |