Перенос TempDB в SQL Server 2019

Временная база данных (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_path\file_name' )

Где:

• database_name — имя базы (в нашем случае tempdb);
• logical_name — логическое имя файла базы;
• new_path\file_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:\TEMPDB\tempdb.mdf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = templog, FILENAME = H:\TEMPDB\templog.ldf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp2, FILENAME = H:\TEMPDB\tempdb_mssql_2.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp3, FILENAME = H:\TEMPDB\tempdb_mssql_3.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp4, FILENAME = H:\TEMPDB\tempdb_mssql_4.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp5, FILENAME = H:\TEMPDB\tempdb_mssql_5.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp6, FILENAME = H:\TEMPDB\tempdb_mssql_6.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp7, FILENAME = H:\TEMPDB\tempdb_mssql_7.ndf )
ALTER DATABASE 'tempdb' MODIFY FILE ( NAME = temp8, FILENAME = H:\TEMPDB\tempdb_mssql_8.ndf )

 

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

 

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

 

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

 

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

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