MSSQL — изменить количество файлов в tempdb

Олег

22 марта 2022

Допустим, у нас в tempdb только один файл данных и один файл лога. Иногда требуется добавить файлы данных.

К примеру, добавим ещё семь файлов данных:

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'R:\tempdb1.mdf' , SIZE = 8192KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'R:\tempdb2.mdf' , SIZE = 8192KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'R:\tempdb3.mdf' , SIZE = 8192KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'R:\tempdb4.mdf' , SIZE = 8192KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'R:\tempdb5.mdf' , SIZE = 8192KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'R:\tempdb6.mdf' , SIZE = 8192KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'R:\tempdb7.mdf' , SIZE = 8192KB , FILEGROWTH = 102400KB )
GO

Здесь:

NAME — название файла данных
FILENAME — полный путь к файлу данных
SIZE — размер, в данном случае начальный, 8 МБ
FILEGROWTH — автоматический прирост, я поставил 100 МБ

Можно ещё указывать при необходимости:

MAXSIZE — максимальный размер, например, MAXSIZE = 35840000KB

Удалить файл данных

Если нужно удалить один из файлов данных...

USE tempdb
GO
CHECKPOINT
GO
USE tempdb
GO
DBCC SHRINKFILE (tempdev7, EMPTYFILE)
ALTER DATABASE tempdb REMOVE FILE tempdev7
GO

Более сильное колдунство удаления:

USE [tempdb]
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO
DBCC SHRINKFILE (N'temp7' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE temp7
GO

Если файлы заблокированы, то изменяем размер и рост файла. Затем перезапускаем службу SQL. Файлы маленькие и заблокированы для роста? можно их легко удалить.

--before the sql server restart
USE [master]
ALTER DATABASE TempDB MODIFY FILE
(NAME = temp7, SIZE = 1KB, FILEGROWTH = 0 )
GO

--then after the sql server restart
USE [TempDB]
DBCC SHRINKFILE (N'temp7' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp7]
GO