AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
![]() ![]() ![]() When a wait for a lock exceeds the time-out value, an error is returned. It says find the offending transaction and terminate it and run the query again. However this seems impossible and I do not know why. A value of -1 (default) indicates no time-out period (that is, wait forever). ERROR 1222: Lock request time out period exceeded. What causes 'lock request time out period exceeded' when droping a column Ask Question Asked 5 years, 11 months ago Modified 5 years, 11 months ago Viewed 18k times 1 I created some columns in our production database but now I need to drop one. ![]() After this your query for the drop table/Column should complete. Is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. If you deem that query inconsequential, then you can kill it using kill. Once you identify this, you can use dbcc inputbuffer(spid) to find out what query/command it is doing that has taken a lock on this table. Next query to see who the owner of the spid is (this will tell you who owns the conflicting lock on this table). Look at the blocked column and note down the spid number. Once this goes into "executing" stage, open another query window and do this, use master Note down the spid of this query window (it should be visible in the bottom of SSMS, right next to username). I ran sp_who2 and did not see anything in the BlkBy column.ĭrop the table using T-SQL as then the query will not timeout but instead get blocked. WHERE l.resource_associated_entity_id = o.object_id To resolve this behavior, use one of the following methods: Click to clear the Override connection string time-out value for table designer updates check box for the table designer and for the database designer in SQL Server Management Studio. Resource_description, o.object_id, o.name, o.type_desc Request_status, request_mode,request_session_id, Using this query specifically: SELECT resource_type, resource_associated_entity_id, I checked for locks using a query found here: ALTER TABLE VEN DROP COLUMN RI_VEN_Approved I used management studio (which fails quickly) and T-SQL (which takes longer but still fails). But when I try to remove this one column I get the error. I created a table in production and dropped that. I dropped the column in our development database just fine. ![]() However this seems impossible and I do not know why. OS windows10 pro SQL Server2019 Express SSMS 18.7. It's on SQL Server 2014 if it matters.I created some columns in our production database but now I need to drop one. It's highly unlikely that it was clashing with another transaction, as the system was brought down for the update. Any thoughts or ideas would be appreciated. I ran it on another database where the number of rows is smaller (~20m)and it ran just fine. INNER JOIN RealTable ON RealTable.PrimaryKeyID = #Update.PrimaryKeyID By default it uses repeatable.read, but you can change this in the config. To do this it takes a lock on the database, and it looks like this is failing: SQLServerException: Lock request time out period exceeded. I_ID int PRIMARY KEY NOT NULL IDENTITY(1,1), When you run the connector for the first time it will take a snapshot of all of the tables. Here is the pseudo code: BEGIN TRANSACTION Tried to run the script two times now, the first time it broke after updating roughly 17m rows, the second time it stopped at 14m.Įrror message: Msg 1222, Level 16, State 43, Line 122 The updated is broken in batches, with checkpoints to allow clearing the transaction log. I'm running long bulk update script which is updating ~50m records. I tried to find something similar, but couldn't so decided to ask directly. ![]()
0 Comments
Read More
Leave a Reply. |