체크섬 I/O 유효성 검사
2005에서 데이터베이스 페이지에 대한 체크섬 및 읽기 재시도 논리가 추가됐다.
물리적 데이터문제로 이어질수 있는 I/O경로 문제를 감지하기 위함이며
다음 설명과 예제를 통해 페이지 유효성 검사에 대해 살펴보자.
ALTER DATBASE의 PAGE_VERIFY 설정을 통해 페이지 유효성 방법이 달라진다.
이 PAGE_VERIFY 는 {CHECKSUM | TORN_PAGE_DETECTION | NONE} 세가지이다.
CHECKSUM
페이지를 디스크에 쓸 때 전체 페이지의 내용에 대한 체크섬을 계산하고
이 값을 페이지 머리글에 저장합니다.
디스크에서 페이지를 읽으면 체크섬이 다시 계산되어 페이지 머리글에 저장된
체크섬 값과 비교됩니다.
두 값이 일치하지 않으면 체크섬 오류를 나타내는 824 오류 메시지가 SQL Server
오류 로그와 Windows 이벤트 로그에 보고됩니다.
체크섬 오류는 I/O 경로 문제를 나타냅니다.
오류에 대한 근본 원인을 확인하려면 하드웨어, 펌웨어 드라이버, BIOS,
필터 드라이버(예: 바이러스 소프트웨어) 및 기타 I/O 경로 구성 요소를 조사해야 합니다.
TORN_PAGE_DETECTION
페이지를 디스크에 쓸 때 8KB 데이터베이스 페이지의 각 512바이트 섹터에 대해 특정 비트를
데이터베이스 페이지 머리글에 저장합니다. 디스크에서 페이지를 읽을 때 페이지 머리글에
저장된 조각난 비트가 실제 페이지 섹터 정보와 비교됩니다.
값이 일치하지 않으면 페이지의 일부분만 디스크에 쓰여졌음을 의미합니다.
이 경우 조각난 페이지 오류를 나타내는 824 오류 메시지가 SQL Server 오류 로그와
Windows 이벤트 로그에 보고됩니다. 조각난 페이지가 실제로 쓰기가 완료되지
않은 페이지인 경우 조각난 페이지는 보통 데이터베이스 복구에서 검색됩니다.
그러나 다른 I/O 경로 오류도 언제든 조각난 페이지의 원인이 될 수 있습니다.
NONE
데이터베이스 페이지 쓰기에서 CHECKSUM 또는 TORN_PAGE_DETECTION 값이 생성되지 않습니다.
SQL Server 는 페이지 머리글에 CHECKSUM 또는 TORN_PAGE_DETECTION 값이 있는 경우에도 읽기
중 체크섬이나 조각난 페이지를 확인하지 않습니다.
/*************************************************************
페이지CHECKSUM
*************************************************************/
USE [master]
GO
/* 테스트DB생성*/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SQLERStudy')
DROP DATABASE [SQLERStudy]
GO
CREATE DATABASE SQLERStudy
ON PRIMARY
(
NAME = SQLERStudy_Data
,FILENAME = 'G:\DBFILE\MDF\SQLERStudy_Data.mdf'
,SIZE = 5mb)
LOG ON
(
NAME = SQLERStudy_Log
,FILENAME = 'G:\DBFILE\LDF\SQLERStudy_Log.ldf'
,SIZE = 5mb)
GO
USE SQLERStudy
GO
IF OBJECT_ID('TBL_Temp') IS NOT NULL
DROP TABLE TBL_Temp
GO
IF OBJECT_ID('TBL_Temp2') IS NOT NULL
DROP TABLE TBL_Temp2
GO
CREATE TABLE TBL_Temp(
Col1 INT IDENTITY
,COL2 CHAR(5000))
GO
CREATE TABLE TBL_Temp2(
Col1 INT IDENTITY
,COL2 CHAR(5000))
GO
/* PAGE_VERIFY 옵션{ CHECKSUM | TORN_PAGE_DETECTION | NONE }별테스트*/
ALTER DATABASE SQLERStudy
SET PAGE_VERIFY CHECKSUM /* TORN_PAGE_DETECTION */ /* NONE */
GO
INSERT INTO dbo.TBL_Temp VALUES('ABCDE')
INSERT INTO dbo.TBL_Temp VALUES('FGHIJ')
INSERT INTO dbo.TBL_Temp VALUES('KLMNO')
INSERT INTO dbo.TBL_Temp VALUES('PQRST')
INSERT INTO dbo.TBL_Temp VALUES('UVWXY')
INSERT INTO dbo.TBL_Temp2 VALUES('ABCDE')
INSERT INTO dbo.TBL_Temp2 VALUES('FGHIJ')
CHECKPOINT;
GO
/*
추적활성화및TBL_Temp 의데이터페이지확인
IndexID = 0:Datapage, 1:Clustered Index, 2:Nonclustered Index
PageType = 10: IAM, 1: Datapage, 2: Index page
*/
DBCC TRACEON(3604)
GO
DBCC IND('SQLERStudy','TBL_Temp',1)
GO
DBCC EXTENTINFO('SQLERStudy','TBL_Temp',1)
GO
/*
첫번째페이지(45)에대한페이지의추가정보(m_flagBits),
손상된페이지쓰기를감지하기위한정보(m_tornBits),
손상시킬정보확인
추가테스트: 824오류의경우실제오류난tornbits값으로변경하면유효성통과
정확한절차필요
*/
/* CASE) CHECKSUM m_flagBits = 0x8200 */
DBCC PAGE('SQLERStudy',1,73,1)
m_flagBits = 0x8200
m_tornBits = -1599833150
Data = 10009013 01000000 41424344 45202020 /* 해당페이지의데이터를변경하면조회시잘못된CHECKSUM값으로인식
CHECKSUM 오류감지를위해페이지손상 또한m_flagBits정보는힙,넌클인덱스,클러스터드에따라다른정보출력*/
SELECT CONVERT(INT,0xa0a4fbc2)
/* CASE) TORN_PAGE_DETECTION m_flagBits = 0x8000 */
DBCC PAGE('SQLERStudy',1,73,1)
m_flagBits = 0x8000
m_tornBits = 357826561
Data = 10009013 01000000 41424344 45202020
SELECT CONVERT(BINARY(4),357826561)
0x55555554 /* m_tornBits 값을변경해I/O경로오류발생(TORN_PAGE_DETECTION 은 디스크섹터에대한논리오류이므로) */
/* CASE) NONE m_flagBits = 0x8000 : 라이브시스템을테스트환경으로구축할때, 데이터신뢰도이슈가없는환경일경우*/
DBCC PAGE('SQLERStudy',1,73,1)
m_flagBits = 0x8000
m_tornBits = 0
Data = 10009013 01000000 41424344 45202020
/* SQL 중지후- HXD Editor로데이터일관성오류발생- SQL 재시작*/
/* 데이터손상시키기절차*/
1. simfile등에서무료헥사에디터HXD 다운및설치.
2. SQL 중지.
3. 헥사에디터를이용해MDF 파일Open후데이터손상.
3-1)헥사에디터를 이용해 MDF파일 오픈
3-2)손상시킬 데이터 검색
3-3) 데이터검색시 아래와 같은 그림
3-4)검색된 데이터를 00 00..으로 손상후 저장
4. SQL 재시작
USE SQLERStudy
GO
/* 손상된페이지정보확인. 오류감지전까진손상정보를저장하지않는다. */
SELECT DB_NAME(database_id),* FROM msdb..suspect_pages
GO
SELECT * FROM TBL_Temp2 /* 손상페이지가없으므로정상출력*/
GO
SELECT * FROM TBL_Temp WHERE Col1 = 5 /* 오류감지. 이시점에suspect_pages 테이블에오류정보가저장.*/
GO
/* 예상체크섬즉, 손상전의체크섬정보확인*/
예상체크섬: 0xa0a47bc2
SELECT CONVERT(INT,0xa0a47bc2)
go
CHECKSUM의m_tornBits = -1599833150 값과일치
아래오류메시지를확인해보면손상전의CHECKSUM:0xa0a47bc2 값이여야하지만실제는0x11aec03b.
※역으로실제CHECKSUM:0x11aec03b 를m_tornBits 값에저장하면정상출력한다.
이는손상된데이터를허용하는범위내에서이같은페이지치료를할수있는방법이다.
단손실된데이터를허용한다면...
/************************** 오류메시지*******************************/
/* CHECKSUM 오류메시지*/
메시지824, 수준24, 상태2, 줄1
SQL Server에서일관성기반의논리적인I/O 오류가검색되었습니다: 체크섬이잘못되었습니다.
(예상체크섬: 0xa0a47bc2. 실제체크섬: 0x11aec03b).. 파일'G:\DBFILE\MDF\SQLERStudy_Data.mdf'의오프셋0x00000000092000에서
데이터베이스ID 6에있는페이지(1:73)의읽기중이오류가발생했습니다.
/* TORN_PAGE_DETECTION 오류메시지*/
메시지824, 수준24, 상태2, 줄1
SQL Server에서일관성기반의논리적인I/O 오류가검색되었습니다:
페이지가손상되었습니다(예상서명: 0x0. 실제서명: 0x55555554).. 파일'G:\DBFILE\MDF\SQLERStudy_Data.mdf'의
오프셋0x00000000050000에서데이터베이스ID 6에있는페이지(1:40)의읽기중이오류가발생했습니다.
생략..
/****************************************************************
인덱스페이지손상시치료데이터는보장, 데이터페이지의경우손실
*****************************************************************/
테스트데이터입력은위와동일
CREATE CLUSTERED INDEX NIX_TBL_Temp_Col1 ON dbo.TBL_Temp(Col1)
GO
DBCC TRACEON(3604)
GO
DBCC IND('SQLERStudy','TBL_Temp',-1) /* -1: 모든IAM,Data,Index Page 출력
PageType = 10: IAM, 1: Datapage, 2: Index page */
GO
DBCC PAGE('SQLERStudy',1,121,1)
m_flagBits = 0x200
m_tornBits = -1949838031
06020000 00730000 000100
/* 데이터손상시키기 절차*/
1. simfile등에서무료헥사에디터HXD 다운및설치.
2. SQL 중지.
3. 헥사에디터를이용해MDF 파일Open후데이터손상.
4. SQL 재시작
/* 손상된페이지정보확인 */
SELECT DB_NAME(database_id),* FROM msdb..suspect_pages
GO
SELECT * FROM TBL_Temp WITH (INDEX = NIX_TBL_Temp_Col1)
GO
/* 오류페이지가데이터베이스ID 6에있는페이지(1:121) 를확인*/
/* 페이지정보확인으로PageType = 2 인경우인덱스페이지손상확인*/
/* 인덱스삭제시일관성오류*/
DROP INDEX TBL_Temp.NIX_TBL_Temp_Col1
GO
USE [master]
GO
/* 문제해결을위해읽기전용응급모드로변경*/
ALTER DATABASE SQLERStudy
SET EMERGENCY
GO
ALTER DATABASE SQLERStudy
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB('SQLERStudy', /* REPAIR_FAST , */ /* REPAIR_REBUILD ,*/ REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS
GO
/* 페이지치료및인덱스재작성*/
USE [master]
ALTER DATABASE SQLERStudy
SET ONLINE
GO
USE SQLERStudy
GO
SELECT * FROM TBL_Temp
GO
sp_helpindex TBL_Temp
DBCC IND('SQLERStudy','TBL_Temp',1) /* 인덱스페이지가재작성된것을확인*/
GO
외부스터디 발표자료
원본파일: http://cafe.naver.com/sqlmvp.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=467
통합검색
