Перемещение базы данных tempdb в MS SQL Server

Системная база данных 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_Temp\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQL_Temp\templog.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