[MSSQL]tempDB 경로변경으로 인한 서버 재시작불가(오류코드:17204,5123)

tempDB는 모든 데이터베이스에서 공통으로 사용하는 임시 공간입니다.

이 공간에 문제가 발생하면  Latch 경합 등 다양한 문제가 발생할 수 있습니다.

 

작업내용

Tempdb 경로를 변경 후 SQL서버 재시작

 

오류내용

SQL서버 재시작이 불가하였고 윈도우 이벤트 로그를 확인하니 아래와 같이 2가지 에러 로그가 찍혀 있었습니다.

 

이벤트 오류 ID : 5123

물리적 파일 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temp\tempdb.mdf'을(를) 열거나 만드는 중 CREATE FILE에서 운영 체제 오류 5(액세스가 거부되었습니다.)이(가) 발생했습니다.

이벤트 오류 ID : 17204

FCB::Open failed: 파일 번호 1에 대한 파일 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temp\tempdb.mdf을(를) 열 수 없습니다. OS 오류: 2(지정된 파일을 찾을 수 없습니다.).

 

 

[해결방법]

이벤트 오류 ID : 5123

다행히도 파일이름 및 경로는 제대로 입력하여 권한만 추가하였다.

 

1.신규로 생성한 폴더 속성을 클릭합니다.

2.SQL서버 사용자 권한수정을 위해 편집을 클릭합니다.

3.수정 권한에 체크하고 확인을 눌러 수정권한을 부여합니다.

이렇게 하면 권한 문제로 생성이 불가능 했던 오류가 간단하게 해결됩니다.

---

이벤트 오류 ID : 17204

해당 오류는 Tempdb 경로를 수정할 때 파일 이름을 잘못 지정한 경우입니다.

 

정상적으로 설정하려고 했던 경로 변경 구문

ALTER DATABASE tempdb
MODIFY FILE(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdb.mdf')
GO

ALTER DATABASE tempdb
MODIFY FILE(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\templog.ldf')
GO

 

오타로 인한 잘못된 변경 구문

ALTER DATABASE tempdb
MODIFY FILE(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\')
GO

ALTER DATABASE tempdb
MODIFY FILE(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\templog.ldf')
GO

위와 같이 tempdb.mdf를 실수로 빼먹은 경우 시스템에 재기동 시 문제가 생깁니다.

이럴경우 CMD에서 비상기동을 진행합니다(해당 기동은 master DB만 접근이 가능한 비상모드입니다)

 

비상기동

1.관리가 권한으로 CMD창을 열어서 Binn 폴더로 이동합니다.

이 폴더의 위치는 설치 시 위치 및 SQL Server의 버전마다 다릅니다.

2.옵션을 추가하여 SQL서버를 시작합니다.

만약 아래와 같이 오류가 발생하면 서버이름을 추가해 줍니다.

https://msdn.microsoft.com/ko-kr/library/ms190737(v=sql.120).aspx

https://msdn.microsoft.com/ko-kr/library/ms188396(v=sql.120).aspx

 

https://sites.google.com/site/jayantdass/trace-flag--t3608--t4022-sql-server

 

 

 

서버이름 추가 진행

아래와 같이 서버이름을 추가해서 명령어를 입력합니다.

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\Binn>sqlservr.exe -sMSSQLSERVER_TEST /c /f -T3608 -T4022

3.ssms접속을 진행합니다. 단일 사용자 모드이고 개체 탐색기에서 이미 점유하고 있어서 접속 오류가 발생합니다.

새쿼리를 눌러서 쿼리창만 활성화 한 다음 tempdb 경로를 정상적으로 수정해 줍니다.

ALTER DATABASE tempdb MODIFY FILE(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdb.mdf')

4.서비스를 재시작 후 정상적으로 SQL서버가 시작되는 것을 확인 할 수 있습니다.

아래는 변경된 tempdb 정보는 보는 쿼리입니다.

Use tempdb
go
sp_helpfile

 

마지막으로 필요한 경우 tempdb 수량을 및 자동증가 옵션용량 등을 추가해 줍니다.

 

--Processor만큼파일분할및사이즈변경및파일사이즈, 증가옵션설정

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 8192KB , FILEGROWTH = 8192KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdev_2.ndf' , SIZE = 8192KB , FILEGROWTH = 8192KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdev_3.ndf' , SIZE = 8192KB , FILEGROWTH = 8192KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdev_4.ndf' , SIZE = 8192KB , FILEGROWTH = 8192KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_5', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdev_5.ndf' , SIZE = 8192KB , FILEGROWTH = 8192KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_6', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdev_6.ndf' , SIZE = 8192KB , FILEGROWTH = 8192KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_7', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdev_7.ndf' , SIZE = 8192KB , FILEGROWTH = 8192KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_8', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\temptest\tempdev_8.ndf' , SIZE = 8192KB , FILEGROWTH = 8192KB )

GO



ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 8192KB )

GO