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 instance 대해 추적 플래그 818을 시작 매개 변수 -T818로 지정하거나 다음 T-SQL 문을 실행하여 런타임에 사용하도록 설정할 수 있습니다.

DBCC TRACEON(818, -1)

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

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

다음 메시지는 SQL Server WriteFile API 또는 ReadFile API 호출의 명시적 오류를 표시하지 않습니다. 대신 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 instance 자동으로 데이터베이스를 닫고 열고 복구하려고 시도합니다. 오류 605 또는 823이 발생하는 첫 번째 페이지는 잘못된 페이지로 간주되며 페이지 ID는 SQL Server 실행하는 컴퓨터에서 유지됩니다. 복구 중(다시 실행 단계 전) 잘못된 페이지 ID를 읽을 때 페이지 헤더에 대한 기본 세부 정보가 SQL Server 오류 로그에 기록됩니다. 이 작업은 쓰기 손실 시나리오와 부실 읽기 시나리오를 구분하는 데 도움이 되므로 중요합니다.

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

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

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

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

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

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

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

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

오류: 3456, 심각도: 21, 상태: 1 로그 레코드(276666:1664:19), 트랜잭션 ID(0:825853240), 페이지(1:1787100), 데이터베이스 '작성자'(7)를 다시 실행하지 못했습니다. 페이지: LSN = (276658:4501:9), 입력 = 1. 로그: OpCode = 4, context 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

sort SQL Server 연산자는 일반적으로 데이터베이스에서 tempdb I/O 작업을 수행합니다. 이러한 I/O 작업은 버퍼 I/O 작업과 비슷합니다. 그러나 읽기 재시도 논리를 사용하여 유사한 문제를 resolve 위해 이미 설계되었습니다. 이 문서에 설명된 추가 진단 이러한 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 데이터베이스 엔진 입력/출력 요구 사항을 참조하세요.