DB Lock 걸렸을 때 해결법!

DB Lock 발생 시, 문제 해결을 위한 유용한 정보와 해결 방법을 자세히 안내해 드립니다.

회사에서 런칭 준비를 하면서 DB 변경 사항을 반영하려는데, ALTER TABLE 명령이 실행될 때마다 다음과 같은 에러메세지가 나면서 Timeout 나는 이슈가 있었다.

Msg 1222, Level 16, State 56, Line 1 Lock request time out period exceeded.
 

온갖 방법을 다 찾아보았는데, 해결되지 않아 구글링 해본 결과, 어렵게 해결법을 찾울 수 있었다.
https://weblogs.asp.net/jeffwids/lock-request-time-out-period-exceeded
 

I found that another SPID from SQL Server Management Studio was holding onto 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... and guess what!?!  It was me!  Smile

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.  As soon as I closed that window (and canceled that transaction) then I could drop the foreign key without a problem.
Hopefully this will help someone else in the future

select distinct object\_name(a.rsc\_objid), a.req\_spid, b.loginame
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req\_spid=b.spid
where object\_name(a.rsc\_objid) is not null

kill ${ spid }
 

(나중을 위해 저장 ..!)

이것도 읽어보세요