SQL Server 2008 - Перенос файлов БД tempdb на отдельный диск

imageПри планировании развертывания приложений активно использующих системную временную базу данных SQL Server tempdb, необходимо учитывать тот факт, что по умолчанию файлы этой БД расположены на системном диске. Соответственно для того чтобы избежать в будущем возможных проблем, связанных с переполнением системного диска из-за увеличения размера файлов tempdb, нам по возможности нужно выделить для файлов этой БД отдельное логическое дисковое пространство.

При создании отдельного логического дискового пространства нужно понимать то, что оно не должно быть использовано для работы каких либо других приложений и его размер должен быть рассчитан исходя из технических требований приложения, использующего наш SQL Server.

Следует учитывать, что для достижения максимальной производительности в работе tempdb может потребоваться разнесение файла данных и лога транзакций БД tempdb. Также следует отметить то, что для поднятия производительности хорошо нагруженных систем в  документе MSDN Library - Optimizing tempdb Performance присутствует рекомендация создавать отдельные группы файлов tempdb на разных дисках для каждого ядра серверного процессора.

По умолчанию БД tempdb настроена на авто-расширение (Autogrow) и при каждой перезагрузке SQL Server пересоздаёт файлы этой БД с минимальным размером инициализации (Initial Size). Опираясь на рекомендации вышеуказанного документа, мы увеличим размер инициализации файлов tempdb таким образом, чтобы свести к минимуму затраты системных ресурсов на операции авто-расширения.

Для того чтобы определить где в текущий момент физически располагаются файлы БД tempdb, откроем  SQL Server Management Studio и выполним SQL запрос:

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

Получим примерно такой результат:

image

 

 

Затем, определившись с тем, на каких логических дисках будут расположены файлы БД, и какой они будут иметь размер, выполним SQL запрос:

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'H:TempDB_Datatempdb.mdf' , SIZE = 10240);

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'I:TempDB_Logtemplog.ldf' , SIZE = 3072);

GO

Перед выполнением этого запроса необходимо понимать, что если мы указываем размер файлов, то SQL Server сразу попытается увеличить существующие в данный момент файлы, и их размер окажется больше места, доступного на текущем диске места диске,- мы получим сообщение об ошибке.

После успешного выполнения запроса - перезапустим службу SQL Server и убедимся что наши файлы расположены там где мы хотим, выполнив SQL запрос:

SELECT name, physical_name AS CurrentLocation, size, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

 

Если всё нормально, результат должен быть примерно таким:

image

 

 

 

После этого необходимо удалить файлы tempdb.mdf и templog.ldf с их старого месторасположения.

Дополнительные источники информации:

MSDN Library- Moving System Databases

MSDN Library - How to: Move a Database Using Detach and Attach (Transact-SQL)

KB224071 - How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

Всего комментариев: 5 Комментировать

  1. ...Как следует из описания MSDN Library – tempdb Database, БД tempdb минимально использует процедуры логирования, и поэтому в большинстве случаев можно для файла лога транзакций и файла данных tempdb использовать один логический диск...

    Вывод сделан совершенно неверный! Использование простой модели восстановления или модели с неполным протоколированием не отменяет протокол WAL и не снгижает нагрузки на дисковую подсистему. Нужно для каждого экземпляра отслеживать использование файлов дапнных и журнала - только это даёт основание для принятия решения о разнесении файлов на разные шпиндели.

  2. Алексей Максимов /

    Мне грешно будет спорить с DBA и поэтому, чтобы не вносить смуту, исправил абзац :)
    Спасибо за оперативную поправку.

  3. razbornovd /

    После этого необходимо УДАЛИТЬ ? файлы tempdb.mdf и templog.ldf с их старого месторасположения.(пропустил, наверное, Алексей)

    1. Алексей Максимов /

      Да. Удалить.

  4. razbornovd /

    вы тогда в статье поправьте последнюю строчку)

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