1TB 이상의 백업 파일을 32bit SQL Server 2008 환경에서 데이터베이스를 복원하는 데 예정보다 많은 시간이 지연되며, 메모리 부족에 의한 restore 실패 현상이 발생한 사례입니다. SQL 서비스 시작 매개변수에 -g 스위치을 사용하여 MemToLeave 또는 Non Buffer Pool 영역을 512MB 이상으로 부여하여도 부족한 MemToLeave 영역은 어쩔 수 없습니다. 하지만 동일한 데이터베이스를 64bit 머신에서는 복원이 된다는 것은 확인하였습니다. 자, 그럼 어떤 경우 이런 문제가 발생할 수 있을까요?
[환경]
SQL Server 2008 SP1 (x86)
[현상]
1TB 의 데이터베이스를 Restore 시도하였으나 아래와 같은 오류와 함께 Restore 를 실패하였습니다.
오류: 701, 심각도: 17, 상태: 123.
There is insufficient system memory in resource pool 'internal' to run this query.
[원인]
32bit SQL Server 에서 가상 로그 파일(VLF)이 30만개가 넘는 트랜잭션 로그를 RESTORE 하는 과정 중 Roll Forward 단계를 수행하는 단계에서 가용한 MemToLeave 메모리 공간이 부족하여 RESTORE 가 실패한 것으로 추정됩니다. 701 오류 발생 시,덤프 생성을 통해 보다 정확한 원인을 파악해 볼 수 있습니다. 수 많은 VLF 파일이 생성된 원인은 로그 파일 자동 증가 설정이1MB 로 설정되어 있으며 10MB 이하여 작은 초기 사이즈과 트랜잭션 로그 백업 주기가 발생하는 트랜잭션에 비해 길게 설정되어 있어 이러한 문제가 발생합니다.
로그 파일의 VLF(Virtual Log File)의 개수가 많을 때 Restore 시 트랜잭션 롤백, 롤포워드 진행 과정에서 성능에 영향을 받게 됩니다.
[해결방안]
시스템 트랜잭션을 일정 기간 모니터링 한 후 적정 기준의 트랜잭션 로그 파일의 최초 크기, 자동 증가 크기를 설정합니다.
1. 트랜잭션 로그 백업
2. 트랜잭션 로그 파일 자동 증가 크기를 적정 단위로 설정합니다.
3. VLF 개수를 줄이기 위해 Shrinkfile 을 진행합니다.
4. DBCC LOGINFO 명령으로 줄더느 VLF 개수를 확인합니다.
5. 수동으로 트랜잭션 로그 파일의 크기를 미리 확장해 놓습니다.
[분석결과]
RESTORE 과정을 ERRORLOG 에 기록하도록 TRACEON 을 설정하고 RESTORE 진행합니다.
1. ERRORLOG 확인 결과
1. 트랜잭션 로그 백업
2. 트랜잭션 로그 파일 자동 증가 크기를 적정 단위로 설정합니다.
3. VLF 개수를 줄이기 위해 Shrinkfile 을 진행합니다.
4. DBCC LOGINFO 명령으로 줄더느 VLF 개수를 확인합니다.
5. 수동으로 트랜잭션 로그 파일의 크기를 미리 확장해 놓습니다.
[분석결과]
RESTORE 과정을 ERRORLOG 에 기록하도록 TRACEON 을 설정하고 RESTORE 진행합니다.
DBCC TRACEON (3004, 3014, 3212, 3213, 3228, 3244, 3605, -1)
RESTORE DATABASE [LAIGO] FROM DISK = 'D:\BACKUP\LAIGO.BAK' WITH NORECOVERY
1. ERRORLOG 확인 결과
DBCC TRACEON 3004, server process ID (SPID) 52
DBCC TRACEON 3014, server process ID (SPID) 52.
DBCC TRACEON 3212, server process ID (SPID) 52
DBCC TRACEON 3213, server process ID (SPID) 52
DBCC TRACEON 3228, server process ID (SPID) 52
DBCC TRACEON 3244, server process ID (SPID) 52
DBCC TRACEON 3605, server process ID (SPID) 52
RestoreDatabase: Database LAIGO
Opening backup set
Restore: Configuration section loaded
Restore: Backup set is open
Restore: Planning begins
Backup/Restore buffer configuration parameters -- 데이터베이스 Restore 구성 파라미터
Memory limit: 10238MB
BufferCount: 17
MaxTransferSize: 1024 KB
Min MaxTransferSize: 64 KB
Total buffer space: 17 MB
Tabular data device count: 6
Fulltext data device count: 0
Filestream device count: 0
TXF device count: 0
Filesystem i/o alignment: 512
Media Buffer count: 17
Media Buffer size: 1024KB
Restore: Planning complete
Restore: BeginRestore (offline) on LAIGO
Restore: Attached database LAIGO as DBID=8
Restore: PreparingContainers
-- 복원할 데이터베이스 파일, 또는 페이지 내용 초기화
Zeroing Z:\SQLDATA\LAIGO_log.ldf from page 1 to 31993 (0x2000 to 0xf9f2000)
Zeroing Z:\SQLDATA\LAIGO_log.ldf from page 1 to 31993 (0x2000 to 0xf9f2000)
Zeroing Y:\SQLDATA\LAIGO_log_2.ldf from page 1 to 49993 (0x2000 to 0x18692000)
Zeroing X:\SQLDATA\LAIGO_log_1.ldf from page 1 to 521296 (0x2000 to 0xfe8a0000)
Zeroing W:\SQLDATA\LAIGO_log_3.ldf from page 1 to 12800 (0x2000 to 0x6400000)
Zeroing V:\SQLDATA\LAIGO_log_4.ldf from page 1 to 10123008 (0x2000 to 0x134ee00000)
Restore: Containers are ready
Restore: Restoring backup set
Restore: Transferring data to LAIGO
Starting MSDA in stream 0, thread 2488, length 0x1745d800000
Zeroing completed on Z:\SQLDATA\LAIGO_log_3.ldf
Zeroing completed on Y:\SQLDATA\LAIGO_log.ldf
Zeroing completed on X:\SQLDATA\LAIGO_log_2.ldf
Zeroing completed on W:\SQLDATA\LAIGO_log_1.ldf
Zeroing completed on Y:\SQLDATA\LAIGO_log_4.ldf
Completed MSDA in stream 0, thread 2488
Restore: Waiting for log zero on LAIGO
Restore: LogZero complete
FileHandleCache: 0 files opened. CacheSize: 34
Restore: Data transfer complete on LAIGO
Restore: Backup set restored
Starting up database 'LAIGO'.
The database 'LAIGO' is marked RESTORING and is in a state that does not allow recovery to be run.
Restore-Redo begins on database LAIGO
-- 전체 백업에 포함된 로그부터 시작하여 순서대로 로그 백업이 복원되도록 롤포워드를 수행합니다.
-- 시간 확인 결과 Roll Forward 구간에서 오랜 시간 지연이 발생하고 있습니다.
-- 시간 확인 결과 Roll Forward 구간에서 오랜 시간 지연이 발생하고 있습니다.
Rollforward complete on database LAIGO
Restore: Done with fixups
Restore: Writing history records
Database was restored: Database: LAIGO, creation date(time): ***, first LSN: 1402101:16:21, last LSN: 1409262:487:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'***SQL-0000089C-0000'}).
Writing backup history records
Restore: Done with MSDB maintenance
RestoreDatabase: Finished
2. DBCC LOGINFO 확인 결과 : VLF 개수가 30만개를 초과하였습니다.
3. SQL 서비스 시작 매개변수에 -g512 스위치를 사용하여 복원을 시도하였으나 메모리 부족으로 쿼리 실행이 중지되기까지 시간만 연장 되었을 뿐 근본적인 문제 해결은 되지 않았습니다.
4. SQL Server 2008 (x64) 환경에서는 정상적으로 RESTORE 를 성공하였으나 마찬가지 RESTORE 에 많은 시간이 소요됨.
5. 스크립트를 사용하여 MTL의 Max free size 를 확인한 결과 최초 156068KB 에서 78208KB 까지 감소된 이후 복원 실패
6. 트랜잭션 로그 파일 자동 증가 단위 : 1MB
7. 데이터베이스 Detach 후 Attach 할 때도 트랜잭션 Roll Forward 에 의한 시간 지연 발생
[참고자료]
Transaction Log Physical Architecture
http://technet.microsoft.com/en-us/library/ms179355.aspx
SQL Server에서의 백업 복원 및 복구 작동 방법 이해
작성자 : Lai Go / 작성일자 : 2011.04.07
