Управление размером файла журнала транзакций
В этой статье рассказывается о мониторинге размера журнала транзакций 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 по умолчанию.
При небольшом шаге приращения может формироваться слишком много виртуальных файлов журнала малого размера и снижаться производительность. Чтобы определить оптимальное распределение виртуальных файлов журнала для текущего размера журнала транзакций всех баз данных в определенном экземпляре, а также требуемые приращения для достижения нужного размера, см. следующий скрипт.
При большом шаге приращения может формироваться слишком мало крупных виртуальных файлов журнала, что также повлияет на производительность. Чтобы определить оптимальное распределение виртуальных файлов журнала для текущего размера журнала транзакций всех баз данных в определенном экземпляре, а также требуемые приращения для достижения нужного размера, см. следующий скрипт.
Даже если включено автоматическое увеличение, вы можете получить сообщение, что журнал транзакций заполнен, если его размер не может достаточно быстро увеличиваться под нужды вашего запроса. Дополнительные сведения об изменении шага приращения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп
Наличие множества файлов журнала в базе данных не способствует повышению производительности, так как файлы журнала транзакций не используют пропорциональное заполнение, как файлы данных в одной файловой группе.
Вы можете настроить автоматическое сжатие файлов журналов. Но делать это не рекомендуется, и параметру базы данных auto_shrink по умолчанию задано значение FALSE. Если параметру auto_shrink задано значение TRUE, автоматическое сжатие уменьшает размер файла, только если в нем не использовано более 25 % объема.