SQL 문제해결 방법론 및 사례 고객 기술 지원부 김 종구

목 차 문제 해결 방법론 사례 연구… 기술 자료들 - 장애 복구와 관련된 KB 기사들 - DB에 문제가 발생한 경우 목 차 문제 해결 방법론 사례 연구… - 장애 복구와 관련된 KB 기사들 - DB에 문제가 발생한 경우 - 백업 및 복원 - MDAC 설치 및 제거 - Oracle과의 연동 - MSDTC 재설치 - 로그 파일 줄이기 기술 자료들

문제 해결 방법론 문제를 구체적으로 정의 한다. - 가능한 모든 데이터(로그)를 수집한다. - 문제 발생지가 어디인가? - 발생 빈도를 확인한다. - 특정 작업 이후에 발생한 것인가? 구체화된 문제를 바탕으로 해결책 모색. - 설정 및 구현이 잘못된 경우. - corruption으로 인해 발생된 문제.

모든 데이터(로그) 수집 이벤트 로그 (시스템, 응용 프로그램) Sqldiag log - Clustering의 경우 (Q233332 참조) Sqlstp.log (setup 실패 시) Sqlsp.log (ServicePack 설치 실패 시) Profiler log Performance log

문제 발생지 및 발생 빈도 확인 DB 자체의 문제인지 Component 또는 Client 상의 문제인지를 확인한다. Query Analyzer에서 수행하여 정상적으로 수행이 되는가를 확인한다. 문제 발생이 규칙적인가 불규칙적 인가? 다른 머신에서도 재현 가능한가?

사례 연구… 장애 복구와 관련된 KB 기사들 DB에 문제가 발생한 경우 백업 및 복원 MDAC 설치 및 제거 Oracle과의 연동 MSDTC 재설치 로그 파일 줄이기

장애 복구와 관련된 KB 기사들 Q307775 INF: Disaster Recovery Articles for Microsoft SQL Server DB 운영 및 이전 작업 중 발생할 수 있는 여러 가지 상황에 대한 KB 기사들을 모아 놓은 자료.

DB에 문제가 발생한 경우 DB 파일에 물리적으로 문제가 발생 Ex) Err. 823 Torn Page detected SQL 서버가 시작 시 DB에 대해 복구가 완료되었다는 확신을 못 가진 경우 Ex) Suspect

Err. 823 Torn Page Detected 하드웨어 장애로 인해 발생하며 물리적으로 consistency에 문제가 생긴 경우. - 오류: 823, 심각도: 24, 상태: 2 spid51 I/O error (torn page) detected during read at offset 0x0000000040e000 in file 'F:\EDMSDB\EDMS_Data.MDF'..

해결 방법 [Best Practice] 1. BACKUP으로부터 Restore가 Best. [Backup이 없는 경우] 1. DBCC CHECKDB 수행. - 문제가 발생한 object를 찾는다. 또는 select * from tbl이 되지 않는 tbl을 찾는다. 2. Select * from tbl with (index = 0|1|2…)를 이용하여 접근가능한 data의 개수를 찾음. 3. DB schema를 scripting하여 다른 DB를 생성. 4. DTS를 이용하여 정상적인 object를 이동.

5. 문제가 발생한 tbl은 insert into new_tbl select * from DB..tbl with (index=1) where ‘indexed_column_name’ <= ‘condition’ 와 같은 형식으로 데이터를 저장함. 6. 문제가 있는 DB를 Detach하고 새로 만든 DB를 기존 DB의 이름으로 attach함. 7. 기존 사용자를 DB_Owner로 설정 8. DBCC CHECKDB를 다시 수행.

[DBCC CHECKDB Repair_Allow_Data_loss 이용] 1. 문제가 발생한 DB에 대하여 DBCC CHECKDB를 수행하면 결과 창에서 위 옵션을 권장하는 경우가 있음. 2. 위 명령을 여러 번 수행하면 문제가 해결되는 경우도 있음. 3. 위 옵션 외에도 repair_fast repair_rebuild 옵션도 있음. (BOL 참조)

5. 다음과 같은 구문을 수행. DBCC TRACEON (3604) Go sp_dboption, ‘DB_name’, ‘single user’, True DBCC CHECKDB (‘DB_Name’, Repair_Allow_Data_Loss)

Suspect 1. 원인 - 다양함. (root cause를 찾는다.) - 시작 시 다른 process가 파일을 사용하 고 있는 경우. - mdf나 ldf 파일에 손상이 간 경우 등등. 2. 해결 방법 - sp_resetstatus 이용 - Emergency Mode로 변경하여 DB 접근 - DBCC Rebuild_log 명령 이용 (sql magazine 9월호 기사 참조) http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26044 http://www.sqlmag.com/Files/09/26044/Listing_03.txt

Sp_configure ‘allow updates’, 1 reconfigure with override Select * from sysdatabases where name = ‘<db_name>’ update sysdatabases set status = 32768 where name = ‘<db_name>’ Restart the SQL Server DBCC REBUILD_LOG(‘<db_name>’, ‘<log_filename>’

백업 및 복원 복구 모델 (전체, 대량 로그, 단순) 백업 종류 (전체, 차등, 트랜잭션 로그) 기본 복구 시나리오 - 전체 백업에서 복원 - 차등 백업에서 복원 - 트랜잭션 로그 백업에서 복원 4. 고급 복구 시나리오 - 파일 그룹 백업 및 복원 - 특정 시점으로 복원하기(stopat, stopatmark) - 데이터 파일로 부터 복원 (sp_attach_db, sp_attach_single_file_db 등 5. Striped backup.(7.0 vs 2000)

MDAC 1. MDAC Version Check Component Checker를 이용. http://www.microsoft.com/data 2. Windows 2K부터 system component로 통합됨. 3. MDAC 제거 및 Uninstall - 제거 : comcheck.exe /d - uninstall : dasetup.exe /u (MDAC 2.6 이후부터 가능) 4. 특정 파일만 바꾸기 불가(SFP 기능 때문)

MDAC Release 정보 MDAC 2.1 – SQL 7.0 MDAC 2.5 - Windows 2000 MDAC 2.5 SP1 – Windows 2000 SP1 MDAC 2.5 SP2 – Windows 2000 SP2 MDAC 2.6 - SQL Server 2000 MDAC 2.6 SP1 – SQL Server 2000 SP1 MDAC 2.6 SP2 – SQL Server 2000 SP2 MDAC 2.7 – Windows XP

5. Upgrade 문제. 6. 설치 실패 문제 해결 1) stop all services that uses MDAC components including the following services. - Certificate Server - Exchange Server - Internet Explorer - Internet Information Server - Microsoft Office (All Office Applications) - Outlook and Outlook Express - SNA Server - SMS Server - SQL Server - Visual Studio (All Development Products)

2) odbc32.dll, oledb32.dll, msado15.dll이 사용 중인지 확인 (ListDll utility이용) http://www.sysinternals.com 에서 다운. 3) 권한 확인 4) 백신 프로그램, Disk Security 프로그램 종료. 5) 디스크 여유 공간 (40MB) 확인 6) Temp 폴더 설정 확인. 7) 7.0 Cluster에는 MDAC 2.6 설치 안됨.

7. 그래도 실패하면… 1) Full installation – MDAC 압축 해제 후 설치 (c:\mdacsetup 폴더로 해제) c:\mdac_typ.exe /c /t:c:\mdacsetup 2) log 남기기 c:\mdacsetup\setup.exe /gc c:\mlog.txt c:\mdac_typ.exe /c:\”setup /gc c:\mlog.txt MDAC 2.6 이후에는 %system root%에 dasetup.log가 있음.

Oracle과의 연동 권장 기사 : Using Oracle with Microsoft Transaction server and COM+ http://support.microsoft.com/support/complus/mtsandoracle.asp Linked Server 연결 시 Oracle에서 제공하는 ODBC 드라이버 사용을 권장. 분산 트랜잭션을 위해서는 XA와 DTC간의 통신이 필요. 에러 발생 시, “OLE/DB 공급자 'MSDAORA'이(가) 분산 트랜잭션을 시작할 수 없으므로 작업을 수행할 수 없습니다” 메시지 Ex) insert into #test6 exec(select * from openquery (OraSrv, ‘select * from test’))

최신의 Oracle Client 설치 최신의 Oracle Net8 설치 MDAC 2.6 SP1이상 설치 Client software registry key값을 변경. Oracle XA Transaction을 지원하도록 설정. 두 개의 view, V$XATRANS$ DBA_PENDING_TRANSACTIONS 가 Oracle에 생성되어 있어야 함. 또한 select 권한이 있어야 함. MSDTCORA.EXE를 이용하여 확인.

MSDTC 재설치 KB Q279786 참조 필수 서비스 외 나머지 멈춤 & set manually start. 서버 재시작 Msdtc –uninstall 다음 registry key 제거. HKEY_CLASSES_ROOT\CID HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MSDTC HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC

5. %system root%\system32에서 Dtcsetup.exe 실행 6. 설치가 끝난 후 서비스를 원래대로 재설정. 7. Msdtc –resetlog 수행.

로그 줄이기 Transaction log의 작동 방법. - 초기 공간은 Virtual Log로 나눠진다. - MinLSN으로 표시된 부분은 truncate 시 지워지지 않는다. - 로그가 파일의 마지막 부분까지 기록되고 나면, wrap around 방식을 이용하여 로그의 앞부분으로 이동, 이 작업 후에야 truncate가 가능.

Transaction Log Virtual Log File Log files are divided into virtual log files The number of VLFs varies; at least two VLFs in a file Log File 8 KB Header VLF Available File Space Number of VLFs VLF Size Up to 1 MB 2 – 4 : space / 248 KB 248 KB - 334 KB Up to 64 MB 4 256 KB - 16 MB Up to 1 GB 8 8 MB - 128 MB Above 1 GB 16 64 MB +

Technical Resources BOL (updated version) http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp SQL Server 홈페이지 http://www.microsoft.com/korea/sql http://www.microsoft.com/sql 기술지원 홈페이지 http://support.microsoft.com

White Paper - For SQL 2000 HTTP://support.microsoft.com/support/search/canned.asp?R=d&H=SQL Server 2000 White Papers&LL=kbSQLServ2000Search&Sz=kbwhitepaper&CDID=EN-US-KB&LCID=1033 - For SQL 7.0 & SQL 6.5 http://support.microsoft.com/default.aspx?scid=/support/sql/papers.asp

Newsgroup http://www.microsoft.com/korea/sql/support/newsgroups/default.asp http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.kr.sql&SLCID=kr&sd=GN&id=fh;KO;NEWSGROUPS

MSDN & TechNet http://support.microsoft.com/default.aspx?SD=msdn&pr=sql (SQL Portal) http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28000409 (SQL Product Technical Information) http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001300 (SQLXML) http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/default.asp

WebCast http://support.microsoft.com/default.aspx?scid=fh;RID;webcst&style=toc&sd=gn Online Seminar http://www.microsoft.com/seminar/ Online chat http://msdn.microsoft.com/chats/ Newsletter http://www.microsoft.com/misc/subscribe.htm

Summary 문제 해결 방법론 사례 연구… 기술 자료들 - 장애 복구와 관련된 KB 기사들 - DB에 문제가 발생한 경우 - 백업 및 복원 - MDAC 설치 및 제거 - Oracle과의 연동 - MSDTC 재설치 - 로그 파일 줄이기 기술 자료들