При планировании развертывания приложений активно использующих системную временную базу данных 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
Получим примерно такой результат:
Затем, определившись с тем, на каких логических дисках будут расположены файлы БД, и какой они будут иметь размер, выполним 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');
Если всё нормально, результат должен быть примерно таким:
После этого необходимо удалить файлы tempdb.mdf и templog.ldf с их старого месторасположения.
Дополнительные источники информации:
MSDN Library- Moving System Databases
MSDN Library - How to: Move a Database Using Detach and Attach (Transact-SQL)
RSS - Записи
...Как следует из описания MSDN Library – tempdb Database, БД tempdb минимально использует процедуры логирования, и поэтому в большинстве случаев можно для файла лога транзакций и файла данных tempdb использовать один логический диск...
Вывод сделан совершенно неверный! Использование простой модели восстановления или модели с неполным протоколированием не отменяет протокол WAL и не снгижает нагрузки на дисковую подсистему. Нужно для каждого экземпляра отслеживать использование файлов дапнных и журнала - только это даёт основание для принятия решения о разнесении файлов на разные шпиндели.
Мне грешно будет спорить с DBA и поэтому, чтобы не вносить смуту, исправил абзац :)
Спасибо за оперативную поправку.
После этого необходимо УДАЛИТЬ ? файлы tempdb.mdf и templog.ldf с их старого месторасположения.(пропустил, наверное, Алексей)
Да. Удалить.
вы тогда в статье поправьте последнюю строчку)