Как сделать шринк tempdb

Добавил пользователь Алексей Ф.
Обновлено: 05.10.2024

Системная база данных tempdb служит рабочим пространством для хранения временных объектов, таких как временные таблицы, промежуточные результаты вычислений, временные хранимые процедуры, результаты буферов и сортировки, внутренние объекты, создаваемые компонентой Database Engine и пр. Кроме того, все изменения данных в базах данных, в которых используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков, а также изменения данных для таких операций, как операции с индексами в сети, множественные активные результирующие наборы (режим MARS) и триггеры AFTER также хранятся в системной базе данных tempdb. Таким образом, данная база данных используется системой довольно активно, и возможно получить значительный прирост производительности путем переноса файлов базы данных tempdb на отдельный дисковый накопитель, более быстрый SSD-диск или даже RAM-диск. О перемещении системной базы данных tempdb в MS SQL Server 2012 (справедливо для более ранних версий) и пойдет речь в данной статье.

1. Указание каталога хранения файлов базы данных tempdb во время установки MS SQL Server 2012

peremeshhenie-bd-tempdb_01

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

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

peremeshhenie-bd-tempdb_02

peremeshhenie-bd-tempdb_03


Для того чтобы изменить путь расположения необходимо выполнить запрос:

peremeshhenie-bd-tempdb_05

peremeshhenie-bd-tempdb_06

Если теперь вызвать свойства базы данных tempdb, можно увидеть, что путь расположения файлов изменился.


При старте SQL Server база данных tempdb создается каждый раз заново. Поэтому из старого каталога файлы tempdb.mdf и tempdb.ldf необходимо удалить вручную.

3. Универсальный скрипт для продвинутых

Смотрите также:

Ниже приведена пошаговая инструкция, показывающая как добавить новую базу данных в Microsoft SQLServer 2012 (в более старых редакциях, например в Microsoft SQL Server 2008 R2, набор действий аналогичен). Запускаем…

В рамках данной статьи рассмотрим системные базы данных MS SQL Server 2012. Ниже приводится их подробное описание, расположение, а также разбираются вопросы о необходимости резервного копирования системных баз данных. …

Раннее я уже писал о создании резервных копий в MS SQL Server 2012. В данной статье подробно рассмотрим процессе восстановления базы данных из имеющейся резервной копии (резервных копий) в MS SQL…

Запись опубликована в рубрике Microsoft SQL Server 2012 с метками SQL. Добавьте в закладки постоянную ссылку.

Рекомендации по настройке tempdb

Рекомендации по количеству файлов tempdb

Решение относительно количества файлов данных, подготавливаемых для базы данных tempdb в SQL Server, зависит от числа логических ядер процессора. Для экземпляров SQL Server, работающих менее чем с восемью логическими ядрами (если такие еще существуют), должно быть соотношение 1:1 между числом логических ядер и файлами данных, размещенных в tempdb. Когда имеется восемь или более логических ядер, поначалу следует подготовить восемь файлов данных. Если в tempdb отмечается конкуренция за выделенные ресурсы (она проявляется в увеличении значения параметра PAGELATCH_UP для ресурса ожидания, размещенного в tempdb), то добавляйте по четыре файла данных, пока это не прекратится.

Рекомендации по размерам файлов

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

Выбор хранилища для tempdb

Пример из практики

Недавно один из моих клиентов столкнулся с рядом проблем производительности в производственной среде, размещенной в AWS. Технических недостатков было много, и для их устранения потребовались бы месяцы работы. Однако в процессе поиска неисправностей я обратил внимание на характеристику, способную принести быстрый выигрыш: задержку tempdb. Задержка показывает, на сколько запаздывают входящие и исходящие вызовы дисковой подсистемы. В сущности, задержка — это время в миллисекундах (мс) между отправкой запроса к элементу данных на диске и получением этого элемента данных. Задержка влияет на пропускную способность, достигаемую данным классом хранилищ, в зависимости от модели или настроек, а также количества запросов в очереди на доступ к объектам на диске.

На экране 1 приводятся данные по задержке диска, полученные в ходе начальной оценки экземпляра SQL.

Начальная задержка диска SQL Server
Экран 1. Начальная задержка диска SQL Server

Мною был сделан ряд выводов относительно элементов данных, просто на основании показателей задержки, полученных в результате одного запроса к динамическому объекту управления sys.dm_io_virtual_file_stats:

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

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

Оптимизация задержки только благодаря изменению типа диска
Экран 2. Оптимизация задержки только благодаря изменению типа диска

Изменив лишь класс накопителя и установив более высокий уровень IOPS и интенсивность пакетной передачи, мы смогли улучшить показатели задержки чтения и записи более чем на 60% на томе tempdb. Это привело к общему уменьшению блокировок из-за запросов с зависимостями от операций с участием tempdb.

Готовы ли вы выделить время для анализа характеристик tempdb? Возможно, в вашем случае тоже существуют простые и быстрые пути решения проблем.

Write The F* Manual — Заметки о сетях, администрировании и вообще

Иногда база TempDB может разрастись (например после выполнения долгих транзакций над большим количеством данных), если место в TempDB уже освободилось, то для освобождения места на диске можно выполнить ее сжатие (shrink). Сделать это можно либо запросом либо в SSMS студии (сжимать нужно файл данных tempdev — tempdb.mdf).

Если операция shrink не привела к уменьшению файла БД, значит необходимо произвести сброс буферов и кешей сервера и повторить shrink :

Создаем checkpoint и сбрасываем буферы страниц и индексов на диск:

Чистим кеш хранимых процедур:

Очищаем остальные типы кешей:

Чистим кеш сессий:

После этого можно повторно запустить сжатие файла — место на диске должно освободиться (способ чаще всего срабатывает и без первого пункта — без создания checkpoint и сброса буфера страниц).

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

Мониторинг используемого пространства журнала

Для мониторинга используемого пространства журнала используйте sys.dm_db_log_space_usage. Это динамическое административное представление возвращает сведения об используемом сейчас журналом объеме пространства и сообщает, когда журнал транзакций требует усечения.

Для получения сведений о текущем размере файла журнала, его максимальном размере и параметре автоматического увеличения файла вы можете также использовать столбцы size, max_size и growth для данного файла журнала в представлении sys.database_files.

Избегайте переполнения содержащего журналы диска. Хранилище журналов должно отвечать требованиям к числу операций ввода-вывода в секунду и низкой задержке для транзакционной нагрузки.

Уменьшение размера файла журнала

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

Такие факторы, как долго выполняемые транзакции, из-за которых виртуальные файлы журналов длительное время остаются в активном состоянии, могут ограничить или вовсе не допустить возможность сжатия журнала. Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала.

Сжатие файла журнала удаляет виртуальные файлы журнала, которые не содержат частей логического журнала (то есть, неактивные виртуальные файлы журнала). При сжатии файла журнала транзакций неактивные виртуальные файлы журнала в конце удаляются, чтобы журнал уменьшился приблизительно до целевого размера.

Перед сжатием следует учесть факторы, которые могут вызвать задержку усечения журнала. Если после сжатия журнала снова потребуется дисковое пространство, размер журнала транзакций снова будет увеличиваться, что повлияет на производительность во время операций увеличения. Дополнительные сведения см. в разделе Рекомендации этой статьи.

Сжатие файла журнала (без сжатия файлов базы данных)

Мониторинг событий сжатия файла журнала

Мониторинг пространства журнала

sys.database_files (Transact-SQL) (См. столбцы size, max_size и growth файла или файлов журнала.)

Добавление или увеличение размера файла журнала

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

  • Чтобы добавить файл журнала в базу данных, используйте предложение ADD LOG FILE инструкции ALTER DATABASE . Это позволяет увеличить размер файла.
  • Чтобы увеличить размер файла журнала, используйте предложение MODIFY FILE инструкции ALTER DATABASE с указанием синтаксиса SIZE и MAXSIZE . Дополнительные сведения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп.

Дополнительные сведения см. в разделе Рекомендации этой статьи.

Оптимизация размера журнала транзакций tempdb

При перезапуске экземпляра сервера размер журнала транзакций базы данных tempdb изменяется и становится равным исходному размеру, который был до применения параметра автоматического увеличения файла. Это может понизить производительность журнала транзакций базы данных tempdb .

Этого можно избежать с помощью увеличения размера журнала транзакций базы данных tempdb после запуска или перезапуска экземпляра сервера. Дополнительные сведения см. в статье tempdb Database.

Управление увеличением размера файла журнала транзакций

Для управления увеличением файла журнала транзакций используйте инструкцию ALTER DATABASE (Transact-SQL) с параметрами для файлов и файловых групп. Следует отметить следующее.

  • Чтобы изменить текущий размер файла в КБ, МБ, ГБ и ТБ, используйте параметр SIZE .
  • Чтобы изменить шаг приращения размера, используйте параметр FILEGROWTH . Значение 0 указывает, что автоматическое приращение выключено и дополнительное пространство для файла не разрешено.
  • Чтобы установить максимальный размер файла журнала в КБ, МБ, ГБ и ТБ или задать неограниченный размер (UNLIMITED), используйте параметр MAXSIZE .

Дополнительные сведения см. в разделе Рекомендации этой статьи.

Рекомендации

Далее приведены некоторые общие рекомендации по работе с файлами журналов транзакций.

Шаг приращения автоматического увеличения журнала транзакций, задаваемый параметром FILEGROWTH , должен быть достаточно большим, чтобы с запасом соответствовать потребностям транзакций рабочих нагрузок. Во избежание слишком частых увеличений размера файла журнала следует задать достаточно большое значение шагу роста файла журнала. Чтобы подбирать оптимальный размер журнала транзакций, рекомендуем отслеживать объем журнала, занимаемый в следующих случаях.

  • Во время, необходимое для выполнения полного резервного копирования, так как резервные копии журнала создаются только после его завершения.
  • Во время, необходимое для самых продолжительных операций обслуживания индекса.
  • Во время, необходимое для выполнения наибольшего пакета в базе данных.

При активации autogrow для файлов журналов и данных с помощью параметра FILEGROWTH может быть лучше задать рост журнала через размер (size), а не процент (percentage). Это позволит более эффективно контролировать увеличение, так как процент будет характеризовать постоянно растущую величину.

  • Учитывайте, что журналы транзакций не могут использовать мгновенную инициализацию файлов, поэтому особо продолжительное время их роста имеет критическую важность.
  • Рекомендуется не устанавливать для журналов транзакций значение параметра FILEGROWTH выше 1024 МБ. Значения для параметра FILEGROWTH по умолчанию.

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

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

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

Вы можете настроить автоматическое сжатие файлов журналов. Но делать это не рекомендуется, и параметру базы данных auto_shrink по умолчанию задано значение FALSE. Если параметру auto_shrink задано значение TRUE, автоматическое сжатие уменьшает размер файла, только если в нем не использовано более 25 % объема.

Сжатие базы данных и журнала транзакций в Microsoft SQL Server

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

Сжатие базы данных и журнала транзакций

Что такое сжатие в Microsoft SQL Server?

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

Наибольший эффект от сжатия достигается тогда, когда операция сжатия выполняется после операции удаления таблиц из БД или удаления данных из таблиц.

Следует отличать процедуру сжатия журнала транзакций от процедуры усечения журнала транзакций. Сжатие — это уменьшение физического размера журнала за счет удаления неиспользуемого пространства, а усечение – это освобождение места в логическом журнале для повторного использования (т.е. образуется неиспользуемое пространство) журналом транзакций при этом размер физического файла не уменьшается.

Усечение журнала транзакций происходит автоматически:

  • В простой модели восстановления — после достижения контрольной точки, которая может возникнуть, например, после создания BACKUP базы данных, при явном выполнении инструкции CHECKPOINT, или тогда когда размер логического журнала транзакций заполняется на 70 процентов, во всех этих случаях происходит автоматическая очистка неактивной части журнала, т.е. его усечение;
  • В модели полного восстановления или в модели восстановления с неполным протоколированием — после создания резервной копии журнала при условии, что с момента создания последней резервной копии журнала была достигнута контрольная точка.

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

Также возможно размер файлов журнала транзакций слишком большой (как при простой, так и при полной модели восстановления) за счет задержки процедуры усечения, т.е. размер журнала, состоит в основном из активной части журнала, а активную часть усечь нельзя, поэтому физический размер журнала растет. На задержку процедуры усечения влияют такие факторы как: активные длительные транзакции, некоторые сценарии отображения зеркальных баз данных и журнала транзакций, некоторые сценарии при репликации транзакций и журнала транзакций, а также усечение журнала невозможно во время операций резервного копирования и восстановления данных. В данном случае Вам нужно устранить причины задержки, затем сделать усечение (т.е. например, для полной модели восстановления BACKUP журнала), а затем сжатие до приемлемых размеров.

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

Как сжать базу данных в MS SQL Server?

Сжать файлы базы данных и журнала транзакций можно и с помощью графического интерфейса Management Studio и с помощью инструкций Transact-SQL: DBCC SHRINKDATABASE и DBCC SHRINKFILE. Также возможно настроить базу данных на автоматическое сжатие путем выставления параметра БД AUTO_SHRINK в значение ON.

Примечание! Сжатие базы данных я буду рассматривать на примере Microsoft SQL Server 2016 Express.

Сжимаем базу данных с помощью среды Management Studio

Скриншот 1

Скриншот 2

Через некоторое время, в зависимости от размера базы данных, сжатие будет завершено.

Сжимаем базу данных с помощью инструкций SHRINKDATABASE и SHRINKFILE

В MS SQL Server для выполнения сжатия файлов базы данных и журнала транзакций существуют две инструкции SHRINKDATABASE и SHRINKFILE.

  • DBCC SHRINKDATABASE – это команда для сжатия базы данных;
  • DBCC SHRINKFILE – с помощью данной команды можно выполнить сжатие некоторых файлов базы данных (например, только журнала транзакций).

Для того чтобы выполнить сжатие БД (например, TestBase) точно также как мы это сделали чуть ранее в Management Studio, выполните следующую инструкцию.

SHRINKDATABASE имеет следующие параметры:

Синтаксис SHRINKDATABASE

Для того чтобы сжать только журнал транзакций можно использовать инструкцию SHRINKFILE, например.

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

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

SHRINKFILE также имеет параметры NOTRUNCATE и TRUNCATEONLY.

Синтаксис SHRINKFILE

Рекомендации и важные моменты при сжатии базы данных

  • Операция сжатия базы данных может вызвать фрагментацию индексов и замедлить работу БД. Поэтому слишком часто не рекомендуется выполнять сжатие базы данных;
  • Сжимать БД лучше до операции перестроения индексов, т.е. после сжатия запустите процедуру перестроения индексов;
  • Параметр базы данных AUTO_SHRINK (автоматическое сжатие) лучше не выставлять в значение ON, а оставлять по умолчанию, т.е. в OFF, если конечно у Вас нет на это достаточно серьезных оснований;
  • Инструкция SHRINKDATABASE не позволяет уменьшить размер базы данных до размера, который меньше начального, т.е. минимального. Однако инструкция SHRINKFILE сделать это может (вторым параметром указываем размер меньше минимального). Минимальный размер базы данных — это размер, который указан при создании базы данных или явно установленный операцией изменения размера БД, такой как DBCC SHRINKFILE или ALTER DATABASE. Например, если база данных была создана с размером 10 мегабайт, потом увеличилась до 100 мегабайт, ее можно сжать с помощью SHRINKDATABASE только до начальных 10 мегабайт, даже если все данные были удалены из базы данных;
  • Сжимать файлы базы данных и журнала транзакций нельзя, когда идет процесс их резервирования. И наоборот, создавать резервные копии базы и журнала транзакций нельзя пока идет процесс их сжатия;
  • Выполнение инструкции DBCC SHRINKDATABASE без указания параметра NOTRUNCATE или TRUNCATEONLY равносильно выполнению инструкции DBCC SHRINKDATABASE с параметром NOTRUNCATE после выполнения инструкции DBCC SHRINKDATABASE с параметром TRUNCATEONLY;
  • В процессе сжатия базы данных пользователи могут работать в ней (т.е. переводить БД в однопользовательский режим не нужно);
  • В любой момент времени Вы можете прервать процесс выполнения операций SHRINKDATABASE и SHRINKFILE, при этом вся выполненная работа сохраняется;
  • Перед запуском процедуры сжатия проверьте, есть ли свободное пространство для удаления в файлах базы данных, т.е. можно ли вообще сжать файлы, выполнив следующий запрос (он покажет в мегабайтах, на сколько Вы можете уменьшить файлы БД).

Скриншот 3


Существует ситуация, когда LDF файл занимает много гигабайт места (файл с постфиксом _log), и его необходимо уменьшить.

Это происходит когда база в SQL находится в режиме Full, т.е. с фиксацией всех произведенных транзакций. Модель Full позволяет восстановить состояние базы SQL на любое время, в то время, как модель Simple не позволяет этого сделать, а только восстановить базу из бэкапа. Смысл модели Full в том, что в журнал транзакций LDF записываются ВСЕ транзакции и там остаются, ну до определенного времени, например, до операции shrink. Таким образом SQL последовательным откатом транзакций назад может восстановить состояние базы на любой момент времени периода записанных в LDF транзакций.

Переход в режим Simple приведет к тому, что в файле LDF будут находиться только незавершенные транзакции, что уменьшит размер этого файла.

Первое что нужно сделать, перевести базу в модель восстановления Simple (при этом настроить механизм создания беэкапов базы, если этого до сих пор не сделано). Эту операцию можно делать "на ходу".
Однако, перевод в simple автоматически не уменьшает размер файла транзакций. Можно, провести операцию shrink (сжатие базы) сразу, но лучше сначала сделать полный бэкап базы средствами SQL (есть там в SQL-е по этому поводу одна маленькая хитрость), а потом сделать shrink как файлу базы MDF, так и файлу журнала транзакций LDF. Размер базы тоже должен уменьшиться, но не на много, а, вот, размер файла транзакций LDF, если было сделано все правильно, должен стать практически нулевым (в случае, когда в этом момент в базе нет активной работы пользователей).
Операции бэкапа средствами SQL, и shrink-а, можно делать не выгоняя пользователей, эти операции могут, разве что, сказаться на производительности. Настоятельная рекомендация сделать резервные копии перед началом этой операции.

Для выполнения операции по очистке логов необходимо запустить восстановление:

Если не охота мучиться с запросами можно сделать через GUI: правой кнопкой на базе -> задачи -> шринк -> файлы -> выбираем лог (там будет видно на сколько процентов можно уменьшить).
Иногда, если лог большой — например 50 Гб, то уменьшать (шринкать) его надо 2 раза — с первого раза уменьшается, но не полностью.

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