Управление размером файла журнала транзакцийManage the size of the transaction log file
Применимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии)
SQL ServerSQL Server (all supported versions)
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.
Важно!
Избегайте переполнения содержащего журналы диска.Avoid overloading the log disk. Хранилище журналов должно отвечать требованиям к числу операций ввода-вывода в секунду и низкой задержке для транзакционной нагрузки.Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load.
Уменьшение размера файла журнала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.
Примечание
Такие факторы, как долго выполняемые транзакции, из-за которых виртуальные файлы журналов длительное время остаются в активном состоянии, могут ограничить или вовсе не допустить возможность сжатия журнала.Factors such as a long-running transaction, that keep VLFs active for an extended period, can restrict log shrinkage or even prevent the log from shrinking at all. Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала.For information, see Factors that can delay 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.
Важно!
Перед сжатием следует учесть факторы, которые могут вызвать задержку усечения журнала.Before shrinking the transaction log, keep in mind Factors that can delay log truncation. Если после сжатия журнала снова потребуется дисковое пространство, размер журнала транзакций снова будет увеличиваться, что повлияет на производительность во время операций увеличения.If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log growth operations. Дополнительные сведения см. в разделе Рекомендации этой статьи.For more information, see the Recommendations in this topic.
Сжатие файла журнала (без сжатия файлов базы данных)Shrink a log file (without shrinking database files)
Мониторинг событий сжатия файла журналаMonitor log-file shrink events
Мониторинг пространства журнала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 theADD LOG FILE
clause of theALTER DATABASE
statement. Это позволяет увеличить размер файла.Adding a log file allows the log to grow. - Чтобы увеличить размер файла журнала, используйте предложение
MODIFY FILE
инструкцииALTER DATABASE
с указанием синтаксисаSIZE
иMAXSIZE
.To enlarge the log file, use theMODIFY FILE
clause of theALTER DATABASE
statement, specifying theSIZE
andMAXSIZE
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 theSIZE
option. - Чтобы изменить шаг приращения размера, используйте параметр
FILEGROWTH
.To change the growth increment, use theFILEGROWTH
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 theMAXSIZE
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 theFILEGROWTH
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 theFILEGROWTH
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 theFILEGROWTH
option value above 1,024 MB for transaction logs. Значения для параметраFILEGROWTH
по умолчанию.The default values forFILEGROWTH
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