[MsSQL] DBCC WRITEPAGE를 활용한 Data Page 오류 만들기 및 테스트

Data Page Error

메시지 824, 수준 24, 상태 2, 줄 54 SQL Server에서 일관성 기반의 논리적인 I/O 오류가 검색되었습니다: 체크섬이 잘못되었습니다(예상: 0x3e7ed287, 실제: 0x3e7e5287).. 파일 'O:\Data\BAN_TEST.mdf'의 오프셋 0x00000000144000에서 데이터베이스 ID 8에 있는 페이지 (1:162)의 읽기 중 이 오류가 발생했습니다. 자세한 내용은 SQL Server 오류 로그 또는 시스템 이벤트 로그의 추가 메시지에서 확인할 수 있습니다. 이는 데이터베이스 무결성을 위협하는 심각한 오류 상태이며 즉시 수정해야 합니다. 전체 데이터베이스 일관성 검사(DBCC CHECKDB)를 완료하십시오. 이 오류는 다양한 요인으로 인해 발생할 수 있습니다. 자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.

MsSQL 데이터베이스를 운영 할 때 어쩌다보면, 이러한 메시지를 만날 날이 올 수 있다.

이 메시지 말고도... Page의 헤더가 잘못되었다, 혹은 오프셋이 잘못되었다 등 여러가지 Page 문제를 알리는 오류를 만날 수 있는데...

 

운이 없게도 만나고야 말았다.

이 오류를 만나게 된 자세한 내용은 다른 포스팅에서 이야기처럼 풀어보고 싶다. 나중에 추가하도록 하겠다.

 

정말 Data Page가 깨짐에 따라 너무나 많은 고생을 하게 되었으며...

해당 내용을 방지 혹은 조치하고자 테스트를 하기위해 DBCC WRITEPAGE를 활용하여 직접 Data Page의 데이터를 덮어씌워 오류를 발생시키는 내용을 다루고자 한다.

 

Page Error 만들기

Data Page의 오류를 만들기위해 새로운 테이블과 데이터를 입력한다.

-- 테스트 table
create table TB_CRASH (
  t_no int not null identity(1, 1),
  t_cts varchar(100)
)

-- 100 row insert
declare @i int = 1
while @i <= 100
begin
    insert into TB_CRASH (t_cts)
    values (concat('text', @i))
    set @i = @i + 1
end

select * from TB_CRASH

테스트할 테이블과 해당 테이블의 100건의 데이터를 간단하게 넣어보았다.

데이터의 크기를 보아하니 해당 테이블은 1개의 Data Page를 사용 하게 될 것이다.

 

Data Page 조회

TB_CRASH 테이블의 Data Page에 대한 정보를 조회해보자.

select allocated_page_file_id,  -- File ID
       allocated_page_page_id,  -- Page ID
       previous_page_file_id, -- 이전 File ID
       previous_page_page_id, -- 이전 Page ID
       next_page_file_id, -- 다음 File ID
       next_page_page_id, -- 다음 Page ID
       page_level,        -- Page LV ( 0 : leaf page)
       page_type_desc,     -- Page 형태
       extent_file_id,
       extent_page_id,
       page_type
  from SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS(db_id('BAN_TEST'), -- db id
                                           object_id('TB_CRASH'), -- table id
                                           null,  -- index id
                                           null,  -- partition id
                                           'detailed') -- limited : 제한적 정보 / detailed : 많은 정보

BAN_TEST라는 데이터베이스의 TB_CRASH 테이블의 페이지 정보를 조회해 보았다.

Data Page의 File_ID는 1 / Page_ID는 162 임을 확인 할 수 있다. (1:162)로 오류 메시지에 표시되었던 그 페이지이다.

(테스트 환경에 따라 페이지 ID가 다르다.)

 

해당 페이지의 데이터 slot을 더 자세히 한번 확인해보도록 하자.

-- DB Name, File ID, PAGE ID, show mode (0 : header, 1: row, 2: page, 3: column/row)
dbcc page ('BAN_TEST', 1, 162, 3)

파라미터에 위에서 조회하여 알게된 (1:162) 값을 넣어주었다.

 

이렇게 각 슬롯의 데이터에 대해서 조회 해보았다.

 

DBCC WRITEPAGE

DBCC WRITEPAGE를 활용하여, Data Page를 직접 수정해 오류를 만들어 보도록하자.

위의 명령어는 테스트 환경에서만 사용하는 것을 권장한다. 사실 운영중인 데이터베이스에서는 사용 할 일이 없다고 생각된다.

 

먼저 Page를 수정하기 앞서 Data Page의 체크섬 오류가 발생할 경우 저장되는 시스템 테이블에 대해 알아보자.

select * from msdb.dbo.SUSPECT_PAGES

SUSPECT_PAGES 테이블에는 Page오류가 발생한 내역이 저장된다.

해당 페이지에 대해 아직 감지하지 못 했을 경우 저장되지 않는다.

 

현재는 조회할 경우 결과 값이 없을 것이다.

 

자 그럼 Data Page를 강제로 수정해보도록 하자.

DBCC WRITEPAGE 명령어를 사용하기전에 해당 데이터베이스를 single user 모드로 변경해야 한다.

alter database BAN_TEST set single_user with rollback immediate

-- db name, file id, page id, offset, length, data, direct update (0/1)
dbcc writepage ('BAN_TEST', 1, 162, 0, 1, 0x00, 1)

alter database BAN_TEST set multi_user

DBCC WRITEPAGE에 사용되는 파라미터는 아래와 같다.

• DB Name : 변경 할 Page가 속한 Database
• File ID : 변경 할 Page가 속한 File ID
• Page ID : 변경 할 Page ID
• Offset : 변경할 데이터의 byte 위치
• Length : 변경할 데이터의 길이 (byte) 
• Data : 변경할 데이터 값 (16진수)
• Direct Update : 메모리 버퍼 풀을 통해 변경 (0) / 디스크에 직접 파일 수정 (1)

반응형

위 명령어를 통해 강제로 수정하였지만, 아직 Data Page의 손상에 대해 감지한 상태는 아니다.

select * from msdb.dbo.SUSPECT_PAGES

해당 테이블을 조회한다고해도 아직 결과 값이 없을 것이다.

 

Page Error 확인 및 테스트

Data Page의 데이터를 강제로 바꾸어 Page의 문제를 만들었는데, 어떻게하면 해당 내용을 확인 할 수 있을까?

또한, 어떻게하면 해당 내용가지고 무엇을 할 수 있을까?

 

먼저 Page Error가 나는 것을 알 수 있는 방법은 크게 아래와 같이 있다고 볼 수 있다.

 

1. 해당 Page에 속한 데이터를 조회한다. 

2. DBCC CHECKDB / DBCC CHECKTABLE 데이터 체크 명령어를 실행한다.

3. BACKUP 시 with CHECKSUM 명령어를 사용한다.

 

Page 데이터 조회

select문을 통해 해당 page에 속한 데이터를 where 절에 넣어 직접적으로 확인해도 되지만, 실제 운영에서는 어느 페이지의 어느 데이터가 깨질지 모르기에 Table Full Scan을 할 수 있도록 아래와 같이 조회하여 오류를 확인하였었다.

select count(*) from TB_CRASH

메시지 824, 수준 24, 상태 2, 줄 54 SQL Server에서 일관성 기반의 논리적인 I/O 오류가 검색되었습니다: 체크섬이 잘못되었습니다(예상: 0x3e7ed287, 실제: 0x3e7e5287).. 파일 'O:\Data\BAN_TEST.mdf'의 오프셋 0x00000000144000에서 데이터베이스 ID 8에 있는 페이지 (1:162)의 읽기 중 이 오류가 발생했습니다. 자세한 내용은 SQL Server 오류 로그 또는 시스템 이벤트 로그의 추가 메시지에서 확인할 수 있습니다. 이는 데이터베이스 무결성을 위협하는 심각한 오류 상태이며 즉시 수정해야 합니다. 전체 데이터베이스 일관성 검사(DBCC CHECKDB)를 완료하십시오. 이 오류는 다양한 요인으로 인해 발생할 수 있습니다. 자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.

위와 같은 메시지가 출력되며 데이터 조회가 안되는 것을 확인 할 수 있다.

 

그렇다면 SUSPECT_PAGES 테이블에는 어떠한 변화가 있을까?

새로운 데이터가 insert되어있다. Page오류가 확인 된 마지막 시점과 Page 위치 등이 기록된다.

 

테스트 중 문제가 발생하는 테이블이 무엇인지 알고 있었기에 빠른 확인을 위해 해당 테이블을 Full Scan 하는 Agent를 시간별로 동작하게 하여 빠른 확인을 하였다.

 

DBCC CHECK 사용

DBCC CHECK 관련 명령어를 사용하는 방법은 제일 확실한 방법이다.

 

DBCC CHECK는 관련 오브젝트의 확인 뿐만아니라, 오류 수정 등의 여러 옵션이 있다. 자세한 내용은 다른 포스팅에서 다루도록 하며, 먼저 간단히 체크하는 내용에 대해서만 다루도록한다.

 

해당 데이터베이스의 모든 내용을 check하는 명령어이다.

dbcc checkdb('BAN_TEST')

 

해당 데이터베이스의 모든 오브젝트를 조회하여 문제가 있는 page를 확인하였다.

오류 Page가 다시한번 확인되었으므로 SUSPECT_PAGES 테이블의 error count가 증가하였음을 확인 할 수 있다.

 

데이터베이스 단위가 아닌 하나의 테이블에 대해서만 check해볼 수도 있다.

use BAN_TEST
dbcc checktable('TB_CRASH')

 

파라미터로 입력한 테이블에 대해서만 오류검사를 실행한다.

 

이 테스트를 하기 앞서 문제가 발생했던 데이터는... 하나의 테이블만 20억이 넘는 대용량의 테이블이였다.

데이터베이스 자체도 너무나 크고, 문제가 발생하는 테이블이 무엇인지 알고 있어 시간 절약을 위해 해당 테이블만 확인해보곤 하였다.

그리고 당연하게도 error count도 증가하였음을 확인 할 수 있다.

 

BACKUP with CHECKSUM 옵션

MsSQL을 주기적으로 백업하여 복구하던 중 알게된 문제였는데, 복구된 데이터베이스도 Data Page에 문제가 있음을 알게 되었다.

해당 데이터베이스를 백업할대 checksum옵션이 없을 경우, 오류발생된 page까지 함께 백업/복구가 되는 것을 알게되었다.

이러한 경우 Log Shipping 으로 DR을 구성해도 Slave서버까지 함께 데이터가 깨지는 문제가 발생하여, 어떻게 하면, DR에 문제 없는 데이터만 복구가 가능한가에 대해 고민하게 되었다.

 

먼저 일반적으로 백업을 할 경우 문제없이 백업이 완료됨을 확인 할 수 있다.

backup database BAN_TEST to disk = 'M:\BAN_TEST.bak'

이렇게 문제가 발생한 백업파일이 생성되었다는 것은 앞으로 DR도 믿을 수 없는 큰 문제라고 생각한다.

 

간단하게도 backup 시 with checksum옵션을 사용하여 백업과 동시에 데이터 오류를 검사 할 수 있다.

backup database BAN_TEST to disk = 'M:\BAN_TEST_CS.bak' with checksum

 

이 또한, SUSPECT_PAGES 테이블에 error count가 증가함을 확인 할 수 있다.

 

COMMENT

관련 테스트를 하며, 여러가지 시행착오도 거치고 많은 것을 알게 되었는데... 사실 정리를 못하겠다.

 

유지관리계획을 통한 백업에서는 checksum 옵션을 적용해도 제대로 동작하지 않는 버그라던지...

무조건 데이터가 깨지는것이 아니라는 것, 하드웨어와도 밀접한 관계가 있다는 것...

 

해당 page가 아닌 다른 page는 잘 읽히기에... 아무런 정보가 없을 경우 너무나 늦게 알게 된다는 것 등

 

오류를 만나기전에 한번 쯤 다들 여러 상황에 대해서 테스트해보기를 바란다.