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

  • Статья

Область применения: SQL Server

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

Эта статья относится к SQL Server. Хотя и аналогично, сведения об управлении размером файлов журнала транзакций в Управляемый экземпляр SQL Azure см. в статье "Управление пространством файлов для баз данных в Управляемый экземпляр SQL Azure". Сведения о База данных SQL Azure см. в разделе "Управление пространством файлов для баз данных в База данных SQL Azure".

Общие сведения о типах дискового пространства для базы данных

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

Объем пространства базы данных Определение Комментарии
Место, занятое данными Пространство, используемое для хранения данных базы данных. Как правило, используемое пространство увеличивается (уменьшается) при операциях вставки (удаления). В некоторых случаях используемое пространство не изменяется при вставке или удалении в зависимости от объема и шаблона данных, участвующих в операции, и любого фрагментации. Например, удаление одной строки на каждой странице данных не обязательно приведет к уменьшению используемого пространства.
Выделенное пространство данных Форматированный файловый пробел, доступный для хранения данных базы данных. Объем выделенного пространства увеличивается автоматически, но никогда не уменьшается после удалений. Это поведение гарантирует, что будущие вставки быстрее, так как пробелы не должны быть переформатированы.
Выделенное, но неиспользуемое пространство данных Разница между выделенным объемом и используемым пространством данных. Это количество представляет максимальное свободное пространство, которое может восстановить файлы данных базы данных.
Максимальный размер данных Максимальное количество места для хранения данных базы данных. Объем выделенного пространства данных не может превышать максимальный объем данных.

На следующей схеме показана связь между разными типами дискового пространства для базы данных.

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

Запрос одной базы данных для сведений о пространстве файлов

Используйте следующий запрос, чтобы вернуть объем выделенного места в файле базы данных и объем неиспользуемого пространства. Единицы результатов запроса указываются в МБ.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

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

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

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

Внимание

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

Сжатие файла журнала

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

Если файл журнала заполнен, скорее всего, из-за открытых транзакций, изучите , что предотвращает усечение журнала транзакций.

Внимание

Операции сжатия не следует рассматривать как обычную операцию обслуживания. Файлы данных и журналов, увеличивающиеся из-за регулярных, повторяющихся бизнес-операций, не нуждаются в операциях сжатия. Команды сжатия влияют на производительность базы данных во время выполнения; они должны выполняться в периоды низкого использования. Не рекомендуется сжимать файлы данных, если из-за обычной рабочей нагрузки приложения файлы снова будут увеличиваться до того же выделенного размера.

Помните о потенциально отрицательном влиянии на производительность сжатия файлов базы данных; см. раздел "Обслуживание индекса после сжатия".

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

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

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

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

Дополнительные сведения об операциях сжатия см. по следующим ссылкам:

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

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

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

Обслуживание индекса после сокращения

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

Дополнительные сведения об обслуживании индексов см. в статье Оптимизация обслуживания индексов для повышения производительности запросов и снижения потребления ресурсов.

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

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

Чтобы добавить файл журнала в базу данных, используйте предложение ADD LOG FILE инструкции ALTER DATABASE. Это позволяет растут журналы.

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

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

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

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

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

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

Используйте инструкцию alter DATABASE (Transact-SQL) File и Filegroup options для управления ростом файла журнала транзакций. Обратите внимание на следующее:

Используйте параметр SIZE, чтобы изменить текущий размер файла в единицах КБ, МБ, ГБ и ТБ.

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

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

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

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

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

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

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

    • В версиях до SQL Server 2022 (16.x) журналы транзакций не могут использовать мгновенное инициализацию файлов, поэтому расширенные периоды роста журналов особенно важны.

    • Начиная с SQL Server 2022 (16.x) (все выпуски) и в База данных SQL Azure, мгновенное инициализация файлов может воспользоваться событиями роста журнала транзакций до 64 МБ. Для новых баз данных по умолчанию увеличивается размер автозавершение— 64 МБ. События автоматического увеличения файла журнала транзакций, превышающие 64 МБ, не могут воспользоваться мгновенным инициализацией файла.

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

      Версия Значения по умолчанию
      Начиная с SQL Server 2016 (13.x) Данные — 64 МБ. Файлы журналов — 64 МБ.
      Начиная с SQL Server 2005 (9.x) Данные — 1 МБ. Файлы журналов — 10 %.
      До SQL Server 2005 (9.x) Данные — 10 %. Файлы журналов — 10 %.

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

  • Увеличение большого автозараста может привести к двум проблемам:

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

  • Даже с включенным автоматическим увеличением можно получить сообщение о том, что журнал транзакций заполнен, если он не может расти достаточно быстро, чтобы удовлетворить потребности запроса. Дополнительные сведения об изменении увеличения роста см. в разделе ALTER DATABASE (Transact-SQL) File and Filegroup options.

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

Вы можете настроить автоматическое сжатие файлов журналов. Однако это не рекомендуется, а свойство базы данных auto_shrink имеет значение FALSE по умолчанию. Если параметру auto_shrink задано значение TRUE, автоматическое сжатие уменьшает размер файла, только если в нем не использовано более 25 % объема. — Файл сократился до размера, в котором только 25 процентов файла неиспользуемы или к исходному размеру файла, в зависимости от того, какой размер больше. — Сведения об изменении параметра свойства auto_shrink см. в разделе "Просмотр" или "Изменение свойств базы данных" и параметров ALTER DATABASE SET (Transact-SQL).

Связанный контент