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

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

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

Эта статья относится к SQL Server. Хотя процесс аналогичен, для управления пространством файлов в 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 удаляются из конца файла журнала, чтобы уменьшить размер журнала до приблизительного целевого размера.

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

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

• DBCC SHRINKFILE (Transact-SQL)

• Сжатие файла

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

• Класс событий автоматического сжатия файла журнала

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

• Sys.dm_db_log_space_usage (Transact-SQL)

• Sys.database_files (Transact-SQL) (смsizemax_size. столбцы и growth файлы журнала.

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

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

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

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

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

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

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

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

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

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

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

Используйте инструкцию 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) параметров файлов и файловой группы.

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

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

• Файл сократился до размера, в котором только 25 процентов файла неиспользуемого пространства или исходного размера файла, в зависимости от того, какой размер больше.
• Сведения об изменении параметра свойства AUTO_SHRINK см. в разделе "Просмотр" или изменение свойств базы данных и параметров ALTER DATABASE SET (Transact-SQL).

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

• BACKUP (Transact-SQL)
• Устранение неполадок с полным журналом транзакций (ошибка SQL Server 9002)
• Резервное копирование журналов транзакций в архитектуре журнала транзакций SQL Server и руководстве по управлению
• Резервные копии журналов транзакций (SQL Server)
• Параметры файлов и файловой группы ALTER DATABASE (Transact-SQL)