Несмотря на наличие предыдущих заметок посвященных тюнингу MS SQL SERVER c tempDB, вынужден писать в стиле how-to перемещение файлов данных путём пересоздания.
Данный способ актуален, когда нет возможности переместить файлы из файловой группы tempDB, а простой возможен лишь на рестарт сервера.
В целом изложу некоторые полезные SQL запросы способствующие аналогичным действиям с сервером СУБД и базами данных в реальной жизни.
Задача:
переместить файлы данных tempDB на новый раздел
1. Собираем список файлов
USE master
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempDB');
На выходе получаем список текущих файлов. В примере их восемь.
tempdev F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE
templog F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE
temp2 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf ONLINE
temp3 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf ONLINE
temp4 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf ONLINE
temp5 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp5.ndf ONLINE
temp6 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp6.ndf ONLINE
temp7 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp7.ndf ONLINE
temp8 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp8.ndf ONLINE
2. Удаляем файлы БД
Используя регулярные выражения превращаем выгрузку в
ALTER DATABASE tempdb REMOVE FILE templog;
ALTER DATABASE tempdb REMOVE FILE tempdev;
ALTER DATABASE tempdb REMOVE FILE temp2;
ALTER DATABASE tempdb REMOVE FILE temp3;
ALTER DATABASE tempdb REMOVE FILE temp4;
ALTER DATABASE tempdb REMOVE FILE temp5;
ALTER DATABASE tempdb REMOVE FILE temp6;
ALTER DATABASE tempdb REMOVE FILE temp7;
ALTER DATABASE tempdb REMOVE FILE temp8;
3. Добавляем новые файлы tempDB
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf', SIZE = 128, FILEGROWTH = 128);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = templog, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf', SIZE = 128, FILEGROWTH = 128);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = temp2, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf', SIZE = 128, FILEGROWTH = 128);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = temp3, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf', SIZE = 128, FILEGROWTH = 128);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = temp4, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf', SIZE = 128, FILEGROWTH = 128);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = temp5, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp5.ndf', SIZE = 128, FILEGROWTH = 128);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = temp6, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp6.ndf', SIZE = 128, FILEGROWTH = 128);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = temp7, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp7.ndf', SIZE = 128, FILEGROWTH = 128);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = temp8, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp8.ndf', SIZE = 128, FILEGROWTH = 128);
GO
для получения данной выгрузки из Вашего вывода (см. пункт 1) прилагаю последовательность регулярных выражений:
- Заменяем " F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\" на ", FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\" где T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\ - нужная литера или путь
- Заменяем " ONLINE" на "', SIZE = 128, FILEGROWTH = 128);\n GO", где SIZE и FILEGROWTH содержат нужные значения
- Заменяем "^"- начало строки - на "ALTER DATABASE tempdb ADD FILE (NAME = "
4. Перезапускаем сервис MSSQLSERVER
5. Проверяем корректность файлов командой из пункта 1
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempDB');
На выходе получаем список текущих файлов. В примере их восемь.
tempdev T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE
templog T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE
temp2 T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf ONLINE
temp3 T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf ONLINE
temp4 T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf ONLINE
temp5 T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp5.ndf ONLINE
temp6 T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp6.ndf ONLINE
temp7 T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp7.ndf ONLINE
temp8 T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp8.ndf ONLINE