MSSQL Suspect 모드 복구하기

팀 내부 프로젝트 도중 DB가 Suspect모드로 들어가는 현상을 보게 되었다.

인터넷에 검색을 해보니 드물게 나오는 상태라고 하는데 H/W 또는 O/S 상에서 실수로 인해 발생하는 현상이라고 한다.

내 경우는 트랜젝션 도중에 워크스테이션 전원이 나갔는데 그게 원인이 아닌가 싶다.

---

Suspect 모드의 발생 예시

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

결론적으로 SQL서버가 데이터에 접근이 되지 않는 경우 발생하는 모드라고 생각하면 되겠다.

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

 

중요한 점은, Device와 DB에 문제가 있는 것은 아니며, 복구를 위해서는 반드시 Device와 Database를 이용할 수 있는 상태여야 한다.

 

---

Suspect 모드 해제

해당 모드를 해제하기 위해서는 다음과 같은 명령을 실행해야 한다.

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 모드를 해제한다.

(위 프로시저는 SystemAdministrator만 실행할 수 있음을 주의한다.)

 

그후 DB를 Emergency모드로 전환한다.

Emergency 모드로 들어가면 DB는 Read_only 상태가 되며 오로지 Sysadmin Fixed Server Roles 만이 서버에 접근할 수 있다.

 

DBCC checkdb는 master DB에 대해 일관성 검증을 실행한다.

그후 DB에 존재하는 트랜잭션들을 롤백하고 DB를 Single User 모드로 전환한다.

다시 DBCC를 통하여 DB의 복구를 시도하며 최종적으로 다시 Multi User 모드로 전환하면 정상적으로 처리가 완료된 것이다.

 

만일 위의 단계를 거쳐도 Suspect 모드가 해제 되지 않는 경우에는 DB를 백업파일로부터 Restore 하는것이 가장 좋은 방법이 되겠다.

---

DBCC란?

그럼 DBCC란 무엇인가? 

쉽게 설명하면 MSSQL에서 사용하는 DB 콘솔 명령어이다.

콘솔 명령은 4가지의 범주로 분류 할 수 있는데

• 알림 : 다양한 정보를 수집하고 표시
• 유효성 검사 : DB,Table,Index,Catalog,File Gruop 또는 DB 페이지 할당에 대한 유효성 검사
• 유지관리 : DB, 인섹스 또는 파일 그룹에 대한 유지 관리
• 기타 : 추적 플래그 설정이나 메모리에서 DLL 제거 같은 기타 작업

와 같은 작업들을 수행할 수 있다.

이중에서 우리가 사용한 CheckDB는 유효성 검사 명령어로 모든 개체의 논리/물리적 무결성을 검사한다.

내부적으로 CHECKALLOC, CHECKTABLE, CHECKCATALOG 같은 개별 검사들을 한번에 실행해 주는 명령어라고 생각하면 되겠다.

 

앞서 설명했듯이 Suspect 모드란 DB에 트랜잭션 손상이 일어난 상태이므로 복구 작업 후 무결성 검사를 통하여 DB가 정상적인지 확인해 주는 작업이라고 생각하면 될것이다.

 

---

참고 자료

https://mozi.tistory.com/279

[MsSQL] DBCC문 알아보기

https://points.tistory.com/12

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