SQL Server 진단은 부실 읽기 또는 쓰기 손실로 인해 보고되지 않은 I/O 문제를 검색합니다.

이 문서에서는 SQL Server 진단이 부실 읽기 또는 쓰기 손실로 인해 발생하는 보고되지 않은 입력 또는 출력 문제를 검색하는 방법을 설명합니다.

원래 제품 버전: SQL Server
원래 KB 번호: 826433

증상

운영 체제, 드라이버 또는 하드웨어 문제로 인해 I/O 경로에서 쓰기 또는 부실 읽기 조건이 손실되는 경우 SQL Server에서 오류 605, 823, 3448 및 3456과 같은 데이터 무결성 관련 오류 메시지가 표시될 수 있습니다. 다음 예제와 유사한 오류 메시지가 표시될 수 있습니다.

2003-07-24 16:43:04.57 spid63 Getpage: bstat=0x9, sstat=0x800, cache
2003-07-24 16:43:04.57 spid63 pageno is/should be: objid is/should be:
2003-07-24 16:43:04.57 spid63 (1:7040966)/(1:7040966) 2093354622/2039782424
2003-07-24 16:43:04.57 spid63 ... IAM indicates that page is allocated to this object
2003-07-24 16:52:37.67 spid63 Error: 605, Severity: 21, State: 1
2003-07-24 16:52:37.67 spid63 Attempt to fetch logical page (1:7040966) in database 'pubs' belongs to object 'authors', not to object 'titles'..
2003-07-24 16:52:40.99 spid63 Error: 3448, Severity: 21, State: 1
2003-07-24 16:52:40.99 spid63 Could not undo log record (63361:16876:181), for transaction ID (0:159696956), on page (1:7040977), database 'pubs' (database ID 12). Page information: LSN = (63192:958360:10), type = 2. Log information: OpCode = 2, context 1..
2003-07-09 14:31:35.92 spid66 Error: 823, Severity: 24, State: 2
2003-07-09 14:31:35.92 spid66 I/O error (bad page ID) detected during read at offset 0x00000016774000 in file 'h:\sql\MSSQL\data\tempdb.mdf'..
2010-02-06 15:57:24.14 spid17s Error: 3456, Severity: 21, State: 1.
2010-02-06 15:57:24.14 spid17s Could not redo log record (58997:5252:28), for transaction ID (0:109000187), on page (1:480946), database 'MyDatabase' (database ID 17). Page: LSN = (58997:5234:17), type = 3. Log: OpCode = 2, context 5, PrevPageLSN: (58997:5243:17). Restore from a backup of the database, or repair the database.

SQL Server의 새로운 I/O 진단 기능

SQL Server는 SQL Server 2000 서비스 팩 4부터 새로운 I/O 진단 기능을 도입했으며 이러한 진단은 그 이후로 제품의 일부였습니다. 이러한 기능은 외부 I/O 관련 문제를 감지하고 증상 섹션에 설명된 오류 메시지를 해결하는 데 도움이 되도록 설계되었습니다.

증상 섹션에 나열된 오류 메시지가 표시되고 실제 드라이브 오류와 같은 이벤트에 의해 설명되지 않는 경우 SQL Server, 운영 체제, 드라이버 및 하드웨어의 알려진 문제를 검토합니다. 진단은 다음 두 가지 조건에 대한 정보를 제공하려고 합니다.

• 쓰기 손실: WriteFile API에 대한 호출이 성공했지만 운영 체제, 드라이버 또는 캐싱 컨트롤러는 SQL Server에서 쓰기가 성공했음을 알리는 경우에도 데이터를 실제 미디어에 올바르게 플러시하지 않습니다.

• 부실 읽기: ReadFile API를 성공적으로 호출했지만 운영 체제, 드라이버 또는 캐싱 컨트롤러가 이전 버전의 데이터를 잘못 반환합니다.

이를 설명하기 위해 Microsoft는 WriteFile API 호출이 성공 상태를 반환하는 시나리오를 확인했지만, 동일한 데이터 블록의 즉시 성공적인 읽기는 하드웨어 읽기 캐시에 저장될 가능성이 있는 데이터를 포함하여 이전 데이터를 반환합니다. 경우에 따라 이 문제는 읽기 캐시 문제로 인해 발생합니다. 다른 경우에는 쓰기 데이터가 실제 디스크에 기록되지 않습니다.

진단을 사용하도록 설정하는 방법

SQL Server 2017 이상 버전에서는 이 진단 기능이 기본적으로 사용하도록 설정됩니다. SQL Server 2016 및 이전 버전에서는 추적 플래그 818을 사용해야만 이러한 진단을 사용하도록 설정할 수 있습니다. SQL Server 인스턴스에 대해 추적 플래그 818 을 시작 매개 변수인 -T818로 지정하거나 다음 T-SQL 문을 실행하여 런타임에 사용하도록 설정할 수 있습니다.

DBCC TRACEON(818, -1)

추적 플래그 818을 사용하면 정렬 및 작업 파일 I/O를 포함하지 않고 SQL Server를 실행하는 컴퓨터에서 수행한 마지막 2,048개의 성공적인 쓰기 작업을 추적하는 데 사용되는 메모리 내 링 버퍼를 사용할 수 있습니다. 605, 823 또는 3448과 같은 오류가 발생하면 들어오는 버퍼의 LSN(로그 시퀀스 번호) 값이 최근 쓰기 목록과 비교됩니다. 읽기 작업 중에 검색된 LSN이 쓰기 작업에 사용된 LSN보다 오래된 경우 SQL Server 오류 로그에 새 오류 메시지가 기록됩니다. 대부분의 SQL Server 쓰기 작업은 검사점 또는 지연 쓰기로 발생합니다(지연 쓰기는 비동기 I/O를 사용하는 백그라운드 작업). 링 버퍼의 구현은 가벼우며 시스템에 미치는 성능 효과는 무시할 수 있습니다.

오류 로그의 메시지에 대한 세부 정보

다음 메시지는 WriteFile API 또는 ReadFile API에서 SQL Server를 호출하는 명시적 오류를 표시하지 않습니다. 대신 LSN을 검토할 때 발생한 논리적 I/O 오류가 표시되고 예상 값이 올바르지 않습니다.

SQL Server 2005부터 표시되는 오류 메시지는 다음과 같습니다.

SQL Server에서 논리적 일관성 기반 I/O 오류: 읽기 부실을 발견했습니다. 파일<FILE NAME>의 오프셋 <PHYSICAL OFFSET> 에 있는 데이터베이스 ID <DBID> 의 페이지 <PAGEID> 중 <Read/Write> 발생합니다. SQL Server 오류 로그 또는 시스템 이벤트 로그의 추가 메시지는 자세한 정보를 제공할 수 있습니다. 이는 데이터베이스 무결성을 위협할 수 있는 심각한 오류 조건이므로 즉시 수정되어야 합니다. 전체 데이터베이스 일관성 검사(DBCC CHECKDB)를 완료합니다. 이 오류는 여러 요인으로 인해 발생할 수 있습니다. 자세한 내용은 SQL Server 온라인 설명서를 참조하세요.

오류 824에 대한 자세한 내용은 MSSQLSERVER_824 참조하세요.

이 오류를 보고하거나 이 오류를 보고할 때 읽기 캐시에 이전 버전의 페이지가 포함되어 있거나 데이터가 실제 디스크에 올바르게 기록되지 않았습니다. 두 경우 모두(쓰기 손실 또는 부실 읽기) SQL Server는 운영 체제, 드라이버 또는 하드웨어 계층에 대한 외부 문제를 보고합니다.

오류 605 또는 823이 있는 트랜잭션을 롤백하려고 할 때 오류 3448이 발생하면 SQL Server 인스턴스가 자동으로 데이터베이스를 닫고 데이터베이스를 열고 복구하려고 시도합니다. 오류 605 또는 823이 발생하는 첫 번째 페이지는 잘못된 페이지로 간주되며 페이지 ID는 SQL Server를 실행하는 컴퓨터에서 유지됩니다. 복구 중(다시 실행 단계 전) 잘못된 페이지 ID를 읽을 때 페이지 헤더에 대한 기본 세부 정보가 SQL Server 오류 로그에 기록됩니다. 이 작업은 쓰기 손실 시나리오와 부실 읽기 시나리오를 구분하는 데 도움이 되므로 중요합니다.

부실 읽기 및 쓰기 손실로 관찰된 동작

부실 읽기 시나리오에서는 다음과 같은 두 가지 일반적인 동작이 나타날 수 있습니다.

• 데이터베이스 파일을 닫은 다음 열면 복구 중에 정확하고 가장 최근에 작성된 데이터가 반환됩니다.

• 검사점을 실행하고 문을 실행 DBCC DROPCLEANBUFFERS 하여 메모리에서 모든 데이터베이스 페이지를 제거한 다음 데이터베이스에서 문을 실행 DBCC CHECKDB 하면 가장 최근에 작성된 데이터가 반환됩니다.

이전 단락에서 언급한 동작은 읽기 캐싱 문제를 나타내며 읽기 캐시를 사용하지 않도록 설정하여 자주 해결됩니다. 이전 단락에 설명된 작업은 일반적으로 캐시 무효화를 강제로 적용하며, 성공적인 읽기가 수행되면 실제 미디어가 올바르게 업데이트되었음을 알 수 있습니다. 다시 읽은 페이지가 캐싱 메커니즘을 강제로 플러시한 후에도 여전히 이전 버전의 데이터일 때 손실된 쓰기 동작이 발생합니다.

경우에 따라 문제가 하드웨어 캐시와 관련이 없을 수 있습니다. 필터 드라이버에 문제가 있을 수 있습니다. 이러한 경우 백업 유틸리티 및 바이러스 백신 소프트웨어를 비롯한 소프트웨어를 검토한 다음 필터 드라이버에 문제가 있는지 확인합니다.

다양한 부실 읽기 및 손실된 쓰기 시나리오에 대한 설명

Microsoft는 오류 605 또는 823에 대한 기준을 충족하지 않지만 동일한 부실 읽기 또는 쓰기 손실 작업으로 인해 발생하는 조건을 언급했습니다. 경우에 따라 페이지가 두 번 업데이트되지만 동일한 LSN 값으로 표시됩니다. 이 동작은 개체 ID와 페이지 ID가 올바르고(이미 개체에 할당된 페이지) 페이지가 변경되어 디스크로 플러시되는 경우에 발생할 수 있습니다. 다음 페이지 검색은 이전 이미지를 반환한 다음 두 번째 변경이 이루어집니다. SQL Server 트랜잭션 로그는 페이지가 동일한 LSN 값으로 두 번 업데이트되었음을 보여 줍니다. 이 작업은 트랜잭션 로그 시퀀스를 복원하려고 하거나 외래 키 오류 또는 누락된 데이터 항목과 같은 데이터 일관성 문제로 문제가 됩니다. 다음 오류 메시지는 이 조건의 한 가지 예를 보여 줍니다.

오류: 3456, 심각도: 21, 상태: 1 트랜잭션 ID(0:825853240), 페이지(1:1787100), 데이터베이스 '작성자'(7)의 로그 레코드(276666:1664:19)를 다시 실행하지 못했습니다. 페이지: LSN = (276658:4501:9), 유형 = 1. 로그: OpCode = 4, 컨텍스트 2, PrevPageLSN: (275565:3959:31).

일부 시나리오는 다음 목록에 더 자세히 설명되어 있습니다.

LSN SequenceAction
1   Checkpoint
2   Begin Transaction
3   Table created or truncated
4   Inserts (Pages allocated)
5   Newly allocated page written to disk by Lazy Writer
6   Select from table - Scans IAM chain, newly allocated page read back from disk (LRU | HASHED = 0x9 in getpage message), encounters Error 605 - Invalid Object ID
7   Rollback of transaction initiated

LSN SequenceAction
1   Checkpoint
2   Begin Transaction
3   Page Modification
4   Page written to disk by Lazy Writer
5   Page read in for another modification (stale image returned)
6   Page Modified for a second time but because of stale image does not see first modification 
7   Rollback - Fails - Transaction Log shows two different log records with the same PREV LSN for the page

SQL Server sort 연산자는 일반적으로 데이터베이스에서 tempdb I/O 작업을 수행합니다. 이러한 I/O 작업은 버퍼 I/O 작업과 비슷합니다. 그러나 이미 읽기 재시도 논리를 사용하여 유사한 문제를 해결하도록 설계되었습니다. 이 문서에 설명된 추가 진단은 이러한 I/O 작업에 적용되지 않습니다.

Microsoft는 다음과 같은 정렬 읽기 실패의 근본 원인은 일반적으로 읽기 부실 또는 쓰기 손실이라고 지적했습니다.

2003-04-01 20:13:31.38 spid122 SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447 Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
2003-03-29 09:51:41.12 spid57 Sort read failure (bad page ID). pageid = (0x1:0x13e9), dbid = 2, file = e:\program files\Microsoft SQL Server\mssql\data\tempdb.mdf. Retrying.
2003-03-29 09:51:41.13 spid57 Error: 823, Severity: 24, State: 7
2003-03-29 09:51:41.13 spid57 I/O error (bad page ID) detected during read at offset 0x000000027d2000 in file 'e:\program files\Microsoft SQL Server\mssql\data\tempdb.mdf'..
* 00931097 Module(sqlservr+00531097) (utassert_fail+000002E3)
* 005B1DA8 Module(sqlservr+001B1DA8) (RecBase::Resize+00000091)
* 00407EE7 Module(sqlservr+00007EE7) (RecBase::LocateColumn+00000012)
* 00852520 Module(sqlservr+00452520) (mergerow+000000A4)
* 008522B3 Module(sqlservr+004522B3) (merge_getnext+00000285)
* 0085207D Module(sqlservr+0045207D) (mergenext+0000000D)
* 004FC5FB Module(sqlservr+000FC5FB) (getsorted+00000021)

읽기 부실 또는 쓰기 손실로 인해 데이터 스토리지가 예상되지 않으므로 다양한 동작이 발생할 수 있습니다. 누락된 데이터로 표시될 수 있지만 누락된 데이터의 일반적인 영향 중 일부는 오류 644 또는 625와 같은 인덱스 손상으로 표시됩니다.

오류 644 심각도 수준 21 메시지 텍스트 인덱스 페이지 %S_PGID, 인덱스 ID %d, 데이터베이스 '%.*ls'에서 RID '%.*hs'에 대한 인덱스 항목을 찾을 수 없습니다.

오류 625 심각도 수준 21 메시지 텍스트는 slotid(%d)가 잘못되었기 때문에 RID로 페이지 %S_PGID 행을 검색할 수 없습니다.

일부 고객은 행 개수 작업을 수행한 후 누락된 행을 보고했습니다. 이 문제는 쓰기 손실로 인해 발생합니다. 아마도 이 페이지는 클러스터형 인덱스 페이지 체인에 연결되어야 했습니다. 쓰기가 실제로 손실된 경우 데이터도 손실됩니다.

Microsoft는 희귀하고 무거운 I/O 로드에서 일부 하드웨어 플랫폼이 부실 읽기를 반환할 수 있음을 확인했습니다. 확장 진단에서 쓰기 상태가 오래되거나 손실된 것으로 표시되면 하드웨어 공급업체에 문의하여 SQLIOSim 유틸리티를 사용하여 즉시 후속 작업 및 테스트를 수행하세요.

SQL Server에는 SQL Server I/O 안정성 프로그램 요구 사항에 설명된 대로 안정적인 미디어에 대한 보장된 배달을 지원하는 시스템이 필요합니다. SQL Server 데이터베이스 엔진의 입력 및 출력 요구 사항에 대한 자세한 내용은 Microsoft SQL Server 데이터베이스 엔진 입력/출력 요구 사항을 참조하세요.