사용자 데이터베이스가 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. 명령 프롬프트 찾에서
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가 수행하는 모든 작업들을 동일하게 수행하며, 데이터 유실이 발생할 수 있는 작업을 추가로 수행합니다. 구조적인 문제와 페이지 오류를 정정하고 손상된 텍스트 오브젝트를 삭제하는 작업을 수행하기 때문에 데이터 유실이 발생할 수도 있습니다.
[참고] 복구 작업 단계
- 해당 데이터베이스를 단일 사용자 모드로 변경합니다.
- 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 명령어를 수행할 것을 권고합니다. 이와 같이 작업하면 복구 작업을 수행하고 결과를 확인한 다음에 필요한 경우에 롤백이 가능해집니다. - 복구가 완료되면 데이터베이스를 백업합니다.
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라는 데이터베이스를 복구하는 예제입니다.
- 설정된 옵션들을 확인합니다.
EXEC sp_dboption RebuildLogTest GO
- 시스템 테이블에 대한 직접적인 업데이트가 가능하도록 변경합니다.
EXEC sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE GO
- 문제가 발생한 데이터베이스를 응급 모드(bypass recovery)로 설정합니다
UPDATE master..sysdatabases SET status = 32768 WHERE name = 'RebuildLogTest' GO
- SQL Server 서비스를 중지하고 다시 시작합니다.
- 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에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
- 시스템 테이블을 직접 업데이트할 수 없도록 원래 값으로 변경합니다.
EXEC sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDE GO
- 데이터베이스를 단일 사용자 모드로 변경하고 DBCC CHECKDB를 수행하여 일관성을 점검합니다. 데이터베이스를 단일 사용자 모드로 변경하는 보다 자세한 방법은 "손상된 데이터베이스 복구하기" 를 참조하기 바랍니다.
EXEC sp_dboption ' RebuildLogTest ', 'single user', 'true' DBCC CHECKDB('RebuildLogTest') GO - DBCC CHECKDB를 수행한 결과 문제가 없으면, 그 데이터베이스를 정상적으로 사용할 수 있습니다. 그러나 롤백되어야 할 트랜잭션이 롤백되지 않거나, 데이터에 반영되어야 할 수정 작업이 반영되지 않는 문제가 발생할 수 있으므로, 논리적인 데이터의 무결성은 별도의 점검이 필요합니다.
- 데이터베이스 옵션을 원래대로 설정하고, 로그 파일의 크기도 원래 크기로 확장합니다. 로그파일을 재구축하면, 504KB의 작은 크기의 로그 파일이 생성됩니다.
교착상태(Deadlock) 발생 시 교착상태 추적하기
추적 플래그 1204를 사용하면 교착상태(Deadlock)에 대한 내용을 확인하는 것이 가능합니다. 명령 프롬프트에서 추적 플래그를 추가하여 SQL Server 서비스를 시작할 수도 있고, 엔터프라이즈 관리자에서 SQL Server 시작 매개 변수에서 추적 플래그를 추가할 수도 있습니다.
[따라하기]
- SQL Server 서비스를 중지해도 되는 시점에 SQL Server 서비스를 중지합니다.
- 다음과 같이 추적 플래그를 추가하고 SQL Server 서비스를 시작합니다.
[주의] SQL Server 서비스를 시작한 명령 프롬프트의 창은 그대로 두어야 합니다. 명령프롬프트 창을 닫거나,
[예] 추적 플래그를 추가하여 디폴트 인스턴스 SQL Server 서비스를 시작하는 예제 (SQL Server 2000 서비스 팩3부터는 -T3605를 추가하지 않아도 ERRORLOG에 추적결과가 기록됩니다.)sqlservr -c -T1204 -T3605
- 위와 같이 작업하면 교착상태 추적 결과가 SQL Server 서비스가 시작된 콘솔 화면과 ERRORLOG 파일로 기록됩니다.
- 엔터프라이즈 관리자에서 [속성] → [시작 매개 변수] "매개 변수"에 -T1204와 -T3605를 입력하고 [추가] 버튼을 클릭한 다음에 [확인] 버튼을 클릭합니다.
- SQL Server 서비스를 중지하고 재시작 합니다.
- 교착상태가 발생하면 교착상태 추적 결과가 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 책에 있는 스크립트로서, 블로킹 발생의 원인이 되는 프로세스에 대한 정보와, 블로킹에 관련되는 잠금에 대한 정보를 제공하는 저장 프로시저입니다.
- 블로킹 추적에 유용한 저장 프로시저들을 생성합니다. (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 - 시스템 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) 점검하기
[따라하기]- 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
- 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 - 수집 간격과 반복 실행 횟수를 지정하여 대기 정보를 수집합니다.
-- 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
