Перемещение базы данных TEMPDB
TEMPDB представляет собой системную базу данных Microsoft SQL Server, в которой хранятся временные таблицы, созданные как самим сервером, так и пользователями. Эта база данных создается заново при каждом перезапуске Microsoft SQL Server. По умолчанию размер этой базы данных неограничен и увеличение его осуществляется при необходимости автоматически, порциями по 10% от текущего размера TEMPDB. Однако эти параметры могут быть переопределены пользователем. По умолчанию, минимальный размер этой базы данных, который устанавливается при старте Microsoft SQL Server, определяется размером системной базы данных MODEL. Очистка журнала транзакций в этой базе данных производится автоматически, при этом удаляются только неактивные записи журнала транзакций.
При работе 1С:Предприятия 8 в режиме клиент-сервер широко используются временные таблицы . Кроме того, TEMPDB используется Microsoft SQL Server при выполнении запросов, использующих операторы GROUP BY , UNION , DISTINCT и т.п.
Проблема
В процессе работы 1С:Предприятия 8 возможно значительное увеличение размера базы данных TEMPDB. Если размер диска, на котором расположена база данных TEMPDB, окажется недостаточным, работа 1С:Предприятия 8 может завершиться аварийно.
Решение
Если эта проблема проявляется регулярно, то рекомендуется переместить TEMPDB на другой диск большего размера.
Эту операцию можно выполнить следующим способом:
- определить логические имена файлов базы данных TEMPDB (колонка «NAME» результата выполнения процедуры). Для этого нужно в Query Analyzer выполнить следующую команду:
USE tempdb GO EXEC sp_helpfile GO
- изменить месторасположение файлов базы данных TEMPDB с помощью команды ALTER DATABASE . Для этого нужно в Query Analyzer выполнить следующую последовательность команд:
USE master GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'Новый_Диск:\Новый_Каталог\tempdb.mdf') GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'Новый_Диск:\Новый_Каталог\templog.ldf') GO
- Перезапустить Microsoft SQL Server.
Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.
Как в Microsoft SQL Server перенести tempdb на другой диск
Microsoft SQL Server использует tempdb при выполнении запросов и является служебной базой и хранит в себе временные оьъекты (временные таблицы, процедуры, вычисления, сортировки, объекты и т.д.). Как правило база tempdb всегда сильно нагружена и её ускорение даст нам небольшой прирост в производительности. Одна из рекомендаций — перенос базы tempdb на другой дискю Лучше всего переносить на SSD диск.
Это основная причина для переноса tempdb на другой диск и эта процедура довольно простая.
Расположение файлов базы tempdb по умолчанию
Во время установки MS SQL Server будет предложена Настройка компонента Database Engine, где в вкладке Каталоги данных мы можем выбрать каталог хранения базы данных tempdb. По умоланию база tempdb распологается в следующем каталоге:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA
Выполняем запрос на перенос базы tempdb на другой диск
Поскольку база MS SQL Server постоянно использует базу данных, базу tempdb не получится переместить сделав просто отсоединение и присоединение базы данных. Для перемещения файлов базы tempdb нам необходимо создать запрос и выполнить скрипт под фото, предварительно подставив в него свои пути нового расположения перемещаемой базы.
USE master GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\TEMP_DB\tempdb.mdf') GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\TEMP_DB\templog.ldf') GO
После выполнения запроса вам потребуется Перезапустить Microsoft SQL Server. В процессе перезапуска пользователей не выбросит из 1С �� так что жмём смелее! После перезапуска сервер самостоятельно создаст базу tempdb там, где вы указали. Старые файлы необходимо самостоятельно подчистить ��.
Перезапуск службы Microsoft SQL Server
Также помимо файлов tempdb.mdf и templog.ldf могут быть ещё несколько штук типа tempdb_mssql_1.ndf. Их таже необходимо перенести в новый каталог:
ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2, FILENAME = 'D:\TEMP_DB\tempdb_mssql_1.ndf' ) ALTER DATABASE tempdb MODIFY FILE ( NAME = temp3, FILENAME = 'D:\TEMP_DB\tempdb_mssql_2.ndf' )
Проверяем новые пути tempdb через запрос
После того, как мы перенесли все файлы в новую директорию и перезапустили службу MS SQL SERVER мы можем проверяем новые пути расположения баз данных tempdb выполнив запрос ниже:
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
В ответ мы получим таблицу с новыми путями до файлов баз данных TEMPDB
Как переместить базу данных tempdb в MS-SQL
Иногда случается, что необходимо перенести служебную базы данных tempdb в другой каталог или на другой диск, например, в случае, когда при установке эта база была установлена на медленный диск, например, на системный, как это часто случается в случае типовой установки MS-SQL сервера. Обычными графическими средствами MS-SQL Enterprise Manager ( EM ) перенос сделать нельзя, в отличие от обычной пользовательской базы, которой можно сделать « detach / attach ».
Но то, чего нельзя сделать графическими средствами, можно сделать средствами T-SQL . Для этого нужно выполнить в Query Analyzer совсем небольшой скрипт.
Use master
go
ALTER DATABASE TEMPDB
MODIFY FILE ( NAME = ‘tempdev’ , FILENAME = ‘E:worksqltempdb.mdf’ )
go
ALTER DATABASE TEMPDB
MODIFY FILE ( NAME = ‘templog’ , FILENAME = ‘E:worksqltemplog.ldf’ )
go
В приведенном примере база данных tempdb переносится в каталог « E:worksql », на диске « E: ». Но этим действия не заканчиваются. Данный скрипт только вносит изменения в служебные данные сервера. Чтобы данная операция была выполнена до конца, необходимо перезапустить MS-SQL сервер. В момент запуска база данных tempdb создастся уже в новом каталоге. После того, как сервер запустился, файлы базы tempdb по старому ее адресу можно спокойно удалить, так как сами они не удаляются.
Перенос TempDB в SQL Server 2019
Временная база данных (tempdb) в SQL Server — это системная база данных, доступная всем пользователям, подключенным к экземпляру SQL Server. В tempdb хранятся как в ременные пользовательские объекты, так и в нутренние объекты, создаваемые ядром СУБД.
Tempdb пользуется особой популярностью, поэтому для нее настоятельно рекомендуется размещать файлы данных и журнала транзакций на специально выделенных дисках, отдельно от операционной системы и других баз данных. И желательно на максимально быстром хранилище с низким временем задержки, типа SSD NVMe или Intel Optane.
Также для ускорения ввода-вывода рекомендуется создавать несколько файлов данных в зависимости от количества логических процессоров, выделенных для сервера базы данных (Database Engine). Если число логических процессоров меньше восьми, рекомендуется создать файл данных для каждого логического процессора, е сли равно или больше восьми, то используйте восемь файлов данных.
В идеале размещение баз лучше планировать заранее, но, к сожалению, это не всегда возможно. И может возникнуть ситуация, когда требуется переместить файлы базы на уже работающем сервере. Для tempdb эта процедура несложная, рассмотрим ее по пунктам.
Первым делом выделяем новый диск H, на котором будут находиться файлы TempDB. Размещать файлы базы в корне диска не рекомендуется, даже если диск целиком выделен под их хранение, поэтому создаем отдельную директорию, назовем ее TempDB. На эту директорию необходимо выдать права на чтение и изменение учетной записи, от имени которой работает служба MS SQL (SQL Server Database Engine).
Теперь нам надо выяснить текущее расположение файлов tempdb. Сделать это можно из графической оснастки, открыв в свойствах базы вкладку Files
либо с помощью вот такого sql-запроса:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
В поле Name мы получаем логическое имя файла, а в поле CurrentLocation путь к файлу. Как видите, в нашем примере имеется 8 файлов базы и один файл журнала транзакций.
Для каждого файла нужно выполнить следующий код:
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = ‘new_path\file_name’ )
• database_name — имя базы (в нашем случае tempdb);
• logical_name — логическое имя файла базы;
• new_path\file_name — новый путь и физическое имя файла.
Можно немного облегчить себе жизнь и сгенерировать код с помощью запроса (перед выполнением нажмите CTRL + T для возврата результата в виде текста):
SELECT ‘ALTER DATABASE »tempdb» MODIFY FILE ( NAME = ‘+[name]+’, FILENAME = ‘+[physical_name]+’ )’
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
В полученном запросе остается только поменять старый путь на новый. В результате получим:
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = tempdev, FILENAME = H:\TEMPDB\tempdb.mdf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = templog, FILENAME = H:\TEMPDB\templog.ldf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp2, FILENAME = H:\TEMPDB\tempdb_mssql_2.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp3, FILENAME = H:\TEMPDB\tempdb_mssql_3.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp4, FILENAME = H:\TEMPDB\tempdb_mssql_4.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp5, FILENAME = H:\TEMPDB\tempdb_mssql_5.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp6, FILENAME = H:\TEMPDB\tempdb_mssql_6.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp7, FILENAME = H:\TEMPDB\tempdb_mssql_7.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp8, FILENAME = H:\TEMPDB\tempdb_mssql_8.ndf )
Выполняем полученный запрос.
Рестартуем службу MS SQL (SQL Server Database Engine). Проверяем в настройках tempdb расположение файлов.
И затем идем в новую папку и проверяем, что в ней появились файлы.
На этом процесс переноса завершен и можно удалить файлы из старого расположения. В завершение некоторые важные моменты:
• Tempdb создается заново всякий раз, когда стартует служба SQL. Поэтому файлы не требуется переносить с места на место, система сама создаст их во время старта службы;
• Пока не будет создан основной файл и файл лога — служба полностью не стартует и СУБД будет недоступна;
• Для подстраховки можно не перезапускать службу MS SQL, а полностью остановить ее, переименовать старую директорию с tempdb, и только потом запускать службу. Так будет сразу видно, что всё работает, а в случае проблем можно быстро откатить изменения;
• Если файлы большие – желательно дать учётной записи SQL право Perform Volume Maintenance Tasks ( Выполнение задач по обслуживанию томов ).