mssql tempdb 물리적 위치 변경

USE master
GO


--1. tempdb의논리파일이름확인

SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
GO


--2. ALTER DATABASE 사용하여파일위치변경

ALTER DATABASE tempdb MODIFY FILE(NAME = tempdev, FILENAME = 'd:\mssql\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE(NAME = templog, FILENAME = 'e:\mssql\templog.ldf')
GO


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

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'd:\mssql\tempdev2.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'd:\mssql\tempdev3.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'd:\mssql\tempdev4.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 163840KB )
GO

--4.SQL Server 서비스 재시작.
 

--5.SQL Server 서비스가 시작된것을확인후 정상 이동 확인 

SELECT name, physical_name, state_desc 
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')

참고 : http://blog.naver.com/PostView.nhn?blogId=jevida&logNo=140149742633