2007년 10월 15일 월요일

SQL DBA 가이드 (문제 점검 및 해결)

사용자 데이터베이스가 suspect로 표시된 경우에 문제 해결하기

Northwind 데이터베이스의 status 컬럼이 suspect로 설정된 경우를 예를 들어 설명합니다.

[주의] sp_resetstatus SP는 아래와 같은 문제 해결을 위해서만 사용해야 하며, 사용 시 주의를 요합니다.

[참고] SQL Server Errorlog 파일에 "Bypassing recovery for database 'Northwind' because it is marked SUSPECT." 와 같은 메시지가 기록됩니다.

[따라하기]

1. sp_resetstatus가 없으면 생성합니다.
USE master
GO
EXEC sp_configure 'allow updates', 1 
RECONFIGURE WITH OVERRIDE 
GO 
CREATE PROCEDURE sp_resetstatus @dbname varchar(30) 
AS
DECLARE @msg varchar(80)
IF @@trancount > 0
BEGIN
    PRINT 'Can''t run sp_resetstatus from within a transaction.'
    RETURN (1)
END
IF suser_id() != 1
BEGIN
    SELECT @msg = 'You must be the System Administrator (SA)'
    SELECT @msg = @msg + ' to execute this procedure.'
    RETURN (1)
END
IF (SELECT COUNT(*) FROM master..sysdatabases WHERE name = @dbname) != 1
BEGIN
    SELECT @msg = 'Database ' + @dbname + ' does not exist!'
    PRINT @msg
    RETURN (1)
END
IF (SELECT COUNT(*) FROM master..sysdatabases 
WHERE name = @dbname AND status & 256 = 256) != 1
BEGIN
    PRINT 'sp_resetstatus can only be run on suspect databases.'
    RETURN (1)
END
BEGIN TRAN
    UPDATE master..sysdatabases SET status = status ^ 256
    WHERE name = @dbname
    IF @@error != 0 OR @@rowcount != 1
        ROLLBACK TRAN
    ELSE 
    BEGIN
        COMMIT TRAN
        SELECT @msg = 'Database ' + @dbname + ' status reset!'
        PRINT @msg
        PRINT ''
        PRINT 'WARNING: You must reboot SQL Server prior to  '
        PRINT '         accessing this database!'
        PRINT ''
    END
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE 
GO
2. Suspect 상태가 된 데이터베이스에 대하여 sp_resetstatus를 실행합니다.
EXEC sp_resetstatus Northwind
GO

3. ALTER DATABASE를 사용하여 Northwind 데이터베이스에 파일을 추가하여 여유 공간을 확보해 줍니다.

4. SQL Server를 중지하고 다시 시작합니다.

 

Tempdb가 suspect 상태가 된 경우의 문제 해결하기

Tempdb가 suspect 상태가 된 경우에 문제를 해결하는 방법을 설명합니다. 참고로 tempdb가 suspect 상태가 되면 SQL Server 서비스 시작이 실패할 수도 있습니다.

[참고] SQL Server Errorlog 파일에 "Database 'tempdb' cannot be opened. It has been marked SUSPECT by recovery." 와 같은 메시지가 기록됩니다.

[따라하기]

1. tempdb.mdf 파일과 tempdb.ldf 파일이 있는지 확인하고, 만약 있으면 파일들의 이름을 변경합니다.

2. 다음과 같은 명령어를 사용하여 명령 프롬프트 상에서 SQL Server를 시작합니다. 명명된 인스턴스인 경우에는 -s 매개변수를 지정합니다.

sqlservr -c -f -T3608 -T4022

[주의] 명령 프롬프트 창이 열린 채로 두어야 합니다. 명령 프롬프트 창을 닫으면 SQL Server 프로세스가 중지됩니다.

3. 쿼리 분석기에서 sp_resetstatus를 수행하여 tempdb의 suspect 상태를 해제합니다.

EXEC master..sp_resetstatus Tempdb

4. 명령 프롬프트 찾에서 키를 눌러 SQL Server 서비스를 중지합니다.

5. SQL Server 서비스를 시작합니다. 이렇게 작업하면 Tempdb 데이터베이스 파일들이 새로 생성되며 tempdb가 정상적으로 복구됩니다.

 

손상된 데이터베이스 복구하기 (DBCC CHECKDB를 사용하여 오류 복구하기)

DBCC CHECKDB 명령어를 사용하면 특정 데이터베이스의 일관성(consistency)를 점검할 수 있습니다. DBCC CHECKDB 명령어는 데이터베이스 손상을 점검하는 주요 수단이며, 다음과 같은 사항들을 점검합니다.

- 인덱스 페이지와 데이터 페이지들이 제대로 연결되어 있는가
- 인덱스가 최신 상태이고, 제대로 정렬되어 있는가
- 포인트들이 일관성이 있는가 (Consistent)
- 각 페이지 상의 데이터가 최신 상태인가
- 페이지 오프셋이 최신 상태인가

[구문]
DBCC CHECKDB
     ( 'database_name'
             [ , NOINDEX
                 | { REPAIR_ALLOW_DATA_LOSS
                     | REPAIR_FAST
                     | REPAIR_REBUILD
                    } ] 
    )     [ WITH { [ ALL_ERRORMSGS ]
                    [ , [ NO_INFOMSGS ] ]
                     [ , [ TABLOCK ] ]
                     [ , [ ESTIMATEONLY ] ] 
                    [ , [ PHYSICAL_ONLY ] ] 
                    } 
        ] 

[사전지식]
DBCC CHECKDB나 DBCC CHECKTABLE에 REPAIR 옵션을 지정하여 수행하고자 하는 경우에는 SQL Server를 단일 사용자 모드로 시작해서는 안되고 해당 데이터베이스를 단일 사용자 모드(single user mode)로 설정해야 합니다
.

[참고] 데이터베이스를 단일 사용자 모드로 설정하는 방법 세 가지
  • 엔터프라이즈 관리자에서 설정하기
    1. 해당 데이터베이스에서 마우스의 오른쪽 버튼을 클릭한 다음에 [속성]을 선택합니다.
    2. 속성 창에서 [옵션] 탭을 선택합니다.
    3. "액세스 제한" checkbox를 선택한 다음에 "단일 사용자"를 선택하고 [확인] 버튼을 클릭합니다.
  • 쿼리 분석기에서 sp_dboption을 사용하여 설정하기
    USE master
    GO
    EXEC sp_dboption db_name, single, true
    GO
    
  • 쿼리 분석기에서 ALTER DATABASE를 사용하여 설정하기 (작업단계)
    1. 지정한 시간이 경과한 후에 완료되지 않은 트랜잭션들을 롤백하고 단일 사용자 모드로 변경하고자 하는 경우
    2. 완료되지 않은 트랜잭션들을 즉시 롤백하고 단일 사용자 모드로 변경하고자 하는 경우
[참고] 복구 옵션에 대하여 이해하기
  • REPAIR_FAST 옵션 : 사소한 손상을 복구하는 작업을 수행하며 수행 시간되 빠르고 데이터 유실도 유발하지 않습니다.
  • REPAIR_REBUILD 옵션 : comprehensive error checking and correction 을 수행하며, 소요 시간이 길고 데이터 유실도 발생하지 않습니다.
  • REPAIR_ALLOW_DATA_LOSS 옵션 : REPAIR_REBUILD가 수행하는 모든 작업들을 동일하게 수행하며, 데이터 유실이 발생할 수 있는 작업을 추가로 수행합니다. 구조적인 문제와 페이지 오류를 정정하고 손상된 텍스트 오브젝트를 삭제하는 작업을 수행하기 때문에 데이터 유실이 발생할 수도 있습니다.

[참고] 복구 작업 단계

  1. 해당 데이터베이스를 단일 사용자 모드로 변경합니다.
  2. REPAIR 옵션을 지정하여 DBCC CHECKDB를 수행합니다.
    2-1. 먼저 REPAIR_FAST 나 REPAIR_REBUILD 옵션을 지정하여 문제 해결을 시도합니다.
    2-2. REPAIR_FAST 나 REPAIR_REBUILD 옵션으로 문제가 해결되지 않으면 REPAIR_ALLOW_DATA_LOSS 옵션을 사용합니다.
    [주의] REPAIR_ALLOW_DATA_LOSS 옵션을 사용하면 데이터의 유실이 발생할 수 있다는 점을 명심하기 바랍니다.
    [권고사항] REPAIR_ALLOW_DATA_LOSS 옵션을 사용하는 경우에는 명령어 수행 후에 다시 원래 상태로 복구할 수 있도록 하기 위해서 트랜잭션 내부에서 DBCC 명령어를 수행할 것을 권고합니다. 이와 같이 작업하면 복구 작업을 수행하고 결과를 확인한 다음에 필요한 경우에 롤백이 가능해집니다.
  3. 복구가 완료되면 데이터베이스를 백업합니다.
[따라하기]
SELECT DATABASEPROPERTYEX ('Northwind', 'UserAccess')
GO
/* 결과:
MULTI_USER
*/

ALTER DATABASE Northwind 
SET SINGLE_USER 
WITH ROLLBACK AFTER 10 --10초 후에 완료되지 않은 트랜잭션들을 롤백
GO

SELECT DATABASEPROPERTYEX ('Northwind', 'UserAccess')
GO
/* 결과:
SINGLE_USER
*/

DBCC CHECKDB ('Northwind', REPAIR_FAST)
GO

ALTER DATABASE Northwind 
SET MULTI_USER 
GO

 

손상된 테이블 복구하기 (DBCC CHECKTABLE을 사용하여 오류 복구하기)

개별 테이블의 문제를 복구하고자 하는 경우에는 DBCC CHECKTABLE 명령어를 사용하면 됩니다.

[구문]
DBCC CHECKTABLE
( 'table_name' | 'view_name'
      	   [ , NOINDEX
   | index_id
   | { REPAIR_ALLOW_DATA_LOSS
                | REPAIR_FAST
         | REPAIR_REBUILD }
           ] 
    	)     [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
                    [ , [ TABLOCK ] ]
                    [ , [ ESTIMATEONLY ] ] 
                    [ , [ PHYSICAL_ONLY ] ] } 
       	       ] 
[따라하기]
SELECT DATABASEPROPERTYEX ('Northwind', 'UserAccess')
GO
/* 결과:
MULTI_USER
*/

ALTER DATABASE Northwind 
SET SINGLE_USER 
-- 10초 후에 완료되지 않은 트랜잭션들을 롤백
WITH ROLLBACK AFTER 10 
GO

SELECT DATABASEPROPERTYEX ('Northwind', 'UserAccess')
GO
/* 결과:
SINGLE_USER
*/

USE Northwind
GO
DBCC CHECKTABLE (Orders, REPAIR_FAST)
GO

ALTER DATABASE Northwind 
SET MULTI_USER 
GO

-- EXEC sp_dboption 'Northwind', 'single user', 'FALSE'
-- GO

 

단일 로그 파일로 구성된 데이터베이스의 새로운 로그 파일 생성하기

[주의] 로그 파일이 오직 하나인 데이터베이스에 대해서만 사용할 수 있습니다.

[따라하기] 단일 로그 파일을 가지는 'TestDB'의 로그 파일이 유실/손상된 경우에 새로운 로그 파일을 생성하는 방법

EXEC sp_detach_db 'TestDB'
GO
EXEC sp_attach_single_file_db 'TestDB', 'E:\DBdata\TestDB_dat.mdf'
GO

 

DBCC REBUILD_LOG를 사용하여 새로운 로그 파일 생성하기

DBCC REBUILD_LOG는, 데이터베이스의 트랜잭션 로그 파일을 사용할 수 없는 경우에 새로운 로그를 재구축하는데 사용되는 명령어입니다.
예를 들어, 하드웨어의 장애로 인하여 로그 파일이 손상되거나 실수로 로그 파일을 삭제하여 기존의 로그 파일을 액세스할 수 없어서 데이터베이스를 사용할 수 없는 경우에 DBCC REBUILD_LOG를 사용하여 로그를 재구축할 수 있습니다. 로그 파일이 하나인 경우에는 먼저 위의 해결 방법을 적용해 본 다음에 실패하면 이 방법을 사용하기 바랍니다.
그러나, 이 명령어를 사용하면 로그에 반영되지 않은 유실된 트랜잭션들의 발생으로 데이터베이스의 일관성이 손상될 가능성이 매우 높다는 점을 유의해야 합니다. 문제가 발생하면 일단 다른 방법 (예를 들어, sp_attach_single_file_db)을 동원하여 문제 해결을 시도하고, 도저히 다른 방법으로는 데이터베이스를 복구할 수 없는 경우에 이 명령어를 사용하기 바랍니다. 이 명령어를 수행하면 로그에 반영되지 않는 트랜잭션의 발생으로 인하여 데이터 무결성이 손상될 가능성이 높기 때문입니다. 이 명령어는 문서로 제공되지 않는 명령어이며 마이크로소프트 고객 지원 서비스의 지원 하에서 사용하는 것이 원칙이지만, 고객 지원 서비스를 받을 수 없는 경우의 응급 복구 작업에 참고하시라고 알려 드립니다. 또한, 하드웨어 장애와 같은 문제가 발생한 경우에는 트랜잭션 로그 파일 뿐만 아니라 데이터까지 손상시켰을 가능성이 높으므로, DBCC REBUILD_LOG가 성공적으로 완료된 이후에 단일 사용자 모드에서 DBCC CHECKDB를 수행하여 데이터의 일관성 (Consistency) 을 확인하는 작업이 반드시 필요합니다.

[구문] DBCC REBUILD_LOG('db_name','log_filename')

db_name : 문제가 발생한 데이터베이스의 이름
log_filename : 새로운 로그 파일에 대한 완전한 물리적 경로

[주의] 이 방법을 사용하면 데이터 일관성이 손상될 가능성이 매우 높으므로 다른 방법으로는 도저히 데이터베이스를 복구할 수 없는 경우에 최후의 방법으로서 사용해야 하며, 매우 신중하게 작업해야 하며, 이 명령어는 문서로 제공되지 않는 명령어로서 마이크로소프트 제품 고객 지원 서비스의 지원 하에서 사용해야 합니다.

[오류 발생]
로그 파일이 유실 또는 손상된 경우에는 그 데이터베이스는 엔터프라이즈 관리자에 "주의 대상" (suspect)로 표시되며, 쿼리 분석기나 엔터프라이즈 관리자에서 주의
대상 상태가 된 데이터베이스를 액세스하려고 하면 다음과 같은 오류가 발생합니다

서버: 메시지 945, 수준 14, 상태 2, 줄 1
파일을 액세스할 수 없거나 메모리 또는 디스크 공간이 부족하여 'RebuildLogTest' 데이터베이스를 열 수 없습니다. 자세한 내용은 SQL Server 오류 로그를 참조하십시오.

그리고 SQL Server를 재시작하면 ERRORLOG 파일에 다음과 같은 오류 메시지가 기록됩니다.

2005-01-12 14:51:56.72 spid11 'RebuildLogTest' 데이터베이스를 시작하는 중입니다.
2005-01-12 14:51:57.24 spid11 장치 활성화 오류입니다. 물리적 파일 이름 'C:\Program Files\Microsoft SQL Server\MSSQL\data\RebuildLogTest_log.LDF'이(가) 잘못된 것 같습니다.

이와 같은 오류가 발생하고 데이터베이스에 연결할 수 없는 경우에 다음과 같은 작업 단계로 복구 작업을 수행하면 로그를 재구축할 수 있습니다.

[예제] 다음은 RebuildLogTest라는 데이터베이스를 복구하는 예제입니다.

  1. 설정된 옵션들을 확인합니다.
    EXEC sp_dboption RebuildLogTest
    GO
    
  2. 시스템 테이블에 대한 직접적인 업데이트가 가능하도록 변경합니다.
    EXEC sp_configure 'allow updates', 1
    RECONFIGURE WITH OVERRIDE
    GO
    
  3. 문제가 발생한 데이터베이스를 응급 모드(bypass recovery)로 설정합니다
    UPDATE master..sysdatabases SET status = 32768 
    WHERE name = 'RebuildLogTest'
    GO
    
  4. SQL Server 서비스를 중지하고 다시 시작합니다.
  5. DBCC REBUILD_LOG를 수행합니다. 이 때 로그 파일의 이름에는 로그 파일이 저장될 경로까지 전체 이름을 기술해야 하며, 로그 파일은 기존에 존재하지 않는 이름을 지정해야 합니다.
    USE master
    GO
    DBCC REBUILD_LOG('rebuildlogtest','C:\Program Files\Microsoft SQL Server\MSSQL\data\RebuildLogTest_log.LDF')
    GO

    [참고]
    이 명령어가 정상적으로 수행되면 결과창에 다음과 같은 메시지가 반환되며, 데이터베이스는 'dbo use only' 모드가 됩니다. 이전의 status 값과 무관하게 sysdatabases 테이블의 status 값이 2048로 설정됩니다. sp_dboption이나 엔터프라이즈 관리자를 사용하여 status 값을 원하는 값으로 변경하면 됩니다.

    경고: 'RebuildLogTest' 데이터베이스에 대한 로그가 다시 작성되었습니다. 트랜잭션에 일관성이 없습니다. 물리적 일관성을 검사하려면 DBCC CHECKDB를 실행해야 합니다. 데이터베이스 옵션을 원래대로 설정하고 다른 로그 파일을 삭제해야 합니다.

    DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

    만약 이미 있는 파일 이름을 지정한 경우에는 다음과 같은 오류 메시지가 반환됩니다.

    서버: 메시지 5025, 수준 16, 상태 1, 줄 2
    'C:\Program Files\Microsoft SQL Server\MSSQL\data\RebuildLogTest_log.LDF' 파일이 이미 있습니다. 새 로그 파일을 만들려면 이 파일의 이름을 바꾸거나 삭제해야 합니다.

    DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

  6. 시스템 테이블을 직접 업데이트할 수 없도록 원래 값으로 변경합니다.
    EXEC sp_configure 'allow updates', 0
    RECONFIGURE WITH OVERRIDE
    GO
    
  7. 데이터베이스를 단일 사용자 모드로 변경하고 DBCC CHECKDB를 수행하여 일관성을 점검합니다. 데이터베이스를 단일 사용자 모드로 변경하는 보다 자세한 방법은 "손상된 데이터베이스 복구하기" 를 참조하기 바랍니다.
    EXEC sp_dboption ' RebuildLogTest ', 'single user', 'true'
    DBCC CHECKDB('RebuildLogTest')
    GO
    
  8. DBCC CHECKDB를 수행한 결과 문제가 없으면, 그 데이터베이스를 정상적으로 사용할 수 있습니다. 그러나 롤백되어야 할 트랜잭션이 롤백되지 않거나, 데이터에 반영되어야 할 수정 작업이 반영되지 않는 문제가 발생할 수 있으므로, 논리적인 데이터의 무결성은 별도의 점검이 필요합니다.
  9. 데이터베이스 옵션을 원래대로 설정하고, 로그 파일의 크기도 원래 크기로 확장합니다. 로그파일을 재구축하면, 504KB의 작은 크기의 로그 파일이 생성됩니다.

교착상태(Deadlock) 발생 시 교착상태 추적하기

추적 플래그 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에 추적결과가 기록됩니다.)
      sqlservr -c -T1204  -T3605
      
    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)
      USE master
      GO
      CREATE PROCEDURE sp_leadblocker  AS  
      IF EXISTS  
          (SELECT * FROM master.dbo.sysprocesses  
          WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))  
          SELECT   
              spid, status, loginame=SUBSTRING(SUSER_SNAME(sid), 1, 12),  
              hostname=substring(hostname, 1, 12),  
                  blk=CONVERT(char(3), blocked),  
              dbname=substring(db_name(dbid),1,10),cmd, waittype  
          FROM master.dbo.sysprocesses  
          WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)  
          AND blocked=0  
      ELSE  
      SELECT 'No blocking processes found!'
      GO
      CREATE PROCEDURE sp_blockinglocks AS
      SET NOCOUNT ON  
      SELECT  DISTINCT CONVERT (SMALLINT, L1.req_spid) AS SPID,   
        L1.rsc_dbid AS DBID,   
        L1.rsc_objid AS OBJID,  
        L1.rsc_indid AS INDID,  
        SUBSTRING (V.name, 1, 4) AS TYPE,  
        SUBSTRING (L1.rsc_text, 1, 16) AS RESOURCE,  
        SUBSTRING (U.name, 1, 8) AS MODE,  
        SUBSTRING (X.name, 1, 5) AS STATUS  
      FROM  master.dbo.syslockinfo L1,  
          master.dbo.syslockinfo L2,  
        master.dbo.spt_values V,  
        master.dbo.spt_values X,  
        master.dbo.spt_values U  
      WHERE L1.rsc_type = V.number  
         AND V.type = 'LR'  
         AND L1.req_status = X.number  
         AND X.type = 'LS'  
         AND L1.req_mode + 1 = U.number  
         AND U.type = 'L'  
         AND L1.rsc_type <>2 /* 2 : DB LOCK */  
         AND L1.rsc_dbid = L2.rsc_dbid  
         AND L1.rsc_bin = L2.rsc_bin  
         AND L1.rsc_objid = L2.rsc_objid   
         AND L1.rsc_indid = L2.rsc_indid   
         AND L1.req_spid <> L2.req_spid  
         AND L1.req_status <> L2.req_status  
        --AND(L1.req_spid IN (SELECT BLOCKED FROM master..SYSPROCESSES)  
        -- OR L2.req_spid IN (SELECT BLOCKED FROM master..SYSPROCESSES))  
      ORDER BY SUBSTRING (L1.rsc_text, 1, 16), SUBSTRING (X.name, 1, 5)   
      RETURN (0) 
      GO
      
    2. 시스템 SP 및 DBCC 명령어와 작업 단계 1에서 추가한 SP를 수행하여 그 결과를 분석합니다.
      2-1. sp_blocker_pss80 활용예
      WHILE 1=1
      BEGIN
         		EXEC master.dbo.sp_blocker_pss80
         		-- Or for fast mode
         		-- EXEC master.dbo.sp_blocker_pss80 @fast=1
         		-- Or for latch mode
         		-- EXEC master.dbo.sp_blocker_pss80 @latch=1
         		WAITFOR DELAY '00:00:15'
      END
      GO
      
      2-2. sp_leadblocker, sp_blockinglocks 활용예
      EXEC sp_leadblocker
      EXEC sp_blockinglocks
      GO
      
      2-3. 블로킹을 유발하는 프로세스에 대하여 sp_lock, sp_who2, sp_who 등의 시스템 SP를 수행하면 잠금과 프로세스에 대한 보다 자세한 내용을 별도로 점검할 수 있습니다.
      	EXEC sp_who2 53
      	EXEC sp_lock 53
      	GO
      
      2-4. 트랜잭션을 오픈한 채로 있는 프로세스가 블로킹을 유발하는 경우에는 DBCC OPENTRAN을 사용하여 특정 데이터베이스에서 가장 오래된 활성 트랜잭션에 대한 정보를 점검할 수 있습니다. 참고로, 트랜잭션에 트랜잭션 이름을 기술하면 문제가 있는 트랜잭션을 확인하는 작업이 용이해집니다.
      	USE pubs
      	DBCC OPENTRAN 
      	GO
      	-- 또는
      	DBCC OPENTRAN ('pubs')
      	GO
      

    대기(wait) 점검하기

    [따라하기]
    1. wait 정보를 저장할 데이터베이스를 생성합니다.
      USE master
      GO
      CREATE DATABASE DBAdmin 
      ON (
      NAME = DBAdmin_dat, 
      FILENAME = 'D:\DBdata\DBAdmin_dat.mdf'
      SIZE = 500 MB, 
      MAXSIZE = 1 GB, 
      FILEGROWTH = 100 MB) 
      LOG ON (
      NAME = DBAdmin_log, 
      FILENAME = 'D:\ DBData\DBAdmin_log.ldf', 
      SIZE = 100 MB, 
      MAXSIZE = 500 MB, 
      FILEGROWTH = 100 MB) 
      GO
      
    2. wait 정보를 추적할 저장 프로시저를 생성합니다.
      USE DBAdmin
      GO
      CREATE PROCEDURE get_waitstats
      AS
      -- This stored procedure is provided "AS IS" with no warranties,
      -- and confers no rights.
      -- Use of included script samples are subject to the terms specified at
      -- http://www.microsoft.com/info/cpyright.htm
      -- this proc will create waitstats report listing wait types by percentage
      -- can be run when track_waitstats is executing
      SET NOCOUNT ON

      DECLARE @now datetime, @totalwait numeric(20,1)
      ,@endtime datetime,@begintime datetime
      ,@hr int, @min int, @sec int

      SELECT @now=max(now),@begintime=min(now),@endtime=max(now)
      FROM waitstats WHERE [wait type] = 'Total'

      -- subtract waitfor, sleep, and resource_queue from Total
      SELECT @totalwait = sum([wait time]) + 1
      FROM waitstats
      WHERE [wait type] not in
      ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '***total***')
      AND now = @now

      -- insert adjusted totals, rank by percentage descending
      DELETE waitstats WHERE [wait type] = '***total***' and now = @now
      INSERT INTO waitstats select '***total***',0,@totalwait,@totalwait,@now

      SELECT [wait type],[wait time]
      ,percentage=cast (100*[wait time]/@totalwait as numeric(20,1))
      FROM waitstats
      WHERE [wait type] not in
      ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')
      AND now = @now
      ORDER BY percentage DESC
      GO

      CREATE PROCEDURE track_waitstats (@num_samples int=10,@delaynum int=1,@delaytype nvarchar(10)='minutes')
      AS
      -- T. Davidson
      -- This stored procedure is provided "AS IS" with no warranties,
      -- and confers no rights.
      -- Use of included script samples are subject to the terms specified at
      -- http://www.microsoft.com/info/cpyright.htm
      -- @num_samples is the number of times to capture waitstats,
      -- default is 10 times. default delay interval is 1 minute
      -- delaynum is the delay interval.
      -- delaytype specifies whether the delay interval is minutes or seconds
      -- create waitstats table if it doesn't exist,
      -- otherwise truncate
      SET NOCOUNT ON
      IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'waitstats')
      CREATE TABLE waitstats ([wait type] varchar(80),
      requests numeric(20,1),
      [wait time] numeric (20,1),
      [signal wait time] numeric(20,1),
      now datetime default getdate())
      ELSE TRUNCATE TABLE waitstats

      DBCC SQLPERF (waitstats,clear) -- clear out waitstats
      DECLARE @i int,@delay varchar(8),@dt varchar(3)
      , @now datetime, @totalwait numeric(20,1)
      ,@endtime datetime,@begintime datetime
      ,@hr int, @min int, @sec int
      SELECT @i = 1
      SELECT @dt = CASE lower(@delaytype)
      WHEN 'minutes' THEN 'm'
      WHEN 'minute' THEN 'm'
      WHEN 'min' THEN 'm'
      WHEN 'mm' THEN 'm'
      WHEN 'mi' THEN 'm'
      WHEN 'm' THEN 'm'
      WHEN 'seconds' THEN 's'
      WHEN 'second' THEN 's'
      WHEN 'sec' THEN 's'
      WHEN 'ss' THEN 's'
      WHEN 's' THEN 's'
      ELSE @delaytype
      END
      IF @dt not in ('s','m')
      BEGIN
      PRINT 'please supply delay type e.g. seconds or minutes'
      RETURN
      END

      IF @dt = 's'
      BEGIN
      SELECT @sec = @delaynum % 60
      SELECT @min = cast((@delaynum / 60) as int)
      SELECT @hr = cast((@min / 60) as int)
      SELECT @min = @min % 60
      END
      IF @dt = 'm'
      BEGIN
      SELECT @sec = 0
      SELECT @min = @delaynum % 60
      SELECT @hr = cast((@delaynum / 60) as int)
      END
      SELECT @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
      + right('0'+convert(varchar(2),@min),2) + ':' +
      + right('0'+convert(varchar(2),@sec),2)
      IF @hr > 23 or @min > 59 or @sec > 59
      BEGIN
      SELECT 'hh:mm:ss delay time cannot > 23:59:59'
      SELECT 'delay interval and type: ' + convert (varchar(10),@delaynum)
      + ',' + @delaytype + ' converts to ' + @delay
      RETURN
      END
      WHILE (@i <= @num_samples)
      BEGIN
      INSERT INTO waitstats ([wait type], requests, [wait time],[signal wait time])
      EXEC ('DBCC SQLPERF(WAITSTATS)')
      SELECT @i = @i + 1
      WAITFOR DELAY @delay
      END
      --- create waitstats report
      EXEC get_waitstats
      GO

    3. 수집 간격과 반복 실행 횟수를 지정하여 대기 정보를 수집합니다.
      -- 2초 간격으로 10번 반복 수행 예제
      USE DBAdmin
      EXEC Track_waitstats @num_samples=10,@delaynum=2,@delaytype='seconds'
      GO
      SELECT * FROM waitstats
      GO
      -- 실행 예제 : 디폴트 ( 실행 소요 시간 : 10분 ) 
      USE DBAdmin
      EXEC Track_waitstats
      GO
      


  • 출처 : http://blog.naver.com/ngmaster/120038831061

    댓글 없음:

    댓글 쓰기