Руководство по архитектуре журнала транзакций SQL Server и управлению имSQL Server Transaction Log Architecture and Management Guide

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Каждая база данных SQL ServerSQL Server имеет журнал транзакций, в котором фиксируются все изменения данных, произведенные в каждой из транзакций.Every SQL ServerSQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. Журнал транзакций является критическим компонентом базы данных и в случае системного сбоя может потребоваться для приведения базы данных в согласованное состояние.The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. В этом руководстве приводятся сведения о физической и логической архитектуре журнала транзакций.This guide provides information about the physical and logical architecture of the transaction log. Понимание архитектуры может помочь в повышении эффективности работы с журналами транзакций.Understanding the architecture can improve your effectiveness in managing transaction logs.

Логическая архитектура журнала транзакцийTransaction Log Logical Architecture

Логически журнал транзакций SQL ServerSQL Server работает так, как если бы он являлся последовательностью записей в журнале.The SQL ServerSQL Server transaction log operates logically as if the transaction log is a string of log records. Каждая запись журнала идентифицируется регистрационным номером транзакции (номер LSN).Each log record is identified by a log sequence number (LSN). Каждая новая запись добавляется в логический конец журнала с номером LSN, который больше номера LSN предыдущей записи.Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. Записи журнала хранятся в той последовательности, в которой они были созданы.Log records are stored in a serial sequence as they are created. Каждая запись журнала содержит идентификатор транзакции, к которой она относится.Each log record contains the ID of the transaction that it belongs to. Все записи журнала, связанные с определенной транзакцией, с помощью обратных указателей связаны в цепочку, которая предназначена для ускорения отката транзакции.For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

Записи журнала для изменения данных содержат либо выполненную логическую операцию, либо исходный и результирующий образ измененных данных.Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. Исходный образ записи — это копия данных до выполнения операции, а результирующий образ — копия данных после ее выполнения.The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

Действия, которые необходимо выполнить для восстановления операции, зависят от типа журнальной записи:The steps to recover an operation depend on the type of log record:

  • Зарегистрирована логическая операция.Logical operation logged

    • Для наката логической операции выполняется эта операция.To roll the logical operation forward, the operation is performed again.

    • Для отката логической операции выполняется логическая операция, обратная зарегистрированной.To roll the logical operation back, the reverse logical operation is performed.

  • Зарегистрированы исходный и результирующий образы записи.Before and after image logged

    • Для наката операции применяется результирующий образ.To roll the operation forward, the after image is applied.

    • Для отката операции применяется исходный образ.To roll the operation back, the before image is applied.

В журнал транзакций записываются различные типы операций,Many types of operations are recorded in the transaction log. например:These operations include:

  • начало и конец каждой транзакции;The start and end of each transaction.

  • любые изменения данных (вставка, обновление или удаление),Every data modification (insert, update, or delete). включая изменения в любой таблице (в том числе и в системных таблицах), производимые системными хранимыми процедурами или инструкциями языка DDL;This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

  • любое выделение и освобождение страниц и экстентов;Every extent and page allocation or deallocation.

  • создание и удаление таблиц и индексов.Creating or dropping a table or index.

Кроме того, регистрируются операции отката.Rollback operations are also logged. Каждая транзакция резервирует в журнале транзакций место, чтобы при выполнении инструкции отката или возникновения ошибки в журнале было достаточно места для регистрации отката.Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. Объем резервируемого пространства зависит от выполняемых в транзакции операций, но обычно он равен объему, необходимому для регистрации каждой из операций.The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. Все это пространство после завершения транзакции освобождается.This reserved space is freed when the transaction is completed.

Часть файла журнала от первой записи, требуемой для полного отката базы данных, до последней зарегистрированной записи называется активной частью журнала или активным журналом.The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. Этот раздел журнала необходим для выполнения полного восстановления базы данных.This is the section of the log required to a full recovery of the database. Ни одна часть активного журнала не может быть усечена.No part of the active log can ever be truncated. Регистрационный номер транзакции в журнале (LSN) этой первой записи называется минимальным номером LSN восстановления (MinLSN).The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

Физическая архитектура журнала транзакцийTransaction Log Physical Architecture

Журнал транзакций в базе данных сопоставляет один или несколько физических файлов.The transaction log in a database maps over one or more physical files. По сути, файл журнала представляет собой строку записей журнала.Conceptually, the log file is a string of log records. Физически последовательность записей журнала эффективно хранится в наборе физических файлов, которые образуют журнал транзакций.Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. Для каждой базы данных должен существовать хотя бы один файл журнала.There must be at least one log file for each database.

Внутри системы компонент Компонент SQL Server Database EngineSQL Server Database Engine делит каждый физический файл журнала на несколько виртуальных файлов журнала.The Компонент SQL Server Database EngineSQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). Виртуальные файлы журнала не имеют фиксированных размеров. Не существует также и определенного числа виртуальных файлов журнала, приходящихся на один физический файл журнала.Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. Компонент Компонент Database EngineDatabase Engine динамически определяет размер виртуальных файлов журнала при создании или расширении файлов журнала.The Компонент Database EngineDatabase Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. Компонент Компонент Database EngineDatabase Engine стремится обслуживать небольшое число виртуальных файлов.The Компонент Database EngineDatabase Engine tries to maintain a small number of virtual files. После расширения файла журнала размер виртуальных файлов определяется как сумма размера существующего журнала и размера нового приращения файла.The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. Администраторы не могут настраивать или устанавливать размеры и число виртуальных файлов журнала.The size or number of virtual log files cannot be configured or set by administrators.

Примечание

Для создания виртуального файла журнала (VLF) используется следующий метод.Virtual log file (VLF) creation follows this method:

  • Если дальнейшее увеличение размера не превышает 1/8 текущего физического размера журнала, создается один виртуальный файл журнала, который покрывает увеличение размера (начиная с SQL Server 2014 (12.x)SQL Server 2014 (12.x)).If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x))
  • Если дальнейшее увеличение размера превышает 1/8 текущего размера журнала, следует использовать метод, доступный в версиях до 2014.If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:
    • Если увеличение размера не превышает 64 МБ, создаются четыре виртуальных файла журнала, которые покрывают увеличение размера (например, для изменения размера в 1 МБ создаются четыре виртуальных файла журнала по 256 КБ каждый).If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)
    • Если увеличение размера находится в диапазоне от 64 МБ до 1 ГБ, создаются восемь виртуальных файлов журнала, которые покрывают увеличение размера (например, для изменения размера в 512 МБ создаются восемь виртуальных файлов журнала по 64 МБ каждый).If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)
    • Если увеличение размера превышает 1 ГБ, создаются 16 виртуальных файлов журнала, которые покрывают увеличение размера (например, для изменения размера в 8 ГБ создаются 16 виртуальных файлов журнала по 512 МБ каждый).If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)

Если в результате большого числа малых приращений файлы журнала разрастаются до крупных размеров, они будут иметь множество виртуальных файлов журнала.If the log files grow to a large size in many small increments, they will have many virtual log files. Это может замедлить запуск базы данных, а также операции резервного копирования и восстановления журналов.This can slow down database startup and also log backup and restore operations. И наоборот, если файлы журнала разрастаются до крупных размеров в результате всего нескольких или даже одного приращения, они будут иметь очень мало виртуальных файлов журнала.Conversely, if the log files are set to a large size with few or just one increment, they will have few very large virtual log files. Дополнительные сведения о правильной оценке параметра требуемого размера и автоувеличения для журнала транзакций см. в разделе Рекомендации статьи Управление размером файла журнала транзакций.For more information on properly estimating the required size and autogrow setting of a transaction log, refer to the Recommendations section of Manage the size of the transaction log file.

Рекомендуется назначать файлам журнала значение size, которое было бы максимально близким к окончательному требуемому размеру, используя требуемые приращения для обеспечения оптимального распределения VLF, а также задавать относительно большое значение growth_increment.We recommend that you assign log files a size value close to the final size required, using the required increments to achieve optimal VLF distribution, and also have a relatively large growth_increment value. Воспользуйтесь советом ниже, чтобы определить оптимальное распределение VLF для текущего размера журнала транзакций.See the tip below to determine the optimal VLF distribution for the current transaction log size.

  • Значение size, задаваемое аргументом SIZE инструкции ALTER DATABASE, характеризует первоначальный размер файла журнала.The size value, as set by the SIZE argument of ALTER DATABASE is the initial size for the log file.
  • Значение growth_increment (которое также называют значением автоувеличения), задаваемое аргументом FILEGROWTH инструкции ALTER DATABASE, — это объем пространства, добавляемый в файл каждый раз, когда требуется больше места.The growth_increment value (also referred as the autogrow value), as set by the FILEGROWTH argument of ALTER DATABASE, is the amount of space added to the file every time new space is required.

Дополнительные сведения об аргументах FILEGROWTH и SIZE для ALTER DATABASE см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп.For more information on FILEGROWTH and SIZE arguments of ALTER DATABASE, see ALTER DATABASE (Transact-SQL) File and Filegroup Options.

Совет

Чтобы определить оптимальное распределение виртуальных файлов журнала для текущего размера журнала транзакций всех баз данных в определенном экземпляре, а также требуемые приращения для достижения нужного размера, см. следующий скрипт.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.

Журнал транзакций является оборачиваемым файлом.The transaction log is a wrap-around file. Рассмотрим пример. Пусть база данных имеет один физический файл журнала, разделенный на четыре виртуальных файла журнала.For example, consider a database with one physical log file divided into four VLFs. При создании базы данных логический файл журнала начинается в начале физического файла журнала.When the database is created, the logical log file begins at the start of the physical log file. Новые записи журнала добавляются в конце логического журнала и приближаются к концу физического файла журнала.New log records are added at the end of the logical log and expand toward the end of the physical log. Усечение журнала освобождает любые виртуальные журналы, все записи которых находятся перед минимальным регистрационным номером восстановления в журнале транзакций (MinLSN).Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). MinLSN — это регистрационный номер транзакции самой старой записи в журнале, которая необходима для успешного отката на уровне всей базы данных.The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. Журнал транзакций рассматриваемой в данном примере базы данных будет выглядеть примерно так же, как на следующей иллюстрации.The transaction log in the example database would look similar to the one in the following illustration.

журнал_транзакций_3

Когда конец логического журнала достигнет конца физического файла журнала, новые записи журнала будут размещаться в начале физического файла журнала.When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.

журнал_транзакций_4

Этот цикл повторяется бесконечно, пока конец логического журнала не совмещается с началом этого логического журнала.This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. Если старые записи журнала усекаются достаточно часто, так что при этом всегда остается место для новых записей журнала, созданных с новой контрольной точки, журнал постоянно остается незаполненным.If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills. Однако, если конец логического журнала совмещается с началом этого логического журнала, происходит одно из двух событий, перечисленных ниже.However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

  • Если для журнала включен параметр FILEGROWTH и на диске имеется свободное место, файл расширяется на величину, указанную в параметре growth_increment, и новые записи журнала будут добавляться в это расширение.If the FILEGROWTH setting is enabled for the log and space is available on the disk, the file is extended by the amount specified in the growth_increment parameter and the new log records are added to the extension. Дополнительные сведения о параметре FILEGROWTH см. в статье Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп.For more information about the FILEGROWTH setting, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

  • Если параметр FILEGROWTH не включен или диск, на котором размещается файл журнала, имеет меньше свободного места, чем указано в параметре growth_increment, выдается ошибка 9002.If the FILEGROWTH setting is not enabled, or the disk that is holding the log file has less free space than the amount specified in growth_increment, an 9002 error is generated. Дополнительные сведения см. в разделе Устранение неполадок при переполнении журнала транзакций.Refer to Troubleshoot a Full Transaction Log for more information.

Если в журнале содержится несколько физических файлов журнала, логический журнал будет продвигаться по всем физическим файлам журнала до тех пор, пока он не вернется на начало первого физического файла журнала.If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file.

Важно!

Дополнительные сведения об управлении размером журнала транзакций см. в разделе Управление размером файла журнала транзакций.For more information about transaction log size management, see Manage the Size of the Transaction Log File.

Усечение журналаLog Truncation

Усечение журнала необходимо для предотвращения переполнения журнала.Log truncation is essential to keep the log from filling. При усечении журнала удаляются неактивные виртуальные файлы журнала из логического журнала транзакций базы данных SQL ServerSQL Server , что приводит к освобождению пространства в логическом журнале для повторного использования физическим журналом транзакций.Log truncation deletes inactive virtual log files from the logical transaction log of a SQL ServerSQL Server database, freeing space in the logical log for reuse by the physical transaction log. Если усечение журнала транзакций не выполняется, со временем он заполняет все доступное место на диске, отведенное для файлов физического журнала.If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. Однако перед усечением журнала должна быть выполнена операция создания контрольной точки.However, before the log can be truncated, a checkpoint operation must occur. Новая контрольная точка записывает текущие страницы, измененные в памяти (известные как измененные незафиксированные страницы), вместе со сведениями журнала транзакций из памяти на диск.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. При создании контрольной точки неактивная часть журнала транзакций помечается как неиспользуемая,When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. после чего ее можно освободить путем усечения журнала.Thereafter, the inactive portion can be freed by log truncation. Дополнительные сведения о контрольных точках см. в статье Контрольные точки базы данных (SQL Server).For more information about checkpoints, see Database Checkpoints (SQL Server).

На следующем рисунке показан журнал транзакций до усечения и после.The following illustrations show a transaction log before and after truncation. На первом рисунке показан журнал транзакций, который никогда не усекался.The first illustration shows a transaction log that has never been truncated. В настоящий момент логический журнал состоит из четырех виртуальных файлов.Currently, four virtual log files are in use by the logical log. Логический журнал начинается с первого файла виртуального журнала и заканчивается виртуальным файлом журнала 4.The logical log starts at the front of the first virtual log file and ends at virtual log 4. Запись MinLSN находится в виртуальном журнале 3.The MinLSN record is in virtual log 3. Виртуальные журналы 1 и 2 содержат только неактивные записи журнала.Virtual log 1 and virtual log 2 contain only inactive log records. Эти записи можно усечь.These records can be truncated. Виртуальный журнал 5 пока не используется и не является частью текущего логического журнала.Virtual log 5 is still unused and is not part of the current logical log.

журнал_транзакций_2

На втором рисунке показан журнал после усечения.The second illustration shows how the log appears after being truncated. Виртуальные журналы 1 и 2 усечены и могут использоваться повторно.Virtual log 1 and virtual log 2 have been freed for reuse. Логический журнал теперь начинается с виртуального журнала 3.The logical log now starts at the beginning of virtual log 3. Виртуальный журнал 5 пока не используется и не является частью текущего логического журнала.Virtual log 5 is still unused, and it is not part of the current logical log.

журнал_транзакций_3

За исключением тех случаев, когда усечение журнала по каким-то причинам задерживается, оно выполняется автоматически после наступления следующих событий.Log truncation occurs automatically after the following events, except when delayed for some reason:

  • В простой модели восстановления — после достижения контрольной точки.Under the simple recovery model, after a checkpoint.
  • В модели полного восстановления или в модели восстановления с неполным протоколированием — после создания резервной копии журналов, при условии, что со времени предыдущей операции резервного копирования была достигнута контрольная точка.Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

Усечение журнала может быть задержано из-за множества факторов.Log truncation can be delayed by a variety of factors. В случае большой задержки усечения журнала возможно заполнение журнала транзакций.In the event of a long delay in log truncation, the transaction log can fill up. См. сведения в разделах Факторы, которые могут вызвать задержку усечения журнала и Устранение неполадок при переполнении журнала транзакций (ошибка SQL Server 9002).For information, see Factors that can delay log truncation and Troubleshoot a Full Transaction Log (SQL Server Error 9002).

Журнал транзакций с упреждающей записьюWrite-Ahead Transaction Log

В этом разделе описана роль, которую журнал транзакций с упреждающей записью играет в записи изменений данных на диск.This section describes the role of the write-ahead transaction log in recording data modifications to disk. SQL ServerSQL Server использует алгоритм с упреждающей записью журнала (WAL), который гарантирует, что сначала на диск будет записана соответствующая запись журнала, и только после этого изменения данных будут записаны на диск.uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. Таким образом обеспечиваются свойства ACID для транзакции.This maintains the ACID properties for a transaction.

Для понимания принципов работы упреждающего ведения журнала важно знать, как измененные данные записываются на диск.To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL ServerSQL Server Имеет буферный кэш, в который считываются страницы данных, когда требуется получить данные.maintains a buffer cache into which it reads data pages when data must be retrieved. Если какая-либо из страниц в буферном кэше изменилась, она не записывается сразу на диск, а помечается грязной.When a page is modified in the buffer cache, it is not immediately written back to disk; instead, the page is marked as dirty. До момента ее физической записи на диск к странице могла быть применена одна или несколько операций логической записи.A data page can have more than one logical write made before it is physically written to disk. При каждой логической операции записи в кэш журнала, который записывает изменения, добавляется запись журнала транзакций.For each logical write, a transaction log record is inserted in the log cache that records the modification. Записи журнала должны быть перенесены на диск до того, как соответствующая «грязная» страница будет удалена из буферного кэша и записана на диск.The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. Заключается в том, что процесс контрольных точек производит периодический просмотр буферного кэша на наличие буферов со страницами определенной базы данных и запись всех «грязных» страниц на диск.The checkpoint process periodically scans the buffer cache for buffers with pages from a specified database and writes all dirty pages to disk. Контрольные точки экономят время во время последующего восстановления при помощи создания точки, в которой все «грязные» страницы гарантированно записываются на диск.Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

Запись измененной страницы данных из буферного кэша на диск называется сбросом страницы на диск.Writing a modified data page from the buffer cache to disk is called flushing the page. SQL ServerSQL Server Имеет логику, которая предотвращает запись на диск "грязной" страницы до записи на него связанной записи журнала.has logic that prevents a dirty page from being flushed before the associated log record is written. Содержимое журнала записывается на диск при сбросе буферов журнала.Log records are written to disk when the log buffers are flushed. Это происходит при фиксации транзакции или заполнении буферов журнала.This happens whenever a transaction commits or the log buffers become full.

Резервные копии журналов транзакцийTransaction Log Backups

Этот раздел содержит основные понятия о создании резервной копии и восстановлении журналов транзакций.This section presents concepts about how to back up and restore (apply) transaction logs. В рамках модели полного восстановления или восстановления с неполным протоколированием для восстановления данных важно регулярно создавать резервные копии журнала транзакций (резервные копии журнала).Under the full and bulk-logged recovery models, taking routine backups of transaction logs (log backups) is necessary for recovering data. Можно создать резервную копию журнала во время выполнения полного резервного копирования.You can back up the log while any full backup is running. Дополнительные сведения о моделях восстановления см. в статье Резервное копирование и восстановление баз данных SQL Server.For more information about recovery models, see Back Up and Restore of SQL Server Databases.

Перед созданием первой резервной копии журнала необходимо создать полную резервную копию данных, например резервную копию базы данных или первую в наборе резервную копию файлов.Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. Восстановить базу данных исключительно из резервных копий файлов может оказаться довольно сложно.Restoring a database by using only file backups can become complex. Поэтому рекомендуется начинать резервирование с создания полной резервной копии базы данных.Therefore, we recommend that you start with a full database backup when you can. После этого необходимо регулярное создание резервных копий журнала транзакций.Thereafter, backing up the transaction log regularly is necessary. Это не только уменьшит вероятность потери данных, но и даст возможность производить усечение журнала транзакций.This not only minimizes work-loss exposure but also enables truncation of the transaction log. Обычно он усекается после каждого обычного резервного копирования журналов,Typically, the transaction log is truncated after every conventional log backup.

Важно!

Рекомендуем создавать резервные копии журналов с достаточной периодичностью в соответствии с вашими бизнес-требованиями, в особенности касающимися устойчивости к потере данных, что может произойти из-за повреждения хранилища журналов.We recommend taking frequent enough log backups to support your business requirements, specifically your tolerance for work loss such as might be caused by a damaged log storage. Частота создания резервных копий журнала зависит от степени толерантности к возможности потери данных и от того, какое количество резервных копий журнала получится хранить и в потенциале восстанавливать, а также каким количеством управлять.The appropriate frequency for taking log backups depends on your tolerance for work-loss exposure balanced by how many log backups you can store, manage, and, potentially, restore. При реализации стратегии восстановления и, в частности, периодичности резервного копирования журнала, определите необходимое целевое время и целевую точку восстановления.Think about the required RTO and RPO when implementing your recovery strategy, and specifically the log backup cadence. Возможно, создания резервных копий журналов один раз в 15-30 минут может оказаться достаточно.Taking a log backup every 15 to 30 minutes might be enough. Если предприятию необходимо минимизировать вероятность потери данных, следует увеличить частоту создания резервных копий журнала.If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. Более частое создание резервных копий журнала предоставляет преимущество за счет более частого усечения журнала, результатом которого является меньший размер файлов журнала.More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.

Важно!

Чтобы ограничить число резервных копий журналов, которые требуется восстановить, важно периодически создавать резервные копии данных.To limit the number of log backups that you need to restore, it is essential to routinely back up your data. Например, можно запланировать еженедельное создание полной резервной копии базы данных и ежедневное создание разностных резервных копий.For example, you might schedule a weekly full database backup and daily differential database backups.
Опять же, при реализации стратегии восстановления и, в частности, периодичности полного и разностного резервного копирования базы данных, определите необходимое целевое время и целевую точку восстановления.Again, think about the required RTO and RPO when implementing your recovery strategy, and specifically the full and differential database backup cadence.

Дополнительные сведения о создании резервных копий журналов транзакций см. в разделе Резервные копии журналов транзакций (SQL Server).For more information about transaction log backups, see Transaction Log Backups (SQL Server).

Цепочка журналовThe Log Chain

Непрерывная последовательность резервных копий журналов называется цепочкой журналов.A continuous sequence of log backups is called a log chain. Цепочка журналов начинается с полной резервной копии базы данных.A log chain starts with a full backup of the database. Обычно новая цепочка журналов начинается, только когда создается первая резервная копия базы данных или после переключения модели восстановления с простой на полную или на модель с неполным протоколированием.Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery. Существующая цепочка журналов остается без изменений, если не выбрана перезапись существующих наборов резервных копий при создании резервной копии базы данных целиком.Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. Сохраняя неизменность цепочки журналов, базу данных можно восстановить из любой резервной копии полной базы данных в любом наборе носителей и из всех последующих резервных копий журналов до точки восстановления.With the log chain intact, you can restore your database from any full database backup in the media set, followed by all subsequent log backups up through your recovery point. Точка восстановления может быть концом последней резервной копии журналов или определенной точкой восстановления в любой из резервных копий журналов.The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups. Дополнительные сведения см. в разделе Резервные копии журналов транзакций (SQL Server).For more information, see Transaction Log Backups (SQL Server).

Чтобы восстановить базу данных на момент точки сбоя, нужна неповрежденная цепочка журналов.To restore a database up to the point of failure, the log chain must be intact. Непрерывная последовательность резервных копий журналов должна следовать до точки сбоя.That is, an unbroken sequence of transaction log backups must extend up to the point of failure. Точка начала этой последовательности зависит от типа восстанавливаемой резервной копии: резервная копия базы данных, частичная резервная копия или резервная копия файлов.Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. В случае резервной копии базы данных или частичной резервной копии последовательность резервных копий журнала должна начинаться от конца резервной копии базы данных или частичной резервной копии.For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup. В наборе резервных копий файлов последовательность резервных копий журналов должна следовать от начала полного набора резервных копий файлов.For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups. Дополнительные сведения см. в разделе Применение резервных копий журналов транзакций (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Восстановление резервных копий журналаRestore Log Backups

Восстановление резервной копии журналов выполняет накат изменения, которые записаны в журнале транзакций, для воссоздания точного состояния базы данных на момент начала операции резервного копирования журнала.Restoring a log backup rolls forward the changes that were recorded in the transaction log to re-create the exact state of the database at the time the log backup operation started. При восстановлении базы данных необходимо восстанавливать резервные копии журналов, которые были созданы после создания восстановленной полной резервной копии или с начала первой восстановленной резервной копии файлов.When you restore a database, you will have to restore the log backups that were created after the full database backup that you restore, or from the start of the first file backup that you restore. Обычно после восстановления последней резервной копии данных или разностной резервной копии необходимо произвести восстановление серии резервных копий журналов до точки восстановления.Typically, after you restore the most recent data or differential backup, you must restore a series of log backups until you reach your recovery point. Затем производится восстановление базы данных.Then, you recover the database. При этом откатываются все незавершенные на момент восстановления транзакции, и база данных переводится в режим «в сети».This rolls back all transactions that were incomplete when the recovery started and brings the database online. После восстановления базы данных нельзя более восстанавливать резервные копии.After the database has been recovered, you cannot restore any more backups. Дополнительные сведения см. в разделе Применение резервных копий журналов транзакций (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Контрольные точки и активная часть журналаCheckpoints and the Active Portion of the Log

При достижении контрольных точек измененные страницы данных записываются из буферного кэша текущей базы данных на диск.Checkpoints flush dirty data pages from the buffer cache of the current database to disk. Это сводит к минимуму активную часть журнала, которую приходится обрабатывать при полном восстановлении базы данных.This minimizes the active portion of the log that must be processed during a full recovery of a database. Во время полного восстановления базы данных выполняются следующие действия.During a full recovery, the following types of actions are performed:

  • Накат записанных в журнал изменений, не записанных на диск до остановки системы.The log records of modifications not flushed to disk before the system stopped are rolled forward.
  • Откат всех изменений, связанных с незавершенными транзакциями, такими как транзакции, для которых в журнале нет записи COMMIT или ROLLBACK.All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.

Функционирование контрольной точкиCheckpoint Operation

Контрольная точка выполняет в базе данных следующее.A checkpoint performs the following processes in the database:

  • Записывает в файл журнала запись, отмечающую начало контрольной точки.Writes a record to the log file, marking the start of the checkpoint.

  • Сохраняет данные, записанные для контрольной точки в цепи записей журнала контрольной точки.Stores information recorded for the checkpoint in a chain of checkpoint log records.

    Одним из элементов данных, регистрируемых в записях контрольной точки, является номер LSN первой записи журнала, при отсутствии которой успешный откат в масштабе всей базы данных невозможен.One piece of information recorded in the checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. Такой номер LSN называется минимальным номером LSN восстановления (MinLSN).This LSN is called the Minimum Recovery LSN (MinLSN). Номер MinLSN является наименьшим значением из:The MinLSN is the minimum of the:

    • номера LSN начала контрольной точки;LSN of the start of the checkpoint.
    • номера LSN начала старейшей активной транзакции;LSN of the start of the oldest active transaction.
    • номера LSN начала старейшей транзакции репликации, которая еще не была доставлена базе данных распространителя.LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.

    Записи контрольной точки содержат также список активных транзакций, изменивших базу данных.The checkpoint records also contain a list of all the active transactions that have modified the database.

  • Если база данных использует простую модель восстановления, помечает для повторного использования пространство, предшествующее номеру MinLSN.If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.

  • Записывает все измененные страницы журналов и данных на диск.Writes all dirty log and data pages to disk.

  • Записывает в файл журнала запись, отмечающую конец контрольной точки.Writes a record marking the end of the checkpoint to the log file.

  • Записывает в страницу загрузки базы данных номер LSN начала соответствующей цепи.Writes the LSN of the start of this chain to the database boot page.

Действия, приводящие к срабатыванию контрольных точекActivities that cause a Checkpoint

Контрольные точки срабатывают в следующих ситуациях.Checkpoints occur in the following situations:

  • При явном выполнении инструкции CHECKPOINT.A CHECKPOINT statement is explicitly executed. Контрольная точка срабатывает в текущей базе данных соединения.A checkpoint occurs in the current database for the connection.
  • При выполнении в базе данных операции с минимальной регистрацией, например при выполнении операции массового копирования для базы данных, на которую распространяется модель восстановления с неполным протоколированием.A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.
  • При добавлении или удалении файлов баз данных с использованием инструкции ALTER DATABASE.Database files have been added or removed by using ALTER DATABASE.
  • При остановке экземпляра SQL Server инструкцией SHUTDOWN или при остановке службы SQL Server (MSSQLSERVER).An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. И в том, и в другом случае будет создана контрольная точка для каждой базы данных на экземпляре SQL Server.Either action causes a checkpoint in each database in the instance of SQL Server.
  • Экземпляр SQL Server периодически создает для каждой базы данных автоматические контрольные точки с целью сократить время восстановления базы данных.An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.
  • При создании резервной копии базы данных.A database backup is taken.
  • При выполнении действия, требующего отключения базы данных.An activity requiring a database shutdown is performed. Примерами могут служить присвоение параметру AUTO_CLOSE значения ON и закрытие последнего соединения пользователя с базой данных или изменение параметра базы данных, требующее перезапуска базы данных.For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

Автоматические контрольные точкиAutomatic Checkpoints

Ядро СУБД SQL Server создает автоматические контрольные точки.The SQL Server Database Engine generates automatic checkpoints. Интервал между автоматическими контрольными точками определяется на основе использованного места в журнале и времени, прошедшего с момента создания последней контрольной точки.The interval between automatic checkpoints is based on the amount of log space used and the time elapsed since the last checkpoint. Интервал времени между автоматическими контрольными точками колеблется в широких пределах и может быть довольно длительным, если база данных изменяется редко.The time interval between automatic checkpoints can be highly variable and long, if few modifications are made in the database. При крупномасштабных изменениях данных частота автоматических контрольных точек может быть гораздо выше.Automatic checkpoints can also occur frequently if lots of data is modified.

Используйте параметр конфигурации сервера recovery interval , чтобы вычислить интервал между автоматическими контрольными точками для всех баз данных на экземпляре сервера.Use the recovery interval server configuration option to calculate the interval between automatic checkpoints for all the databases on a server instance. Значение этого параметра определяет максимальное время, отводимое ядру СУБД на восстановление базы данных при перезапуске системы.This option specifies the maximum time the Database Engine should use to recover a database during a system restart. Ядро СУБД оценивает число записей журнала, которые оно может обработать за время recovery interval в ходе операции восстановления.The Database Engine estimates how many log records it can process in the recovery interval during a recovery operation.

Кроме того, интервал между автоматическими контрольными точками зависит от модели восстановления, как показано ниже.The interval between automatic checkpoints also depends on the recovery model:

  • Если применяется полная модель восстановления или модель восстановления с неполным протоколированием, то автоматическая контрольная точка создается каждый раз, когда число записей в журнале достигает значения, определенного ядром СУБД в качестве предельного количества записей, которое оно может обработать за время, заданное параметром recovery interval.If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

  • Если используется простая модель восстановления базы данных, автоматическая контрольная точка создается каждый раз, когда число записей в журнале достигает меньшего из двух предельных условий:If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    • журнал заполняется на 70 процентов;The log becomes 70 percent full.
    • число записей в журнале достигает значения, определенного ядром СУБД в качестве количества записей, которое оно может обработать за время, заданное параметром recovery interval.The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

Информацию о настройке интервала восстановления см. в статье Настройка параметра конфигурации сервера recovery interval.For information about setting the recovery interval, see Configure the recovery interval Server Configuration Option.

Совет

Параметр расширенной настройки SQL Server, -k, позволяет администратору баз данных регулировать поведение ввода-вывода контрольной точки с учетом скорости обработки подсистемой ввода-вывода некоторых типов контрольных точек.The -k SQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. Параметр настройки -k применяется к автоматическим контрольным точкам и любым не отрегулированным иным способом контрольным точкам.The -k setup option applies to automatic checkpoints and any otherwise unthrottled checkpoints.

Если используется простая модель восстановления базы данных, то при срабатывании автоматических контрольных точек неиспользуемая часть журнала транзакций удаляется.Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model. Однако при использовании модели полного восстановления или модели восстановления с неполным протоколированием журнал в результате срабатывания автоматических контрольных точек не усекается.However, if the database is using the full or bulk-logged recovery models, the log is not truncated by automatic checkpoints. Дополнительные сведения см. в статье Журнал транзакций.For more information, see The Transaction Log.

Инструкция CHECKPOINT теперь поддерживает необязательный аргумент checkpoint_duration, определяющий время (в секундах), отводимое контрольным точкам на завершение.The CHECKPOINT statement now provides an optional checkpoint_duration argument that specifies the requested period of time, in seconds, for checkpoints to finish. Дополнительные сведения см. в статье CHECKPOINT.For more information, see CHECKPOINT.

активным журналомActive Log

Часть файла журнала, начинающаяся с номера MinLSN и заканчивающаяся последней зафиксированной записью, называется активной частью журнала или "активным журналом".The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log. Этот раздел журнала необходим для выполнения полного восстановления базы данных.This is the section of the log required to do a full recovery of the database. Ни одна часть активного журнала не может быть усечена.No part of the active log can ever be truncated. Все записи журнала до номера MinLSN должны быть удалены из частей журнала.All log records must be truncated from the parts of the log before the MinLSN.

На следующем рисунке изображена упрощенная схема журнала завершения транзакций, содержащего две активные транзакции.The following illustration shows a simplified version of the end-of-a-transaction log with two active transactions. Записи контрольных точек были сжаты в одну запись.Checkpoint records have been compacted to a single record.

активный_журнал

Последней записью в журнале транзакций является запись с номером LSN, равным 148.LSN 148 is the last record in the transaction log. На момент обработки записанной контрольной точки с номером LSN 147 транзакция 1 уже зафиксирована и единственной активной транзакцией является транзакция 2.At the time that the recorded checkpoint at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. В результате первая запись журнала, созданная для транзакции 2, становится старейшей записью активной транзакции на момент последней контрольной точки.That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. Таким образом, номером MinLSN становится номер LSN, равный 142 и соответствующий записи начала транзакции 2.This makes LSN 142, the Begin transaction record for Tran 2, the MinLSN.

Длительные транзакцииLong-Running Transactions

Активный журнал должен включать в себя все элементы всех незафиксированных транзакций.The active log must include every part of all uncommitted transactions. Приложение, инициирующее транзакцию и не выполняющее ее фиксацию или откат, не позволяет ядру СУБД назначать новый MinLSN.An application that starts a transaction and does not commit it or roll it back prevents the Database Engine from advancing the MinLSN. Это может привести к проблемам двух типов.This can cause two types of problems:

  • Если система будет выключена после того, как транзакцией было выполнено много незафиксированных изменений, этап восстановления при последующем перезапуске может занять гораздо больше времени, чем задано параметром recovery interval .If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take much longer than the time specified in the recovery interval option.
  • Журнал может достичь очень большого объема, потому что после номера MinLSN усечь его нельзя.The log might grow very large, because the log cannot be truncated past the MinLSN. Это справедливо даже в том случае, если используется простая модель восстановления, когда журнал транзакций обычно усекается при каждой автоматической контрольной точке.This occurs even if the database is using the simple recovery model, in which the transaction log is generally truncated on each automatic checkpoint.

Транзакции репликацииReplication Transactions

Агент чтения журнала следит за журналом транзакций всех баз данных, на которых настроена репликация транзакций, и копирует отмеченные для репликации транзакции из журнала транзакций в базу данных распространителя.The Log Reader Agent monitors the transaction log of each database configured for transactional replication, and it copies the transactions marked for replication from the transaction log into the distribution database. Активный журнал должен содержать все транзакции, отмеченные для репликации, но еще не доставленные базе данных распространителя.The active log must contain all transactions that are marked for replication, but that have not yet been delivered to the distribution database. Если эти транзакции не реплицировать в допустимый срок, усечение журнала может оказаться невозможным.If these transactions are not replicated in a timely manner, they can prevent the truncation of the log. Дополнительные сведения см. в статье Репликация транзакций.For more information, see Transactional Replication.

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

Мы рекомендуем следующие статьи и книги, которые содержат дополнительные сведения о журнале транзакций и рекомендации по управлению журналами.We recommend the following articles and books for additional information about the transaction log and log management best practices.

Журнал транзакций (SQL Server) The Transaction Log (SQL Server)
Управление размером файла журнала транзакций Manage the size of the transaction log file
Резервные копии журналов транзакций (SQL Server) Transaction Log Backups (SQL Server)
Контрольные точки базы данных (SQL Server) Database Checkpoints (SQL Server)
Настройка интервала восстановления в конфигурации сервера Configure the recovery interval Server Configuration Option
sys.dm_db_log_info (Transact-SQL) sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL) sys.dm_db_log_space_usage (Transact-SQL)
Основные сведения о ведении журнала и восстановлении из резервных копий в SQL Server. Автор: Пол Рендел (Paul Randal) Understanding Logging and Recovery in SQL Server by Paul Randal
Управление журналом транзакций SQL Server. Авторы: Тони Дэвис (Tony Davis) и Гейл Шоу (Gail Shaw)SQL Server Transaction Log Management by Tony Davis and Gail Shaw