SQL Server 2000 / LDF 파일 이상으로 주의 대생(Suspect)상태.

SQL Server 2000 사용중 트랜젝션로그 파일의 이상 현상으로 DB 가 이상이 발생되었습니다. 

이상이 발생한 DB 의 이름이 duck 라고 전재하였을시, SQL 서비스는 재대로 실행이 되고 있으나

EM 이나, 타 웹에서 엑세스를 하지 못합니다. 

가장큰무넺는 EM 에서 해당 DB 를 확인하기 위해서 duck 데이터 베이스를 클릭했을경우 1초에 약 5Mbitye 씩 

메모리 할당량이 올라 갑니다.

이 이유는 BCP를 이용해서 duck 데이터베이스에 대량으로 업데이트시 해당 문의 완결되지 않고, 중지 된경우에 발생할 수 있음을 확인하였습니다.

BCP 를 이용해서 duck에 대량의 쿼리를 발생시키는 도중 디스크의 용량이 부족하여 해당 쿼리문이 실행되지 않음...

모든 이유가 트랜젝션의 이상으로 확인하고, 트랜젝션 로그 LDF 파일을 삭제(변경)후 SQL 서비스를 다시 시작하였습니다. 아니나 다를까 여지없이 에러.

(오프라인)또는(주의대상)이라는 메세시가 나타나게 됩니다.

그럼 해당 DB 를 복원해보도록 하겠습니다. 

DB 이름 : duck

1. 시스템 테이블을 변경하기 위한 옵션을 설정합니다.

use master

go

sp_configure 'allow update',1

Reconfigure with override

go

2. 해당 DB 를 응급복구모드로 변경합니다. 

=> 응급복구모드 (32768 = emergency mode)

update sysdatabases set status = 32768 where name = 'duck'

3. SQL 서비스를 중지후 기존의 트랜젝션파일 LDF 를 삭제/변경 한후 SQL 서비스를 재식후 로그 파일을

   다시 생성해 줍니다.

dbcc rebuild_log('duck','D:\Microsoft SQL\DATA\duck_log.ldf')

4. 이제 duck 데이터 베이스는 dbo 만 사용상태로 활성화 되었으니 다음 쿼리로 DB 상태가 

  2048 = dbo use only 상태인지 확인합니다.

select * from sysdatabase

Select 문을 실행하면 데이터 베이스의 상태가 쭉~나옵니다. 

duck 데이터 베이의 상태만 나타난 곳을 보면. 대략 다음과 같습니다.

name      dbid   sid                                             mode  status

duck         2      0x01210012030120391402350250....    0        2048

여기에서 제일 마지막 status 가 나타납니다.

5. 그럼 응급복구 모드로 변경한 duck 데이터베이스를 기본모드로 변경합니다.

update sysdatabases set status = 0 where name = 'duck'

6. 만약 데이터 베이스의 문제가 발생하여 LDF 파일 뿐만 아니라 MDF 파일까지 손상이 되었다면, 다음사항을 진행하는 편이 좋습니다.

- 먼저 single user 모드로 변경하여 사용자 접속을 차단합니다.

Alter database duck set single_user with rollback immediate

- 손상여부를 체크합니다.

dbcc checkdb('duck')

- 만약 오류가 발생하였다면 다음 쿼리를 실행합니다.

dbcc checkdb('duck',옵션)

(옵션)    

repair_fast :데이터의 손실 위험없이 빨리 복구(클러스터안된 인덱스 복구가능)

repair_rebuild : repair_fast에서 하는 모든 작업, 인덱스 재생과 같이 시간이 소요되는 작업이 복구 과정에서 진행. 데이터 손실 없음.

repair_allow_data_loss : repaid_rebuild 에서 하는 모든 작업, 할당 오류, 구조적 행 오류나 페이지오류, 손상된 텍스트 개채 삭제를 수정하기 위한 행과 페이징의 할당 및 할당 취소 등의 작업이 행해 집니다. 이러한 복구를 할경우 일부 데이터 손실이 될수 있습니다. 복구 완료후 데이터베이스를 백업합니다.

- 위 복구 명령이 완료되는 dbcc checkdb 를 다시 실행하여 오류가 있는지 확인합니다.

- 오류가 발생하지 않으면 single_user 에서 사용자가 접속할수 있게 multi_user 모드로 변경합니다.

Alter database duck set multi_user

7. 모든 작업이 완료 되면 시스템 카탈로그를 수정할수 없게 변경합니다.

sp_configure 'allow update',0

reconfigure with override

go

[참고]

http://www.microsoft.com/korea/technet/sql/tuning_guide_developer03.mspx