August 31, 2010 at 9:40 am
Hello, This morning deveploers complained that they are not able to select data from one particular table in DEV Env. only 2 developers. their select query was runnning and running.
when i check sysproceses on sql 2005 EE 64 bit, I found blocked column has value of -2.
Lastwaittype = Lock_M_S
WaitResource = KEY:23:3362532123(e5212312)
I told them to close all the connecteions,killed rest of the connections and when I tried the same thing again
there was a lock, at this time only I was present in the database.but still my query was running and running.
I didnt know about this -2 value but from developers talk, lot of updates happened on this table so I tried to DE-fragmenet
and i ran reorganise index and it ran fine but when i ran rebuild index, it failed saying 'Rebuild Failed' Lock request timed out.
I killed all connections
I thought if something is corrupted and I ran DBCC UPDATEUSAGE('db') WITH COUNT_ROWS and at that time i was able to see
TAB lock on the table.(nothing else was running at this time). The table that i was trying to select. I was able to select other tables though.
i stopped that and try to run dbcc checkdb but same error.
( this table has only 500 rows, one primary key and hence one clustered index)
So for temporary,since its DEV,
I decided to copy the data of this table to some other table, but unable to copy the data.
--I was unable to restore database (after killing all connections)but error was 'Exclusive lock can not be obtained on the db'
but in sysprocesses i was not able to see anyone.
I tried to set db in singleuser/restricted user with rollback immediate but no luck. I tried to rename the db but no luck.
I believe some one was holding a lock on that table. but i was not able to see him in sysprocesses so how can i kill him?? or remove this lock?
Does anyone has this kind of problem??
how can i see/kill/get rid of this lock?? Let me know thanks
August 31, 2010 at 12:19 pm
dallas13 (8/31/2010)
Hello, This morning deveploers complained that they are not able to select data from one particular table in DEV Env. only 2 developers. their select query was runnning and running.when i check sysproceses on sql 2005 EE 64 bit, I found blocked column has value of -2.
Lastwaittype = Lock_M_S
WaitResource = KEY:23:3362532123(e5212312)
I told them to close all the connecteions,killed rest of the connections and when I tried the same thing again
there was a lock, at this time only I was present in the database.but still my query was running and running.
I didnt know about this -2 value but from developers talk, lot of updates happened on this table so I tried to DE-fragmenet
and i ran reorganise index and it ran fine but when i ran rebuild index, it failed saying 'Rebuild Failed' Lock request timed out.
I killed all connections
I thought if something is corrupted and I ran DBCC UPDATEUSAGE('db') WITH COUNT_ROWS and at that time i was able to see
TAB lock on the table.(nothing else was running at this time). The table that i was trying to select. I was able to select other tables though.
i stopped that and try to run dbcc checkdb but same error.
( this table has only 500 rows, one primary key and hence one clustered index)
So for temporary,since its DEV,
I decided to copy the data of this table to some other table, but unable to copy the data.
--I was unable to restore database (after killing all connections)but error was 'Exclusive lock can not be obtained on the db'
but in sysprocesses i was not able to see anyone.
I tried to set db in singleuser/restricted user with rollback immediate but no luck. I tried to rename the db but no luck.
I believe some one was holding a lock on that table. but i was not able to see him in sysprocesses so how can i kill him?? or remove this lock?
Does anyone has this kind of problem??
how can i see/kill/get rid of this lock?? Let me know thanks
Try
"select * into new_tablename from old_tablename with(nolock)"
August 31, 2010 at 12:56 pm
not working
August 31, 2010 at 5:48 pm
You can query sys.dm_tran_locks or sp_lock, which can show you if there are any locks being held.
Also check if there are any open trans which have not yet been comitted / rolledback.
if the query was blocked by -2, this means that it was blocked by an orphaned distributed transaction. Check this link,
http://www.eraofdata.com/blog/2008/12/orphaned-msdtc-transactions-2-spids/
Hope this helps.
September 1, 2010 at 8:11 am
Hmm Thanks. I feel the same case as mentioned in this link. I am not able to see who is holding this lock and when i try to select the table or do any maintenance task it will show me as the lock on the same object. I already found http://support.microsoft.com/default.aspx?scid=kb;en-us;224453 where in point 4 its talking about distributed deadlocks and how to resolve it. and we cant see it inside sql server activity monitor and sql is not able to resolve these kind of locks automatically.
I felt very strange that we cant see a locking person in ssms-sysprocesses. and hence cant kill him.
Once again thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply