[DB] MSSQL Server 'Suspect' 모드에서 복구하기

MS SQL을 사용하다 보면 드물게 ‘Suspect’ 모드로 나타나는 경우 가 있습니다. (한글은 ‘주의대상’)

이는 H/W 또는 O/S상에서 실수로 인해 발생합니다. (예: Transaction log파일 삭제)

발생예시

• 해당 시스템이 지정된 파일을 찾을 수 없을 때 발생합니다.
• 데이터 또는 로그가 존재하는 장치를 열수 없을 때 발생합니다.
• SQL서버가 트렌젝션 중에 다운되거나 재시작되었을 때, 트렌젝션 로그가 손상되면 발생합니다.
• 안티바이러스 프로그램 등으로 인해 SQL서버가 특정 데이터나 로그파일에 접근할 수 없을 때 발생합니다.

문제가 발생하면 처음에는 SQL Server가 Device file에 배타적 잠금을 시도합니다. 만약 해당 파일이 다른 프로세스에 의해 사용 중이거나 파일이 없으면 SQL Server는 에러를 출력합니다.

하지만, Device와 Database에는 문제가 있는 것은 아니며, 복구를 위해서는 반드시 Device와 Database를 이용할 수 있는 상태여야 합니다.

해결

문제를 해겨하기 위해서는 다음과 같은 명령어를 실행합니다.

EXEC sp_resetstatus ‘DB이름’;
ALTER DATABASE DB이름 SET EMERGENCY
DBCC CHECKDB (‘DB이름’)
ALTER DATABASE DB이름 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (‘DB이름’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DB이름 SET MULTI_USER

위 명령어는 DB의 논리적, 물리적 일관성을 검증하고 복구하기 위한 일련의 작업을 실행합니다.

sp_resetstatus 저장프로시저는 Suspect를 해제합니다. sp_resetstatus는 System Administrator만 실행할 수 있습니다.

다음에는 DB를 EMERGENCY모드로 전환하며, 이 때부터 DB는 READ_ONLY가 되며 오로지 sysadmin fixed server roles만이 서버에 접근할 수 있게 됩니다.

DBCC checkdb(‘DB이름’) 명령을 통해 master 데이터베이스에 대해 일관성 검증을 실행합니다. 다음 단계는 현재 DB에 존재하는 트렌젝션들을 롤백하고 DB를 Single User 모드로 전환합니다. 다음으로 DBCC를 통해 DB의 복구를 시도하며 최종적으로 다시 Multi User 모드로 전환합니다.

만약 이 단계를 거쳤는데도 Suspect가 해제되지 않을 경우에는, 또 다른 문제가 있는 것입니다. 이 경우 실행 가능한 가장 좋은 대안은 DB를 백업으로부터 복구하는 것입니다.

기타 (DBCC CHECKDB 명령어)

위에서 DBCC CHECKDB 명령어는 DB의 일관성과 무결성을 점검하며, 오류로부터 데이터를 복구하는 명령입니다.

• 인덱스와 데이터 페이지가 정확하게 연결되어 있는지 점검합니다.
• 인덱스가 가장 최신의 것이며 정확히 정렬되어 있는지 점검합니다.
• 포인터의 무결성을 검증합니다.
• 각 페이지의 데이터가 최신의 것인지 점검합니다.
• 페이지 오프셋이 최신의 것이 맞는지 점검합니다.

옵션: 다음과 같은 3가지 옵션 중에서 선택할 수 있습니다.

1. REPAIR_FAST: 데이터의 손실이 없는 사소한 오류를 수정하며 시간이 걸리지 않는 작업을 합니다.
2. REPAIR_REBUILD: REPAIR_FAST가 하는 작업과 더하여 인덱스를 재생성하는 등 시간이 걸리지만 데이터는 손실되지 않는 작업을 수행합니다.
3. REPAIR_ALLOW_DATA_LOSS: REPAIR_REBUILD의 모든 작업과 더불어 할당오류, 구조적 행 오류, 페이지 오류, 손상된 텍스트 개체 삭지 및 수정을 위한 할당, 할당 취소 등의 작업이 이루어 집니다.