Wednesday, November 12, 2008

Lock request time out period exceeded

Got this error today from one of my databases I'm working on. Last night I added an index to one of my tables not realizing that it may result to slow inserts. Well, as my SSIS package ran early in the morning, it's taking time for it to finish. So I had to resort to brute force to stop it. Problem is when I did that, lock for that particular table wasn't automatically released, so how do I release it? Here's how...

-- list processes which are locked
exec sp_lock

-- find out dbid of database
-- try each of the dbids to find out
-- in this case '9' is the dbid of my database
select db_name(9)

-- look for the 'TAB' (table) type
-- then kill that process
-- to find the table, try ObjId's
USE <YourDb>
SELECT OBJECT_NAME(<ObjId>)

-- now kill
kill 57

1 comments:

Adeel Nayyer said...

Thanks brother. Got into this situation and was scratching my hair to find its solution.

Thanks a lot. :)