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 = 'C:\TempDB\tempdb.mdf')

ALTER DATABASE tempdb

MODIFY FILE(NAME = templog, FILENAME = 'C:\TempDB\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'C:\TempDB\tempdev2.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'C:\TempDB\tempdev3.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'C:\TempDB\tempdev4.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'C:\TempDB\tempdev5.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'C:\TempDB\tempdev6.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'C:\TempDB\tempdev7.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev8', FILENAME = N'C:\TempDB\tempdev8.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')

GO