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

logo_sql_2012Системная база данных 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).

peremeshhenie-bd-tempdb_01

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

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

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

peremeshhenie-bd-tempdb_02

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

peremeshhenie-bd-tempdb_03

В открывшемся окне свойств перейдем на вкладку «Файлы» (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), расположенную там же, на панели задач.

peremeshhenie-bd-tempdb_05

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

peremeshhenie-bd-tempdb_06

Если теперь вызвать свойства базы данных 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

Помогла ли Вам данная статья?
Да, спасибо, все получилось.
Немного помогла.
Совсем не помогла.
Не то, что я искал(а).
Смотреть результаты
Запись опубликована в рубрике Microsoft SQL Server 2012 с метками . Добавьте в закладки постоянную ссылку.


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

  1. Денис говорит:

    На последнем скрине путь на диске с: указан, а в запросе е:

  2. Максим говорит:

    Добрый день. Подскажите пожалуйста. Есть БД в SQL в БД пишется два файла. *.LDF и *.MDF. дак вот, файл логов, тобишь *.LDF весит слишком много, 104 гига. ограничение нужно сделать в 5 гигов, но он не дает это сделать. как можно сейчас обрезать файл логов?

    • Виталий Онянов говорит:

      Вариантов 2:
      1) Сделать резервную копию журнала транзакций (trn), после чего запустить сжатие базы данных (shrink darabase).
      2) Перевести базу данных в простую модель восстановления (simple), после чего опять же запустить сжатие базы данных (shrink darabase).

      Если у вас журнал транзакций вырос до такого размера, скорее всего вы не занимаетесь его резервным копированием. В этом случае, необходимо настроить соответствующие планы обслуживания, либо перевести базу данных на простую модель восстановления (simple).

      • serg говорит:

        а такой вариант чем плох:
        Перевести базу данных в простую модель восстановления (simple), и в параметрах базы указать меньший размер *.LDF ? Без
        shrink darabase. Потом восстановить модель восстановления.

        • Виталий Онянов говорит:

          Можно сделать проще. Запустить Shrink только на файле журнала транзакций. После перевода в простую модель, сжатие произойдет за секунды.

  3. Нариман говорит:

    Спасибо за статью. Все получилось. Обычные базы таким же образом переносить?

    • Лёха говорит:

      Обычные я бы не стал переносить так же.
      Я обычно делаю detach базы, перенос файлов, attach обратно с новыми путями.

  4. Андрей говорит:

    А возможно ли изменить расположение баз с указанием сетевого размещения,например:
    \\server\d$\sqlbase\ ?

  5. Александр говорит:

    Здравствуйте!
    В моем случае tempdb разбита на 8 файлов: 1.mdf, 2-8.ndf, и лог.ldf
    В данном случае все делать, как в вашей статье?

  6. mitrich говорит:

    Здравствуйте, с базой model такой же принцип?

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *