Как изменить путь к tempdb

Системная база данных tempdb служит рабочим пространством для хранения временных объектов, таких как временные таблицы, промежуточные результаты вычислений, временные хранимые процедуры, результаты буферов и сортировки, внутренние объекты, создаваемые компонентой Database Engine и пр. Кроме того, все изменения данных в базах данных, в которых используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков, а также изменения данных для таких операций, как операции с индексами в сети, множественные активные результирующие наборы (режим MARS) и триггеры AFTER также хранятся в системной базе данных tempdb. Таким образом, данная база данных используется системой довольно активно, и возможно получить значительный прирост производительности путем переноса файлов базы данных tempdb на отдельный дисковый накопитель, более быстрый SSD-диск или даже RAM-диск. О перемещении системной базы данных tempdb в MS SQL Server 2012 (справедливо для более ранних версий) и пойдет речь в данной статье.

1. Указание каталога хранения файлов базы данных tempdb во время установки  MS SQL Server 2012

Указать отдельный каталог расположения файлов базы данных tempdb, можно еще на стадии установки MS SQL Server 2012, на странице настройки компоненты Database Engine на вкладке «Каталоги данных» (Data Directories).

Подробнее про установку MS SQL Server 2012 можно прочить здесь.

2. Изменения каталога хранения файлов базы данных tempdb для существующего экземпляра MS SQL Server

Если же имеется уже установленный экземпляр MS SQL Server, то изменить каталог хранения файлов базы данных tempdb не удастся с помощью стандартных средств отсоединение и присоединение базы данных. В этом случае поможет инструкция ALTER DATABASE языка Transact-SQL. Подключимся к экземпляру MS SQL Server, для которого необходимо выполнить изменения, с помощью программы  «SQL Server Management Studio».

Просмотрим текущее расположение файлов базы данных tempdb. Для этого в обозревателе объектов (Object Explorer) раскроем вкладки «Базы данных» (Databases) — «Системные базы данных» (System Databases). Кликнем правой кнопкой мыши по базе данных tempdb и в контекстном меню выберем пункт «Свойства» (Properties).

В открывшемся окне свойств перейдем на вкладку «Файлы» (Files), где можно увидеть имена файлов (File Name), логические имена файлов (Logical Name) и путь расположения каждого из файлов (Path).

Для того чтобы изменить путь расположения необходимо выполнить запрос:

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQL_Temptempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:SQL_Temptemplog.ldf');
GO

Где E:SQL_Temp новый путь расположения файлов базы данных tempdb. Каталоги файла данных базы — файла tempdb.mdf (или файлов, если их несколько) и файла журнала транзакций — tempdb.ldf могут различаться и, соответственно, могут быть изменены независимо. Для того чтобы выполнить запрос, необходимо нажать кнопку на панели задач «Создать запрос» (New Query), в окне нового запроса ввести текст запроса и нажать кнопку «Выполнить» (Execute), расположенную там же, на панели задач.

После чего необходимо перезапустить экземпляр SQL Server. Сделать это можно, здесь же, кликнув правой кнопкой мыши по имени сервера в обозревателе объектов, и выбрав в контекстном меню пункт «Перезапустить» (Restart).

Если теперь вызвать свойства базы данных tempdb, можно увидеть, что путь расположения файлов изменился.

При старте SQL Server база данных tempdb создается каждый раз заново. Поэтому из старого каталога файлы tempdb.mdf и tempdb.ldf необходимо удалить вручную.

3. Универсальный скрипт для продвинутых

-------------------------------------------
-- Скрипт перемещает все файлы базы данных tempdb в указанный каталог
-- Версия от 27.10.2021
-- Свежие версии скриптов: https://github.com/Tavalik/SQL_TScripts
 
-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПЕРЕМЕННЫЕ
-- Новый каталог для базы tempdb
DECLARE @Path as NVARCHAR(400) = 'E:SQL_Temp'
 
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @physicalName NVARCHAR(500), @logicalName NVARCHAR(500)
DECLARE @SQLString NVARCHAR(400)
 
-------------------------------------------
-- ТЕЛО СКРИПТА
USE master;
 
-- Цикл по всем файлам базы данных tempdb
DECLARE fnc CURSOR LOCAL FAST_FORWARD FOR 
	(
		SELECT
			name,
			physical_name
		FROM sys.master_files 
		WHERE database_id = DB_ID('tempdb')
	)
OPEN fnc;
FETCH fnc INTO @logicalName, @physicalName;
WHILE @@FETCH_STATUS=0
 
	BEGIN
		SET @SQLString = '
		ALTER DATABASE tempdb
		MODIFY FILE (NAME = ' + @logicalName 
		+ ', FILENAME = ''' + @Path + '' 
		+ REVERSE(SUBSTRING(REVERSE(@physicalName), 1, CHARINDEX('', REVERSE(@physicalName))-1)) 
		+ ''');'
 
		PRINT @SQLString
		EXEC sp_executesql @SQLString
 
		FETCH fnc INTO @logicalName, @physicalName;
	END;
 
CLOSE fnc;
DEALLOCATE fnc;

Больше полезных скриптов здесь: https://github.com/Tavalik/SQL_TScripts

Роман, данная операция выполняется в несколько этапов.

Настоятельно рекомендую не размещать файлы в корне диска даже если он создан только под хранение баз(ы). Создайте отдельную директорию, к примеру, так её и назовите:
TEMPDB 

Убедитесь, что у учётной записи, из-под которой запущена служба SQL Server Database Engine, есть права на чтение и модификацию этой директории. Лучше даже выдать полные права на NT SERVICEMSSQLSERVER,
либо NT SERVICEMSSQL$ имя_экземпляра
если инстанс именованный.

Далее открываем SQL Server Management Studio и выполняем следующую команду:

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

Результатом будет что-то такое:

Нам нужно содержение колонки name, это логические имена файлов на которые разбита база.

Для каждого файла нужно выполнить следующий код:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_pathos_file_name' )

Где

database_name — имя базы, в нашем случае – tempdb

logical_name — логическое имя файла базы, в случае моего примера это tempdev, templog, temp2, temp3 и т.д.

new_pathos_file_name — полный путь и физическое название файла, к примеру: T:TEMPDBtempdb.mdf
Вышеупомянутый запрос, который вернул логические имена файлов, также возвращает колонку
CurrentLocation По сути, Вам просто нужно скопировать её содержимое и поменять первую часть строки с текущим размещением файла на новое.

Для моего примера код для перемещения файлов будет следующий:

ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = tempdev, FILENAME = T:TEMPDBtempdb.mdf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = templog, FILENAME = T:TEMPDBtemplog.ldf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp2, FILENAME = T:TEMPDBtempdb_mssql_2.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp3, FILENAME = T:TEMPDBtempdb_mssql_3.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp4, FILENAME = T:TEMPDBtempdb_mssql_4.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp5, FILENAME = T:TEMPDBtempdb_mssql_5.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp6, FILENAME = T:TEMPDBtempdb_mssql_6.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp7, FILENAME = T:TEMPDBtempdb_mssql_7.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp8, FILENAME = T:TEMPDBtempdb_mssql_8.ndf )

Сгененрировал я его седующим путём (перед выполнением нажмите CTRL + T для возврата результата в виде текста):

SELECT 'ALTER DATABASE ''tempdb'' MODIFY FILE ( NAME = '+[name]+', FILENAME = '+[physical_name]+' )'
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

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

1. Файлы не обязательно переносить с места на место, система сама создаст их во время старта службы.
2. Если файлы большие
   – желательно дать системной учётной записи право на Perform Volume Maintenance Tasks
3. Пока не будет создан основной файл и файл лога — служба полностью не стартует, СУБД будет не доступна.
4. Рекомендую для проверки не перезапускать службу, а остановить. Далее переименовать старую директорию где была tempdb, и только потом запускать службу. Так Вы точно поймёте, что всё работает верно и всегда сможете откатиться
   обратно.

Частые ошибки, которые с которыми ко мне обращаются за помощью:

1. Забыли выдать доступ на новую директорию
2. Нашли скрипт в интернете на перенос только основного файла данных и журнала транзакций, бездумно запустили
3. Поменяли настройки, но не перезапустили службу по ряду причин. Другой администратор увидел пустую папку и удалил её. Когда службу перезапустили, она не стартовала, так как не было где разместить tempdb

В любом случае, если что-то не так – посмотрите лог ошибок. Ну и пишите. Уверен, что всё пройдёт удачно, операция не сложная.

Временная база данных (tempdb) в SQL Server — это системная база данных, доступная всем пользователям, подключенным к экземпляру SQL Server. В tempdb хранятся как временные пользовательские объекты, так и внутренние объекты, создаваемые ядром СУБД.

Tempdb пользуется особой популярностью, поэтому для нее настоятельно рекомендуется размещать файлы данных и журнала транзакций на специально выделенных дисках, отдельно от операционной системы и других баз данных. И желательно на максимально быстром хранилище с низким временем задержки, типа SSD NVMe или Intel Optane.

Также для ускорения ввода-вывода рекомендуется создавать несколько файлов данных в зависимости от количества логических процессоров, выделенных для сервера базы данных (Database Engine). Если число логических процессоров меньше восьми, рекомендуется создать файл данных для каждого логического процессора, если равно или больше восьми, то используйте восемь файлов данных.

В идеале размещение баз лучше планировать заранее, но, к сожалению, это не всегда возможно. И может возникнуть ситуация, когда требуется переместить файлы базы на уже работающем сервере. Для tempdb эта процедура несложная, рассмотрим ее по пунктам.

Первым делом выделяем новый диск H, на котором будут находиться файлы TempDB.  Размещать файлы базы в корне диска не рекомендуется, даже если диск целиком выделен под их хранение, поэтому создаем отдельную директорию, назовем ее TempDB. На эту директорию необходимо выдать права на чтение и изменение учетной записи, от имени которой работает служба MS SQL (SQL Server Database Engine).

Теперь нам надо выяснить текущее расположение файлов tempdb. Сделать это можно из графической оснастки, открыв в свойствах базы вкладку Files

либо с помощью вот такого sql-запроса:

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

В поле Name мы получаем логическое имя файла, а в поле CurrentLocation путь к файлу. Как видите, в нашем примере имеется 8 файлов базы и один файл журнала транзакций.

Для каждого файла нужно выполнить следующий код:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_pathfile_name' )

Где:

• database_name — имя базы (в нашем случае tempdb);
• logical_name — логическое имя файла базы;
• new_pathfile_name — новый путь и физическое имя файла.

Можно немного облегчить себе жизнь и сгенерировать код с помощью запроса (перед выполнением нажмите CTRL + T для возврата результата в виде текста):

SELECT 'ALTER DATABASE ''tempdb'' MODIFY FILE ( NAME = '+[name]+', FILENAME = '+[physical_name]+' )'
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

В полученном запросе остается только поменять старый путь на новый. В результате получим:

ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = tempdev, FILENAME = H:TEMPDBtempdb.mdf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = templog, FILENAME = H:TEMPDBtemplog.ldf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp2, FILENAME = H:TEMPDBtempdb_mssql_2.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp3, FILENAME = H:TEMPDBtempdb_mssql_3.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp4, FILENAME = H:TEMPDBtempdb_mssql_4.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp5, FILENAME = H:TEMPDBtempdb_mssql_5.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp6, FILENAME = H:TEMPDBtempdb_mssql_6.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp7, FILENAME = H:TEMPDBtempdb_mssql_7.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp8, FILENAME = H:TEMPDBtempdb_mssql_8.ndf )

Выполняем полученный запрос.

Рестартуем службу MS SQL (SQL Server Database Engine). Проверяем в настройках tempdb расположение файлов.

И затем идем в новую папку и проверяем, что в ней появились файлы.

На этом процесс переноса завершен и можно удалить файлы из старого расположения. В завершение некоторые важные моменты:

• Tempdb создается заново всякий раз, когда стартует служба SQL. Поэтому файлы не требуется переносить с места на место, система сама создаст их во время старта службы;
• Пока не будет создан основной файл и файл лога — служба полностью не стартует и СУБД будет недоступна;
• Для подстраховки можно не перезапускать службу MS SQL, а полностью остановить ее, переименовать старую директорию с tempdb, и только потом запускать службу. Так будет сразу видно, что всё работает, а в случае проблем можно быстро откатить изменения;
• Если файлы большие – желательно дать учётной записи SQL право Perform Volume Maintenance Tasks (Выполнение задач по обслуживанию томов).