SQL Server tempdb 손상시 Trace Flag 3609 사용하기

   

SQL Server 에서 TempDB를 별도 디스크에 배치하여 사용을 권장하곤 한다. 그런데 해당 디스크가 장애가 발생되면 어떻게 될까?

SQL 서비스는 정상적일까?

   

[장애]

Tempdb 가 손상되었을 경우나 해당 디스크장애로 인하여 tempdb를 사용하지 못하게 되었을 경우 장애가 발생한다.

   

[장애발생 시나리오]

SQL Server 2005 환경에서 tempdb 를 별도 디스크로 분리 후 해당 디스크를 제거를 한다.

제거하면 아래와 같은 응용프로그램로그가 발생한다.

   

SQL Server 어설션: 파일: <latch.cpp>, 줄 = 1538 어설션 오류 = '0' Attempted release of unacquired latch. 이 오류는 타이밍과 관련된 것일 수 있습니다. 문을 다시 실행한 후에도 이 오류가 지속되면 DBCC CHECKDB를 사용하여 데이터베이스의 구조적 무결성을 확인하거나 서버를 다시 시작하여 메모리 내부 데이터 구조가 손상되지 않도록 하십시오.

   

SQL Server 서비스가 정상인지 확인한다 - 사용자데이터베이스의 접근은 정상이다. 다른 서비스는 모르겠다.

   

[Action Plan]

#.1 정상적인 디스크에 tempdb 경로를 변경해본다.

시스템카탈로그를 변경후에 재시작하여 경로를 변경하는 작업이다. 오류가 발생한다.

alter database tempdb

modify file (name='tempdev', filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf')

GO

alter database tempdb

modify file (name='templog', filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf')

   

메시지 9001, 수준 21, 상태 1, 줄 1

데이터베이스 'tempdb'의 로그를 사용할 수 없습니다. 관련 오류 메시지의 이벤트 로그를 확인하십시오. 모든 오류를 해결하고 데이터베이스를 다시 시작하십시오.

   

#.2 SQL Server 서비스를 재 시작해본다.

아래와 같이 tempdb 를 만들 수 없다는 메시지와 SQL 서비스 시작 실패가 된다.

2016-04-07 17:13:52.35 spid9s 오류: 5123, 심각도: 16, 상태: 1.

2016-04-07 17:13:52.35 spid9s CREATE FILE encountered operating system error 3(지정된 경로를 찾을 수 없습니다.) while attempting to open or create the physical file 'E:\SQLTEMP\tempdb.mdf'.

2016-04-07 17:13:52.49 spid9s 오류: 17207, 심각도: 16, 상태: 1.

2016-04-07 17:13:52.49 spid9s FCB::Open: Operating system error 3(지정된 경로를 찾을 수 없습니다.) occurred while creating or opening file 'E:\SQLTEMP\tempdb.mdf'. Diagnose and correct the operating system error, and retry the operation.

2016-04-07 17:13:52.52 spid9s 오류: 17204, 심각도: 16, 상태: 1.

2016-04-07 17:13:52.52 spid9s FCB::Open failed: Could not open file E:\SQLTEMP\tempdb.mdf for file number 1. OS error: 3(지정된 경로를 찾을 수 없습니다.).

2016-04-07 17:13:52.52 spid9s 오류: 5120, 심각도: 16, 상태: 101.

2016-04-07 17:13:52.52 spid9s 물리적 파일 "E:\SQLTEMP\tempdb.mdf"을(를) 열 수 없습니다. 운영 체제 오류 3: "3(지정된 경로를 찾을 수 없습니다.)".

2016-04-07 17:13:52.62 spid9s 오류: 1802, 심각도: 16, 상태: 4.

2016-04-07 17:13:52.62 spid9s CREATE DATABASE가 실패했습니다. 나열된 일부 파일 이름을 만들 수 없습니다. 관련 오류를 확인하십시오.

2016-04-07 17:13:52.65 spid9s 오류: 5123, 심각도: 16, 상태: 1.

2016-04-07 17:13:52.65 spid9s 물리적 파일 'E:\SQLTEMP\tempdb.mdf'을(를) 열거나 만드는 중 CREATE FILE에서 운영 체제 오류 3(지정된 경로를 찾을 수 없습니다.)이(가) 발생했습니다.

2016-04-07 17:13:52.65 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

2016-04-07 17:13:52.65 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

   

#.3 Trace Flag 3609 로 tempdb 없이 SQL을 시작한다.

SQL Server 구성관리자에서 아래와 같이 -T3609 를 추가하고 서비스를 시작한다.

   

재 시작 후 SQL로그를 보면 아래와 같이 tempdb를 생성하지 않음을 볼 수 있다.

2016-04-07 17:17:28.01 Server Registry startup parameters:

2016-04-07 17:17:28.01 Server          -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

2016-04-07 17:17:28.01 Server          -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2016-04-07 17:17:28.01 Server          -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2016-04-07 17:17:28.01 Server          -T 845

2016-04-07 17:17:28.01 Server          -T 3609

2016-04-07 17:17:28.07 서버 SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2016-04-07 17:17:30.15 spid5s Recovering all databases, but not clearing tempdb. This is an informational message only. No user action is required.

2016-04-07 17:17:30.15 spid5s Starting up database 'master'.

   

정상적으로 서비스는 시작되었다.

   

Tempdb 시스템 카탈로그를 아래와 같이 변경 후 -T3609 옵션 제거 후 서비스를 재 시작해준다.

   

alter database tempdb

modify file (name='tempdev', filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf')

GO

alter database tempdb

modify file (name='templog', filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf')

시스템 카탈로그에서 파일 "tempdev"이(가) 수정되었습니다. 새 경로는 다음에 데이터베이스가 시작될 때 사용됩니다.

시스템 카탈로그에서 파일 "templog"이(가) 수정되었습니다. 새 경로는 다음에 데이터베이스가 시작될 때 사용됩니다.

   

재 시작 후 SQL로그를 확인한다.

2016-04-07 17:20:36.63 서버 SQL Server is now ready for client connections. This is an informational message; no user action is required.

2016-04-07 17:20:36.64 spid13s Starting up database 'msdb'.

2016-04-07 17:20:37.72 spid9s Clearing tempdb database.

2016-04-07 17:20:41.59 spid9s Starting up database 'tempdb'.

   

정상적으로 복구 하였다.

   

다른 방법도 있지만 여기서는 TF3609 를 설명하기 위해서 상황을 만들어 봤다.