Управление размером файла журнала транзакцийManage the size of the transaction log file

• 01/05/2018
• Чтение занимает 5 мин
• • M
  • o
  • O
  • S

Применимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) Применимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions)

В этой статье рассказывается о мониторинге размера журнала транзакций SQL ServerSQL Server, сжатии журнала транзакций, добавлении или увеличении файла журнала транзакций, оптимизации скорости роста журнала транзакций tempdb, а также об управлении размером файла журнала транзакций.This topic covers how to monitor SQL ServerSQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.

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

Для мониторинга используемого пространства журнала используйте sys.dm_db_log_space_usage.Monitor log space use by using sys.dm_db_log_space_usage. Это динамическое административное представление возвращает сведения об используемом сейчас журналом объеме пространства и сообщает, когда журнал транзакций требует усечения.This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.

Для получения сведений о текущем размере файла журнала, его максимальном размере и параметре автоматического увеличения файла вы можете также использовать столбцы size, max_size и growth для данного файла журнала в представлении sys.database_files.For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.

Уменьшение размера файла журналаShrink log file size

Для уменьшения реального размера физического файла журнала необходимо выполнить его сжатие.To reduce the physical size of a physical log file, you must shrink the log file. Это полезно, если файл журнала транзакций содержит неиспользованное пространство.This is useful when you know that a transaction log file contains unused space. Вы можете сжать файл журнала, только если база данных активна и хотя бы один виртуальный файл журнала (VLF) свободен.You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free. В ряде случаев сжатие невозможно до тех пор, пока не выполнена следующая операция усечения журнала.In some cases, shrinking the log may not be possible until after the next log truncation.

Сжатие файла журнала удаляет виртуальные файлы журнала, которые не содержат частей логического журнала (то есть, неактивные виртуальные файлы журнала).Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). При сжатии файла журнала транзакций неактивные виртуальные файлы журнала в конце удаляются, чтобы журнал уменьшился приблизительно до целевого размера.When a transaction log file is shrunk, inactive VLFs are removed from the end of the log file to reduce the log to approximately the target size.

Сжатие файла журнала (без сжатия файлов базы данных)Shrink a log file (without shrinking database files)

• DBCC SHRINKFILE (Transact-SQL)DBCC SHRINKFILE (Transact-SQL)

• Сжатие файлаShrink a File

Мониторинг событий сжатия файла журналаMonitor log-file shrink events

• Log File Auto Shrink Event Class.Log File Auto Shrink Event Class.

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

• sys.dm_db_log_space_usage (Transact-SQL)sys.dm_db_log_space_usage (Transact-SQL)

• sys.database_files (Transact-SQL) (См. столбцы size, max_size и growth файла или файлов журнала.)sys.database_files (Transact-SQL) (See the size, max_size, and growth columns for the log file or files.)

Добавление или увеличение размера файла журналаAdd or enlarge a log file

Вы можете выделить дополнительное место на диске, увеличив существующий файл журнала (если для этого достаточно места на диске) либо добавив файл журнала в базу данных, как правило, на другом диске.You can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk. До тех пор, пока в журнале и на содержащем его дисковом томе достаточно свободного места, будет достаточного одного файла журнала транзакций.One transaction log file is sufficient unless log space is running out, and disk space is also running out on the volume that holds the log file.

• Чтобы добавить файл журнала в базу данных, используйте предложение ADD LOG FILE инструкции ALTER DATABASE.To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. Это позволяет увеличить размер файла.Adding a log file allows the log to grow.
• Чтобы увеличить размер файла журнала, используйте предложение MODIFY FILE инструкции ALTER DATABASE с указанием синтаксиса SIZE и MAXSIZE.To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. Дополнительные сведения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп.For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup options.

Дополнительные сведения см. в разделе Рекомендации этой статьи.For more information, see the Recommendations in this topic.

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

При перезапуске экземпляра сервера размер журнала транзакций базы данных tempdb изменяется и становится равным исходному размеру, который был до применения параметра автоматического увеличения файла.Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. Это может понизить производительность журнала транзакций базы данных tempdb .This can reduce the performance of the tempdb transaction log.

Этого можно избежать с помощью увеличения размера журнала транзакций базы данных tempdb после запуска или перезапуска экземпляра сервера.You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. Дополнительные сведения см. в статье tempdb Database.For more information, see tempdb Database.

Управление увеличением размера файла журнала транзакцийControl transaction log file growth

Для управления увеличением файла журнала транзакций используйте инструкцию ALTER DATABASE (Transact-SQL) с параметрами для файлов и файловых групп.Use the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. Следует отметить следующее.Note the following:

• Чтобы изменить текущий размер файла в КБ, МБ, ГБ и ТБ, используйте параметр SIZE.To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
• Чтобы изменить шаг приращения размера, используйте параметр FILEGROWTH.To change the growth increment, use the FILEGROWTH option. Значение 0 указывает, что автоматическое приращение выключено и дополнительное пространство для файла не разрешено.A value of 0 indicates that automatic growth is set to off and no additional space is permitted.
• Чтобы установить максимальный размер файла журнала в КБ, МБ, ГБ и ТБ или задать неограниченный размер (UNLIMITED), используйте параметр MAXSIZE.To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.

Дополнительные сведения см. в разделе Рекомендации этой статьи.For more information, see the Recommendations in this topic.

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

Далее приведены некоторые общие рекомендации по работе с файлами журналов транзакций.Following are some general recommendations when you are working with transaction log files:

• Шаг приращения автоматического увеличения журнала транзакций, задаваемый параметром FILEGROWTH, должен быть достаточно большим, чтобы с запасом соответствовать потребностям транзакций рабочих нагрузок.The automatic growth (autogrow) increment of the transaction log, as set by the FILEGROWTH option, must be large enough to stay ahead of the needs of the workload transactions. Во избежание слишком частых увеличений размера файла журнала следует задать достаточно большое значение шагу роста файла журнала.The file growth increment on a log file should be sufficiently large to avoid frequent expansion. Чтобы подбирать оптимальный размер журнала транзакций, рекомендуем отслеживать объем журнала, занимаемый в следующих случаях.A good pointer to properly size a transaction log is monitoring the amount of log occupied during:

  • Во время, необходимое для выполнения полного резервного копирования, так как резервные копии журнала создаются только после его завершения.The time required to execute a full backup, because log backups cannot occur until it finishes.
  • Во время, необходимое для самых продолжительных операций обслуживания индекса.The time required for the largest index maintenance operations.
  • Во время, необходимое для выполнения наибольшего пакета в базе данных.The time required to execute the largest batch in a database.

• При активации autogrow для файлов журналов и данных с помощью параметра FILEGROWTH может быть лучше задать рост журнала через размер (size), а не процент (percentage). Это позволит более эффективно контролировать увеличение, так как процент будет характеризовать постоянно растущую величину.When setting autogrow for data and log files using the FILEGROWTH option, it might be preferred to set it in size instead of percentage, to allow better control on the growth ratio, as percentage is an ever-growing amount.

  • Учитывайте, что журналы транзакций не могут использовать мгновенную инициализацию файлов, поэтому особо продолжительное время их роста имеет критическую важность.Keep in mind that transaction logs cannot leverage Instant File Initialization, so extended log growth times are especially critical.
  • Рекомендуется не устанавливать для журналов транзакций значение параметра FILEGROWTH выше 1024 МБ.As a best practice, do not set the FILEGROWTH option value above 1,024 MB for transaction logs. Значения для параметра FILEGROWTH по умолчанию.The default values for FILEGROWTH option are:

  ВерсияVersion	Значения по умолчаниюDefault values
  Начиная с SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)	Данные — 64 МБ.Data 64 MB. Файлы журналов — 64 МБ.Log files 64 MB.
  Начиная с SQL Server 2005 (9.x)SQL Server 2005 (9.x)Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x)	Данные — 1 МБ.Data 1 MB. Файлы журналов — 10 %.Log files 10%.
  До SQL Server 2005 (9.x)SQL Server 2005 (9.x)Prior to SQL Server 2005 (9.x)SQL Server 2005 (9.x)	Данные — 10 %.Data 10%. Файлы журналов — 10 %.Log files 10%.

• При небольшом шаге приращения может формироваться слишком много виртуальных файлов журнала малого размера и снижаться производительность.A small growth increment can generate too many small VLFs and can reduce performance. Чтобы определить оптимальное распределение виртуальных файлов журнала для текущего размера журнала транзакций всех баз данных в определенном экземпляре, а также требуемые приращения для достижения нужного размера, см. следующий скрипт.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

• При большом шаге приращения может формироваться слишком мало крупных виртуальных файлов журнала, что также повлияет на производительность.A large growth increment can generate too few and large VLFs and can also affect performance. Чтобы определить оптимальное распределение виртуальных файлов журнала для текущего размера журнала транзакций всех баз данных в определенном экземпляре, а также требуемые приращения для достижения нужного размера, см. следующий скрипт.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

• Даже если включено автоматическое увеличение, вы можете получить сообщение, что журнал транзакций заполнен, если его размер не может достаточно быстро увеличиваться под нужды вашего запроса.Even with autogrow enabled, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query. Дополнительные сведения об изменении шага приращения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых группFor more information on changing the growth increment, see ALTER DATABASE (Transact-SQL) File and Filegroup options

• Наличие множества файлов журнала в базе данных не способствует повышению производительности, так как файлы журнала транзакций не используют пропорциональное заполнение, как файлы данных в одной файловой группе.Having multiple log files in a database does not enhance performance in any way, because the transaction log files do not use proportional fill like data files in a same filegroup.

• Вы можете настроить автоматическое сжатие файлов журналов.Log files can be set to shrink automatically. Но делать это не рекомендуется, и параметру базы данных auto_shrink по умолчанию задано значение FALSE.However this is not recommended, and the auto_shrink database property is set to FALSE by default. Если параметру auto_shrink задано значение TRUE, автоматическое сжатие уменьшает размер файла, только если в нем не использовано более 25 % объема.If auto_shrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused.

  • Файл будет сжат либо до размера, в котором 25 % пространства не используется, либо до исходного размера, каким бы большим он ни был.The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger.
  • Сведения об изменении свойства auto_shrink см. в разделах Просмотр или изменение свойств базы данных и Параметры ALTER DATABASE SET (Transact-SQL).For information about changing the setting of the auto_shrink property, see View or Change the Properties of a Database and ALTER DATABASE SET Options (Transact-SQL).

См. также разделSee also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
Устранение неполадок при переполнении журнала транзакций (ошибка SQL Server 9002) Troubleshoot a Full Transaction Log (SQL Server Error 9002)
Раздел "Резервные копии журналов транзакций" в руководстве по архитектуре журнала транзакций SQL Server и управлению им Transaction Log Backups in the SQL Server Transaction Log Architecture and Management Guide
Резервные копии журналов транзакций (SQL Server) Transaction Log Backups (SQL Server)
Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых группALTER DATABASE (Transact-SQL) File and Filegroup options