angle-left

и снова tempDB - перенос и полезные скрипты

Несмотря на наличие предыдущих заметок посвященных тюнингу 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) прилагаю последовательность регулярных выражений:

  1. Заменяем "    F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\" на ", FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\" где T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\ - нужная литера или путь
  2. Заменяем "    ONLINE" на  "', SIZE = 128, FILEGROWTH = 128);\n GO", где SIZE и FILEGROWTH содержат нужные значения
  3. Заменяем  "^"- начало строки -  на "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