LOCK 발생 쿼리 찾기 및 Deadlock 발생 시 교착상태 추적 > db

본문 바로가기

db

LOCK 발생 쿼리 찾기 및 Deadlock 발생 시 교착상태 추적

페이지 정보

작성자 서방님 댓글 0건 조회 15회 작성일 07-12-18 09:30

본문

select * from sysprocesses

dbcc inputbuffer(293)

kill 293

 

추적 플래그 1204를 사용하면 교착상태(Deadlock)에 대한 내용을 확인하는 것이 가능합니다. 명령 프롬프트에서 추적 플래그를 추가하여 SQL Server 서비스를 시작할 수도 있고, 엔터프라이즈 관리자에서 SQL Server 시작 매개 변수에서 추적 플래그를 추가할 수도 있습니다.

명령 프롬프트에서 추적 플래그를 지정하는 방법

 

[따라하기]

  1. SQL Server 서비스를 중지해도 되는 시점에 SQL Server 서비스를 중지합니다.
  2. 다음과 같이 추적 플래그를 추가하고 SQL Server 서비스를 시작합니다.
    [주의] SQL Server 서비스를 시작한 명령 프롬프트의 창은 그대로 두어야 합니다. 명령프롬프트 창을 닫거나, 를 입력하면 SQL Server 서비스가 중지됩니다. [참고] sqlservr.exe 파일은 SQL Server 설치 폴더의 하위 폴더 중 하나인 binn에 있습니다.

    [예] 추적 플래그를 추가하여 디폴트 인스턴스 SQL Server 서비스를 시작하는 예제 (SQL Server 2000 서비스 팩3부터는 -T3605를 추가하지 않아도 ERRORLOG에 추적결과가 기록됩니다.)
    
    
  3. 위와 같이 작업하면 교착상태 추적 결과가 SQL Server 서비스가 시작된 콘솔 화면과 ERRORLOG 파일로 기록됩니다.


엔터프라이즈 관리자에서 추적 플래그를 지정하는 방법
  1. 엔터프라이즈 관리자에서 [속성] → [시작 매개 변수] "매개 변수"에 -T1204와 -T3605를 입력하고 [추가] 버튼을 클릭한 다음에 [확인] 버튼을 클릭합니다.
  2. SQL Server 서비스를 중지하고 재시작 합니다.
  3. 교착상태가 발생하면 교착상태 추적 결과가 ERRORLOG 파일로 기록됩니다.

[교착상태에 대한 추적결과 예]
추적 플래그 1204를 추가하고 SQL Server 서비스를 시작하면 교착상태에 대한 추적결과가 다음과 같은 형태로 반환 또는 기록됩니다.

2003-02-25 05:12:55.13 spid4
Deadlock encountered .... Printing deadlock information
2003-02-25 05:12:55.13 spid4
2003-02-25 05:12:55.13 spid4 Wait-for graph
2003-02-25 05:12:55.13 spid4
2003-02-25 05:12:55.13 spid4 Node:1
2003-02-25 05:12:55.14 spid4 RID: 2:1:15:0     CleanCnt:1 Mode: X Flags: 0x2
2003-02-25 05:12:55.14 spid4 Grant List 0::
2003-02-25 05:12:55.14 spid4 Owner:0x192e32e0 Mode: X  Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0
2003-02-25 05:12:55.15 spid4 SPID: 52 ECID: 0 Statement Type: DELETE Line #: 1
2003-02-25 05:12:55.15 spid4 Input Buf: Language Event: delete deadt2

2003-02-25 05:12:55.15 spid4 Requested By:
2003-02-25 05:12:55.15 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x19CA3500) Value:0x192e3340 Cost:(0/98)
2003-02-25 05:12:55.16 spid4
2003-02-25 05:12:55.16 spid4 Node:2
2003-02-25 05:12:55.16 spid4 RID: 2:1:28:0     CleanCnt:1 Mode: X Flags: 0x2
2003-02-25 05:12:55.17 spid4 Grant List 0::
2003-02-25 05:12:55.17 spid4 Owner:0x192e3400 Mode: X   Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0
2003-02-25 05:12:55.17 spid4 SPID: 51 ECID: 0 Statement Type: DELETE Line #: 1
2003-02-25 05:12:55.18 spid4 Input Buf: Language Event: delete deadt1

2003-02-25 05:12:55.18 spid4 Requested By:
2003-02-25 05:12:55.18 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:52 ECID:0 Ec:(0x19AB9508) Value:0x192e3300 Cost:(0/98)
2003-02-25 05:12:55.19 spid4 Victim Resource Owner:
2003-02-25 05:12:55.19 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:52 ECID:0 Ec:(0x19AB9508) Value:0x192e3300 Cost:(0/98)

 

블로킹 발생 시 원인 추적하기

다음에 소개하는 저장 프로시저들은 블로킹을 점검하는데 유용하게 사용할 수 있는 저장 프로시저들입니다. 다음의 저장 프로시저들은 master 데이터베이스에 생성해 두고 블로킹 발생 시에 활용하실 것을 권고합니다.

  • sp_blocker_pss80
    블로킹에 관한 전반적인 정보를 수집할 수 있는 매우 유용한 저장 프로시저입니다. Microsoft 웹사이트의 다음 아티클에 sp_blocker_pss80 저장 프로시저 생성 스크립트가 있으므로 활용하시기 바랍니다.
    http://support.microsoft.com/default.aspx?scid=kb;ko-kr;271509
    (아티클 제목 : How to monitor SQL Server 2000 blocking)
  • sp_leadblocker, sp_blockinglocks
    Inside SQL Server 책에 있는 스크립트로서, 블로킹 발생의 원인이 되는 프로세스에 대한 정보와, 블로킹에 관련되는 잠금에 대한 정보를 제공하는 저장 프로시저입니다.
[따라하기]
  1. 블로킹 추적에 유용한 저장 프로시저들을 생성합니다. (sp_blocker_pss80, sp_leadblocker, sp_blockinglocks)
    
    
  2. 시스템 SP 및 DBCC 명령어와 작업 단계 1에서 추가한 SP를 수행하여 그 결과를 분석합니다.
    2-1. sp_blocker_pss80 활용예
    2-4. 트랜잭션을 오픈한 채로 있는 프로세스가 블로킹을 유발하는 경우에는 DBCC OPENTRAN을 사용하여 특정 데이터베이스에서 가장 오래된 활성 트랜잭션에 대한 정보를 점검할 수 있습니다. 참고로, 트랜잭션에 트랜잭션 이름을 기술하면 문제가 있는 트랜잭션을 확인하는 작업이 용이해집니다. 

댓글목록

등록된 댓글이 없습니다.

Total 447건 1 페이지
게시물 검색

회원로그인

접속자집계

오늘
165
어제
365
최대
592
전체
40,139

그누보드5
Copyright © seobangnim.com All rights reserved.
자바스크립트를 활성화 하세요![ 브라우저에서 자바스크립트를 활성화하는 방법 ]