Ms sql tempdb удалить дополнительные файлы

Обновлено: 08.07.2024

Обсуждаемая в статье проблема актуальна для клиент-серверных баз, размещенных на СУБД MS SQL Server. Она связана с настройками размещения системной базы tempdb, которые получаются при установке SQL-сервера с параметрами «по умолчанию». Подобные проблемы вполне возможны при работе 1С с другими СУБД (у меня не было возможности это проверить).

Описание проблемы:

И так «задача» – положить SQL-сервер с помощью обычной консоли запросов 1С. И решается она достаточно просто и непринужденно. Достаточно выбрать в запросе декартовое произведение какой-нибудь большой таблицы саму на себя (так сказать взять «декартов квадрат») и уложить результат во временную таблицу.

Самый подходящий кандидат для этого – таблица регистра сведений «Адресный классификатор». Но подойдет и любая другая, достаточно большая таблица. Если размер таблицы будет недостаточным, можно вместо «квадрата» выбрать «куб» или более высокую «декартову степень» таблицы.

И так проведем эксперимент:

Для его проведения нужно выполнить следующие «системные» требования:

Стандартно-установленный MS SQL Server (все равно, какой версии, на 2005-том сервере это проявляется);
Сервер 1С:Предприятие (все равно какой, на той же машине или нет – не важно);
Так же для «фокуса» нужно, что бы на системном разделе SQL-сервера было не слишком много свободного места (30-40 гигабайт, но не сотни). Как правило, так часто и бывает. Чем больше свободного места, тем труднее будет получить «результат». Причем труднее не в смысле, что этого трудно добиться, а в смысле, что этого придется дольше ждать;

Убедившись, что указанные выше требования удовлетворены, выполним следующую последовательность действий:

Создадим клиент-серверную информационную базу;
Загрузим туда выгрузку демонстрационной базы какой-нибудь типовой конфигурации (какой – сильно не важно);
Заполним адресный классификатор с диска ИТС, выбрав все регионы (примерно 112000 записей);
И наберем в обработке «Консоль запросов» следующий запрос

ВЫБРАТЬ
ДекартовКвадрат.КодРегионаВКоде КАК КодРегионаВКоде ,
ДекартовКвадрат.Код КАК Код ,
ДекартовКвадрат.ТипАдресногоЭлемента КАК ТипАдресногоЭлемента ,
ДекартовКвадрат.КодРайонаВКоде КАК КодРайонаВКоде ,
ДекартовКвадрат.КодГородаВКоде КАК КодГородаВКоде ,
ДекартовКвадрат.КодНаселенногоПунктаВКоде КАК КодНаселенногоПунктаВКоде ,
ДекартовКвадрат.КодУлицыВКоде КАК КодУлицыВКоде ,
ДекартовКвадрат.Наименование ,
ДекартовКвадрат.Сокращение ,
ДекартовКвадрат.Индекс ,
ДекартовКвадрат.АльтернативныеНазвания ,
ДекартовКвадрат.КодРегионаВКоде1 КАК КодРегионаВКоде1 ,
ДекартовКвадрат.Код1 КАК Код1 ,
ДекартовКвадрат.ТипАдресногоЭлемента1 КАК ТипАдресногоЭлемента1 ,
ДекартовКвадрат.КодРайонаВКоде1 КАК КодРайонаВКоде1 ,
ДекартовКвадрат.КодГородаВКоде1 КАК КодГородаВКоде1 ,
ДекартовКвадрат.КодНаселенногоПунктаВКоде1 КАК КодНаселенногоПунктаВКоде1 ,
ДекартовКвадрат.КодУлицыВКоде1 КАК КодУлицыВКоде1 ,
ДекартовКвадрат.Наименование1 ,
ДекартовКвадрат.Сокращение1 ,
ДекартовКвадрат.Индекс1 ,
ДекартовКвадрат.АльтернативныеНазвания1
ПОМЕСТИТЬ тзДекартовКвадрат
ИЗ
(ВЫБРАТЬ
АдресныйКлассификатор.КодРегионаВКоде КАК КодРегионаВКоде ,
АдресныйКлассификатор.Код КАК Код ,
АдресныйКлассификатор.ТипАдресногоЭлемента КАК ТипАдресногоЭлемента ,
АдресныйКлассификатор.КодРайонаВКоде КАК КодРайонаВКоде ,
АдресныйКлассификатор.КодГородаВКоде КАК КодГородаВКоде ,
АдресныйКлассификатор.КодНаселенногоПунктаВКоде КАК КодНаселенногоПунктаВКоде ,
АдресныйКлассификатор.КодУлицыВКоде КАК КодУлицыВКоде ,
АдресныйКлассификатор.Наименование КАК Наименование ,
АдресныйКлассификатор.Сокращение КАК Сокращение ,
АдресныйКлассификатор.Индекс КАК Индекс ,
АдресныйКлассификатор.АльтернативныеНазвания КАК АльтернативныеНазвания ,
АдресныйКлассификатор1.КодРегионаВКоде КАК КодРегионаВКоде1 ,
АдресныйКлассификатор1.Код КАК Код1 ,
АдресныйКлассификатор1.ТипАдресногоЭлемента КАК ТипАдресногоЭлемента1 ,
АдресныйКлассификатор1.КодРайонаВКоде КАК КодРайонаВКоде1 ,
АдресныйКлассификатор1.КодГородаВКоде КАК КодГородаВКоде1 ,
АдресныйКлассификатор1.КодНаселенногоПунктаВКоде КАК КодНаселенногоПунктаВКоде1 ,
АдресныйКлассификатор1.КодУлицыВКоде КАК КодУлицыВКоде1 ,
АдресныйКлассификатор1.Наименование КАК Наименование1 ,
АдресныйКлассификатор1.Сокращение КАК Сокращение1 ,
АдресныйКлассификатор1.Индекс КАК Индекс1 ,
АдресныйКлассификатор1.АльтернативныеНазвания КАК АльтернативныеНазвания1
ИЗ
РегистрСведений.АдресныйКлассификатор КАК АдресныйКлассификатор ,
РегистрСведений.АдресныйКлассификатор КАК АдресныйКлассификатор1 ) КАК ДекартовКвадрат
ИНДЕКСИРОВАТЬ ПО
КодРегионаВКоде ,
Код ,
ТипАдресногоЭлемента ,
КодРайонаВКоде ,
КодГородаВКоде ,
КодНаселенногоПунктаВКоде ,
КодУлицыВКоде ,
КодРегионаВКоде1 ,
Код1 ,
ТипАдресногоЭлемента1 ,
КодРайонаВКоде1 ,
КодГородаВКоде1 ,
КодНаселенногоПунктаВКоде1 ,
КодУлицыВКоде1

Результаты и "последствия" эксперимента:

После нажатия на кнопку «Выполнить» нам придется запастись терпением. Для файловой базы все закончится довольно быстро и не очень интересно – клиент 1С «подавится» памятью и «лопнет» без глобальных последствий.

Для клиент-серверной базы все будет несколько иначе. Через достаточно большое время операционная система на сервере начнет жаловаться, что «не достаточно места на диске», а возмущенные пользователи (если сервер вдруг окажется рабочим) – начнут звонить и спрашивать: «почему тормозит и вылетает 1С».

Еще более серьезными будут последствия, если сервер является «трехголовым Змеем-Горынычем», в одном лице – SQL сервер, сервер 1С и терминальный сервер. Тогда произойдет «полный коллапс».

В такой ситуации приходится срочно предпринимать чрезвычайные меры: срочно что-то освобождать на системном разделе, а также перезапускать SQL сервер (чтобы усечь базу tempdb) и сервер 1С:Предприятия (на всякий случай).

Причины проблемы:

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

При показанных выше настройках, база tempdb может легко «съесть» все свободное дисковое пространство на системном разделе сервера и поставить тем самым операционную систему р… в неработоспособное состояние.

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

Лично на моем опыте такой «фокус» удавался два-три раза. После чего мы решили что-то с этим сделать. Так какие можно предпринять меры, чтобы избежать описанных выше неприятностей?

Варианты решения проблемы:

Окончательного (раз и навсегда!) решения этой проблемы, конечно, нет. Но есть способы сделать такой сценарий развития событий менее вероятным:

А) Можно расширить системный раздел сервера. Не всегда это можно сделать (тем более «на горячую»). И это не панацея – у меня в ходе эксперимента на тестовом сервере (не самом хилом, но не самом крутом) свободное пространство размером 80 гигабайт съелось где-то за 40 минут. И к тому же сейчас много свободного места, а завтра его может стать не так много.

Б) Можно еще установить SQL сервер не на системный раздел. Но говорят, это не слишком оптимально по производительности. Есть и другой веский довод «против» - не переустанавливать же «боевой сервак»!

В) Можно переместить файлы базы tempdb с помощью команды ALTER DATABASE (способ указан AKV77 в комментарии (5) ):

Для того чтобы указанные выше изменения настроек базы tempdb вступили в силу потребуется перезапусть SQL сервер.
Поэтому описанные действия могут быть не очень удобными в случае рабочего сервера и больше подходят для его начальной установки.

Г) Еще один вариант решения проблемы (пожалуй, самый взвешенный, его можно сделать без перезапуска сервера) - это оптимизация размещения системной базы tempdb без ее физического перемещения с системного раздела на другие диски:

После проведения такой оптимизации размещения базы tempdb мне уже ни разу не удавалось «завалить» сервер описанным образом. В худшем случае возникали не требующие чрезвычайных мер «тормоза», которые решались личным «харакири» через консоль кластеров серверов 1С.

Работа с базой данных 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, как правило, является невозможность автоматической очистки журнала транзакций и повторного использования свободного пространства в базе данных TEMPDB из-за наличия активных транзакций, использующих объекты этой базы данных. Основные причины, вызывающие длительную блокировку работы этих механизмов базы данных TEMPDB, заключаются в следующем:

"Большие" транзакции, использующие TEMPDB , выполнение которых занимает большой промежуток времени.
Сетевые ошибки, из-за которых Microsoft SQL Server не получает уведомление о потере сетевого подключения. Если клиентская рабочая станция зависает, перезагружается, или будет выключена во время исполнения определяемой пользователем транзакции, то Microsoft SQL Server будет считать, что клиент продолжает работу, и выполняющаяся клиентская транзакция будет по-прежнему активна. Время обнаружения подобной ситуации зависит от настроек параметров сетевого протокола, используемого Windows . Например, при использовании протокола TCP/IP это время составляет 2 часа.

Если для завершения активных транзакций не хватает места в базе данных, Microsoft SQL Server автоматически увеличивает размер TEMPDB на величину, заданную в параметрах этой базы данных (по умолчанию - 10% от текущего размера).

Решение

Уменьшить размер базы данных TEMPDB до требуемой величины можно следующими способами:

В этом случае размер базы данных TEMPDB будет установлен по умолчанию или, если эта величина переопределена пользователем, размер будет установлен в соответствии с заданными параметрами.

С помощью команды DBCC SHRINKDATABASE можно уменьшить размер всей базы данных. Для этого нужно в Query Analyzer выполнить следующую команду: С помощью команды DBCC SHRINKFILE можно уменьшить размер отдельных файлов базы данных. Для этого нужно в Query Analyzer выполнить следующую последовательность команд:

Копировать в буфер обмена
DBCC SHRINKFILE ( Имя_Файла_Данных, Желаемый_Размер_Файла_Данных )
go
DBCC SHRINKFILE ( Имя_Файла_Журнала_Транзакций, Желаемый_Размер_Файла_Журнала_Транзакций )
go

Следует отметить, что эти команды рекомендуется выполнять в период наименьшей активности пользователей, и для их выполнения необходимо обладать правами администратора.

Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.

Системная база данных tempdb активно используется базами данных 1С:Предприятие 8.3 для хранения временных таблиц, промежуточных расчетов, версий строк при использовании режима версионирования и прочих временных данных. То есть для задач 1С:Предприятие интенсивность обращений к базе tempdb находится на высоком уровне, поэтому нужно подумать о размещении этой базы на выделенном быстром дисковом устройстве.Подходящими кандидатами на роль диска под tempdb будут выделенные быстрые дисковые RAID-группы уровня RAID1, выделенные накопители SSD или вообще RAM-диск.

В большинстве сценариев рекомендуется разбивать базу tempdb на несколько файлов данных с одинаковом начальным размером (Initial size) от 1GB и больше и увеличенным показателем прироста, например, в 512MB.

При определении количества файлов можно руководствоваться принципом: количество процессорных ядер = количество файлов данных tempdb, но при этом стоит помнить о том, что использование более 8 файлов (даже при количестве ядер более 8) далеко не всегда может иметь положительный эффект. Возможно по этой причине в инсталляторе SQL Server 2016 даже при большом количестве процессорных ядер по умолчанию предлагается 8 файлов tempdb.

Относительно 1С:Предприятие 8.3 можно встретить рекомендацию о том, что общий размер Initial size для всех файлов БД tempdb должен быть от 25% до 40% от размера рабочей БД 1С:Предприятие.

Саму процедуру переноса файлов tempdb на другой диск мы рассматривали ранее в заметке SQL Server 2008 - Перенос файлов БД tempdb на отдельный диск. Эта процедура может использоваться и на новых версиях СУБД, вплоть до SQL Server 2016.

Рассмотрим частный пример распределения файлов tempdb по разным дисковым томам, имеющим разные показатели производительности. В нашем примере имеется два тома NTFS:

R: менее производительный, но больший по объёму диск

Часть файлов данных в файловой группе tempdb, а также файл лога размещены на быстром диске. Файлы данных, расположенные на быстром диске установлены фиксированного размера без возможности авторасширения (исключением здесь является только файл лога). Другая часть файлов данных размещена на менее производительном дисковом томе, но при этом для файлов включено авторасширение.

В результате такой конфигурации, файлы tempdb в нашем случае будут распределены по дисковым томам следующим образом: 4 файла данных и лог tempdb окажутся на быстром томе T:

Другие 4 файла данных tempdb окажутся на менее производительном дисковом томе R:

В случае если в ходе работы экземпляра SQL Server потребуется дополнительное расширение ёмкости tempdb, то файлы начнут прирастать на меньшем по производительности, но большем по объёму дисковом томе R.

К операциям, которые могут вызвать бурный рост tempdb при работе БД 1С:Предприятие 8.3 можно отнести, например, регламентные процедуры с конфигурацией 1С, выполняемые из конфигуратора (обновление конфигурации, перерасчёт итогов и т.п.). Кроме того, активный рост tempdb может вызвать построение в 1С каких-то тяжёлых отчётов с большим количеством данных и за длительные периоды при условии, что код отчётов неоптимален или вообще содержит ошибки. В практической среде при размере БД около 170GB во время построения подобного отчёта мы наблюдали рост tempdb до 350GB. Учитывая эти моменты стоит подумать о полной изоляции файлов tempdb на выделенных дисковых томах, чтобы их возможный бурный рост не смог нарушить функционирования других БД SQL Server.

Используемый в нашем примере дисковый том T: представляет собой RAM-диск, подключенный к серверу SQL Server по методике, описанной в отдельной статье нашего Блога : Организуем RAM-диск для кластера Windows Server с помощью Linux-IO FC Target

В нашей производственной БД у нас есть только 1 файл tempdb, и он взлетел до 180 ГБ.

для изменения размера первичного файла, а затем выполните дополнительные действия:

для добавления дополнительных файлов tempdb. Я столкнулся с ошибкой на диске, поскольку первичный не уменьшался до 30 гб, и операция была остановлена на середине.

Я теперь остался с одним дополнительным файлом tempdb, который составляет 30 ГБ, а основной - 180 ГБ, и я не могу работать, чтобы избавиться от него.

Я попытался запустить

Не удалось найти файл «tempdev2» для базы данных «tempdb» в sys.database_files.
Файл либо не существует, либо был удален.

Я чувствую, что эта ошибка связана с тем, что операция не была успешно завершена. (У меня есть коррупция?)

И когда я пытаюсь запустить

Я получаю следующую ошибку:

Файл «M: \ SQLData \ tempdb2.ndf» был изменен в системном каталоге. Новый путь будет использоваться при следующем запуске базы данных.
Msg 5042, уровень 16, состояние 1, строка 35
Файл «tempdev2» не может быть удален, потому что он не пуст.

The sys.database_files looks like this

Results from tempdb.sys.all_objects

Редактировать: Я смог разрешить повреждение имени файла, которое не было найдено, которое я получал при попытке запуска EMPTYFILE Shrink. Я использовал идентификатор файла вместо логического имени.

Другой метод, который я нашел, что работала, переименовала файл.

Итак, ваша текущая ситуация - это полный диск, где у вас есть два TempDB, которые вы хотите изменить размер старого, и у вновь созданного есть трудности с удалением?

Существуют ограничения на использование команды DBCC SHRINKDATABASE на база данных tempdb. Размер цели для файлов данных и журналов не может быть меньше размера, указанного при создании базы данных или меньше последнего размера, который был явно задан с помощью операция изменения размера файла, такая как ALTER DATABASE, которая использует MODIFY FILE или команду. Другое ограничение BCC SHRINKDATABASE - это вычисление параметра target_percentage и его зависимость от текущего пространства, которое используется.

Это должно установить ваш файл на

54GB, который должен быть безопасным размером, учитывая, что я предполагаю, что ваш размер или модель составляет 30 ГБ. Отрегулируйте% соответственно.

Теперь для второго файла попробуйте:

Если проблема не устранена, вам придется перезапустить SQL Server, чтобы удалить файл, когда используется TempDB.

В последней заметке вы набрали это:

ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev2', FILENAME = 'M: \ SQLData ** tempdb3 **. Ndf', Size = 30GB);

Снимок экрана показывает tempdb2 , убедитесь, что вы точно указали все имена и дважды проверьте их. На всякий случай есть ошибка, и это вызывает ваши головные боли.

Есть случаи, когда

может помочь облегчить боль и позволить вам сжать файл.

Если у вас все еще есть проблемы, посмотрите, что находится в вашем tempdb:

Это позволит вам увидеть размер файлов tempdb.

С вашего поста мы видим, что у вас есть файлы, используемые в TempDB, и свободное пространство. SQLSunday от Daniel Hutmacher имеет хорошую ссылку на сокращение вашего БД с помощью дополнительных методов, которые были похожи на Озар, я бы рекомендовал также попробовать это с осторожностью.

Читайте также: