Tempdb 최적화 – tempdb 데이터파일 제거하기

 

쉬운 작업인데 사실 사용중에 작업하기에는 부담이 될 수 있다.

 

 

[환경만들기]

tempdb 데이터파일이 9개, 로그파일이 1개로 구성된 템프디비를 변경해 보자. 각 데이터파일도 사이즈가 제각각이다.

 

sp_helpdb tempdb

go

 

 

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\tempdev2.ndf' , SIZE =2MB , FILEGROWTH = 2MB)

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\tempdev3.ndf' , SIZE =3MB , FILEGROWTH = 3MB)

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev4',

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\tempdev4.ndf' , SIZE =4MB , FILEGROWTH = 4MB)

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev5',

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\tempdev5.ndf' , SIZE =5MB , FILEGROWTH = 5MB)

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev6',

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\tempdev6.ndf' , SIZE =6MB , FILEGROWTH = 6MB)

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev7',

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\tempdev7.ndf' , SIZE =7MB , FILEGROWTH = 7MB)

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev8',

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\tempdev8.ndf' , SIZE =8MB , FILEGROWTH = 8MB)

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev9',

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\tempdev9.ndf' , SIZE =9MB , FILEGROWTH = 9MB)

 

sp_helpdb tempdb

go

 

 

 

[작업내역]

1. tempdev9 파일을 제거하기

 

-- 데이터파일을 초기화 단계.

USE tempdb

GO

DBCC SHRINKFILE (tempdev9, EMPTYFILE);

GO

 

/*

DbId    FileId    CurrentSize    MinimumSize    UsedPages    EstimatedPages

2        10        1152        1152        0            0

*/

-- 데이터파일 제거 단계.

 

ALTER DATABASE tempdb

REMOVE FILE tempdev9; --to delete "tempdev12" data file

GO

 

-- 제거된 결과보기

sp_helpdb tempdb

 

2. 각기 다른 파일 사이즈 통일하기.

 

-- 8MB에서 5MB 로 줄이기

USE [tempdb]

GO

    DBCC SHRINKFILE (N'tempdev' , 5)

    GO

 

 

    DBCC SHRINKFILE (N'tempdev6' , 5)

    DBCC SHRINKFILE (N'tempdev7' , 5)

    DBCC SHRINKFILE (N'tempdev8' , 5)

 

 

-- 작은 파일은 늘려주기

USE [master]

GO

    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev2', SIZE = 5MB, filegrowth=5MB )

    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev3', SIZE = 5MB, filegrowth=5MB )

    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev4', SIZE = 5MB, filegrowth=5MB )

 

    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev6', filegrowth=5MB )

    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev7', filegrowth=5MB )

    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev8', filegrowth=5MB )

 

-- 최종 결과 확인

    select DB_NAME(mf.database_id) database_name

    , mf.name logical_name, mf.file_id

    , CONVERT (DECIMAL (20,2)

    , (CONVERT(DECIMAL, size)/128)) as [file_size_MB]

    , CASE mf.is_percent_growth

    WHEN 1 THEN 'Yes'

    ELSE 'No'

    END AS [is_percent_growth]

    , CASE mf.is_percent_growth

    WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'

    WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'

    END AS [growth_in_increment_of]

    , CASE mf.is_percent_growth

    WHEN 1 THEN CONVERT(DECIMAL(20,2)

    ,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)

    WHEN 0 THEN CONVERT(DECIMAL(20,2)

    , (CONVERT(DECIMAL, growth)/128))

    END AS [next_auto_growth_size_MB]

    , physical_name from sys.master_files mf

    where database_id =2 and type_desc= 'rows'