Руководство по архитектуре журнала транзакций SQL Server и управлению

Относится к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема платформы аналитики (PDW)База данных SQL в Microsoft Fabric

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

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

Журнал транзакций SQL Server работает логически, как если бы журнал транзакций был строкой записей журналов. Каждая запись журнала идентифицируется номером последовательности в журнале (LSN). Каждая новая запись добавляется в логический конец журнала с номером LSN, который больше номера LSN предыдущей записи. Записи журналов хранятся в последовательной последовательности при их создании, например, если LSN2 больше LSN1, изменение, описанное записью журнала, на которую ссылается LSN2, произошло после изменения, описанного записью журнала LSN1. Каждая запись журнала содержит идентификатор транзакции, к которой она относится. Для каждой транзакции все связанные с ней записи журнала по отдельности связываются в цепочку с помощью обратных указателей, что ускоряет откат транзакции.

Базовая структура LSN представляет собой [VLF ID:Log Block ID:Log Record ID]. Дополнительные сведения см. в разделах VLF и блок журнала.

Ниже приведен пример LSN: 00000031:00000da0:0001где 0x31 идентификатор VLF, 0xda0 является идентификатором блока журнала и 0x1 является первой записью журнала в этом блоке журнала. Чтобы увидеть примеры LSN, посмотрите на выходные данные DMV sys.dm_db_log_info и изучите столбец vlf_create_lsn.

Записи журнала, фиксирующие изменения данных, содержат либо сведения о выполненной логической операции, либо состояния изменённых данных до и после изменения. Перед выполнением операции изображение является копией данных; после выполнения операции изображение является копией данных после выполнения операции.

Действия, которые необходимо выполнить для восстановления операции, зависят от типа журнальной записи:

• Логическая операция зарегистрирована.

  • Чтобы перенести логическую операцию вперёд, её выполняют повторно.
  • Чтобы откатить логическую операцию, выполняется обратная логическая операция.

• Изображение «до и после» зарегистрировано.

  • Чтобы продолжить операцию, применяется последующий образ.
  • Чтобы отменить операцию, применяется предыдущее состояние.

В журнал транзакций записываются различные типы операций, Эти операции включают:

• начало и конец каждой транзакции;

• любые изменения данных (вставка, обновление или удаление), Сюда входят изменения любой таблицы, включая системные таблицы, внесенные системными хранимыми процедурами или операторами языка определения данных (DDL).

• Каждое выделение или освобождение экстентов и страниц.

• создание и удаление таблиц и индексов.

Операции отката также регистрируются. Каждая транзакция резервирует место в журнале транзакций, чтобы обеспечить наличие в нём достаточного пространства для выполнения отката, вызванного либо явной инструкцией ROLLBACK, либо возникновением ошибки. Объем зарезервированного пространства зависит от операций, выполняемых в транзакции, но обычно это равно количеству пространства, используемого для регистрации каждой операции. Все это пространство после завершения транзакции освобождается.

Участок файла журнала от первой записи журнала, который должен быть сохранён для успешного отката всей базы данных до последней записанной записи журнала, называется активной частью журнала, активным журналом или хвостом журнала. Этот раздел журнала необходим для полного восстановления базы данных. Ни одна часть активного журнала не может быть усечена. Регистрационный номер транзакции в журнале (LSN) этой первой записи журнала называется минимальным номером LSN восстановления (MinLSN). Дополнительные сведения об операциях, поддерживаемых журналом транзакций, см. в журнале транзакций.

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

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

Журнал транзакций базы данных размещается в одном или нескольких физических файлах. По сути, файл журнала представляет собой строку записей журнала. Физически последовательность записей журнала эффективно хранится в наборе физических файлов, которые образуют журнал транзакций. Для каждой базы данных должен существовать хотя бы один файл журнала.

Виртуальные файлы журнала (VLF)

SQL Server ядро СУБД делит каждый физический файл журнала на несколько виртуальных файлов журналов (VLFs). Виртуальные файлы журнала не имеют фиксированного размера, и для физического файла журнала не установлено фиксированное количество файлов виртуальных журналов. Ядро СУБД динамически выбирает размер файлов виртуальных журналов при создании или расширении файлов журналов. Ядро СУБД пытается сохранить несколько виртуальных файлов. После расширения файла журнала размер виртуальных файлов определяется как сумма размера существующего журнала и размера нового приращения файла. Размер или количество виртуальных файлов журналов не может быть настроено или задано администраторами.

Создание файла виртуального журнала

Для создания виртуального файла журнала (VLF) используется следующий метод.

• В SQL Server 2014 (12.x) и более поздних версиях, если следующее увеличение меньше 1/8 текущего физического размера файла журнала, создается 1 VLF, который покрывает размер этого увеличения.
• Если следующее увеличение превышает 1/8 текущего размера журнала, используйте метод, применявшийся до 2014 года, а именно:
  • Если рост меньше 64 МБ, создайте 4 VLFs, охватывающие размер роста (например, для роста в 1 МБ, создайте 4 VLFs размером 256 КБ).
    • В База данных SQL Azure и начиная с SQL Server 2022 (16.x) (все выпуски), логика немного отличается. Если рост меньше или равен 64 МБ, ядро СУБД создает только один VLF для покрытия размера роста.
  • Если рост составляет от 64 МБ до 1 ГБ, создайте 8 VLFs, охватывающие размер роста (например, для роста в 512 МБ, создайте 8 VLFs размером 64 МБ).
  • Если рост превышает 1 ГБ, создайте 16 VLFs, которые охватывают размер роста, например для роста в 8 ГБ, создайте 16 VLFs размером 512 МБ).

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

Мы рекомендуем создавать файлы журнала сразу почти до требуемого конечного размера, используя приращения, необходимые для достижения оптимального распределения VLF, и задавать относительно большое значение growth_increment.

Ознакомьтесь со следующими советами, чтобы определить оптимальное распределение VLF для текущего размера журнала транзакций:

• Значение размера, заданное SIZE аргументомALTER DATABASE, является начальным размером файла журнала.
• Значение growth_increment (также известное как значение автоматического увеличения), которое задаётся аргументом FILEGROWTH инструкции ALTER DATABASE, представляет собой объём пространства, добавляемого в файл каждый раз, когда требуется дополнительное пространство.

Дополнительные сведения об аргументах FILEGROWTH и SIZEALTER DATABASE см. в разделе ALTER DATABASEПараметры файлов и файловых групп (Transact-SQL).

Что происходит, когда у вас слишком много VLFs?

На начальных этапах процесса восстановления базы данных SQL Server обнаруживает все VLFs во всех файлах журнала транзакций и создает список этих VLFs. Этот процесс может занять много времени в зависимости от количества VLF, имеющихся в конкретной базе данных. Чем больше VLFs, тем дольше процесс. В базе данных может оказаться большое количество VLF, если журнал транзакций часто автоматически увеличивается или увеличивается вручную небольшими приращениями. Когда число VLFs достигает диапазона в несколько сотен тысяч, вы можете столкнуться с некоторыми или большинством следующих симптомов:

• Одно или несколько баз данных занимает очень много времени, чтобы завершить восстановление во время запуска SQL Server.
• Восстановление базы данных занимает очень много времени.
• Попытки подключения базы данных занимают очень много времени.
• При попытке настроить зеркальное отображение базы данных возникают сообщения об ошибках 1413, 1443 и 1479, указывающие время ожидания.
• При попытке восстановить базу данных возникают ошибки, связанные с памятью, такие как 701.
• Репликация транзакций или запись измененных данных могут столкнуться с значительной задержкой.

При проверке журнала ошибок SQL Server вы можете заметить, что до этапа анализа процесса восстановления базы данных может потребоваться значительное время. Рассмотрим пример.

2022-05-08 14:42:38.65 spid22s Starting up database 'lot_of_vlfs'.
2022-05-08 14:46:04.76 spid22s Analysis of database 'lot_of_vlfs' (16) is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Кроме того, SQL Server может регистрировать ошибку MSSQLSERVER_9017 при восстановлении базы данных с большим количеством VLFs:

Database %ls has more than %d virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Дополнительные сведения см. в MSSQLSERVER_9017.

Исправьте базы данных с большим количеством VLF

Чтобы поддерживать общее количество VLF в разумных пределах, например не более нескольких тысяч, можно сбросить файл журнала транзакций так, чтобы он содержал меньшее число VLF, выполнив следующие действия:

1. Сжатие файлов журнала транзакций вручную.

2. Выращивайте файлы до требуемого размера вручную на одном шаге с помощью следующего скрипта T-SQL:

   ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);

   Note

   Этот шаг также можно выполнить в SQL Server Management Studio с помощью страницы свойств базы данных.

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

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

Чтобы определить оптимальное распределение VLF для текущего размера журнала транзакций всех баз данных в данном экземпляре, а также необходимый рост для достижения требуемого размера, можно использовать следующий сценарий GitHub для исправления VLFs.

Блоки логов

Каждый VLF содержит один или несколько блоков журнала. Каждый блок журнала состоит из записей журнала (выровненных по 4-байтовой границе). Блок журнала имеет переменный размер и всегда кратен 512 байтам (минимальный размер сектора, поддерживаемый SQL Server), а его максимальный размер составляет 60 КБ. Блок журнала — это базовая единица ввода-вывода для ведения журнала транзакций.

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

Каждый блок журнала в VLF однозначно идентифицируется своим смещением блока. Первый блок всегда имеет смещение, указывающее на область за пределами первых 8 КБ в VLF.

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

Циклическая природа журнала транзакций

Журнал транзакций является оборачиваемым файлом. Например, рассмотрим базу данных с одним физическим файлом журнала, разделённым на четыре виртуальных файла журнала. При создании базы данных логический файл журнала начинается в начале физического файла журнала. Новые записи журнала добавляются в конец логического журнала и расширяются по направлению к концу физического журнала. Усечение журнала освобождает все виртуальные журналы, все записи которых предшествуют минимальному номеру последовательности в журнале восстановления (MinLSN). MinLSN — это регистрационный номер транзакции самой старой записи в журнале, которая необходима для успешного отката на уровне всей базы данных. Журнал транзакций в примере базы данных будет выглядеть примерно так, как на следующей схеме.

Когда конец логического журнала достигнет конца физического файла журнала, новые записи журнала будут размещаться в начале физического файла журнала.

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

• FILEGROWTH Если параметр включен для журнала и пространства доступен на диске, файл расширяется по количеству, указанному в параметре growth_increment, а новые записи журнала добавляются в расширение. Дополнительные сведения о параметре FILEGROWTH см. в разделе ALTER DATABASE (Transact-SQL) Параметры файлов и файловой группы.

• FILEGROWTH Если параметр не включен или диск, содержащий файл журнала, имеет меньше свободного места, чем сумма, указанная в growth_increment, создается ошибка 9002. Дополнительные сведения см. в разделе "Устранение неполадок с полным журналом транзакций( ошибка SQL Server 9002)".

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

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

Усечение журнала необходимо для предотвращения переполнения журнала. Усечение журнала удаляет неактивные виртуальные файлы журнала из логического журнала транзакций базы данных SQL Server, освобождая в нём место, чтобы физический журнал транзакций мог использовать его повторно. Если журнал транзакций никогда не усекается, то в конечном итоге он заполнит всё дисковое пространство, выделенное под его физические файлы журнала. Однако перед усечением журнала должна быть выполнена операция создания контрольной точки. Контрольная точка записывает текущие измененные страницы в памяти (известные как грязные страницы) и данные журнала транзакций из памяти на диск. При выполнении контрольной точки неактивная часть журнала транзакций помечается как доступная для повторного использования. После этого усечение журнала может освободить неактивную часть. Дополнительные сведения о контрольных точках см. в разделе "Контрольные точки базы данных" (SQL Server).

На следующих схемах показан журнал транзакций до и после усечения. На первой схеме показан журнал транзакций, который никогда не был усечен. В настоящий момент логический журнал состоит из четырех виртуальных файлов. Логический журнал начинается в начале первого виртуального файла журнала и заканчивается на виртуальном файле журнала 4. Запись MinLSN расположена в виртуальном журнале 3. Виртуальные журналы 1 и 2 содержат только неактивные записи журнала. Эти записи можно обрезать. Виртуальный журнал 5 по-прежнему не используется и не является частью текущего логического журнала.

На второй схеме показано, как выглядит журнал после усечения. Виртуальные журналы 1 и 2 освобождены для повторного использования. Логический журнал теперь начинается с виртуального журнала 3. Виртуальный журнал 5 по-прежнему не используется, и он не является частью текущего логического журнала.

Усечение журнала происходит автоматически после следующих событий, кроме случаев, когда оно по какой-либо причине задерживается:

• В простой модели восстановления — после достижения контрольной точки.
• В модели полного восстановления или в модели восстановления с неполным протоколированием — после создания резервной копии журналов, при условии, что со времени предыдущей операции резервного копирования была достигнута контрольная точка.

Усечение журнала может быть отложено различными факторами. В случае длительной задержки усечения журнала транзакций журнал транзакций может заполниться. Дополнительные сведения см. в разделах «Факторы, которые могут задержать усечение журнала транзакций» и «Устранение неполадок, связанных с переполнением журнала транзакций (ошибка SQL Server 9002)».

Журнал предзаписи транзакций

В этом разделе описана роль, которую журнал транзакций с упреждающей записью играет в записи изменений данных на диск. SQL Server использует алгоритм упреждающего журналирования (WAL), который гарантирует, что изменения данных не будут записаны на диск до того, как соответствующая запись журнала будет записана на диск. Таким образом обеспечиваются свойства ACID для транзакции.

Дополнительные сведения о WAL см. в статье Основы ввода-вывода SQL Server.

Чтобы понять, как работает упреждающее журналирование в контексте журнала транзакций, вам важно понимать, как изменённые данные записываются на диск. SQL Server поддерживает кэш буфера (также называемый буферным пулом), в который он считывает страницы данных при извлечении данных. При изменении страницы в кэше буфера она не сразу записывается на диск; Вместо этого страница помечена как грязная. На странице данных может быть несколько логических записей, прежде чем они физически записываются на диск. Для каждой логической записи в кэш журнала вставляется запись журнала транзакций, которая фиксирует это изменение. Записи журнала должны быть перенесены на диск до того, как соответствующая «грязная» страница будет удалена из буферного кэша и записана на диск. Процесс контрольной точки периодически просматривает буферный кэш в поисках буферов, содержащих страницы указанной базы данных, и записывает все «грязные» страницы на диск. Контрольные точки экономят время во время последующего восстановления при помощи создания точки, в которой все «грязные» страницы гарантированно записываются на диск.

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

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

Этот раздел содержит основные понятия о создании резервной копии и восстановлении журналов транзакций. В рамках модели полного восстановления или восстановления с неполным протоколированием для восстановления данных важно регулярно создавать резервные копии журнала транзакций (резервные копии журнала). Можно создать резервную копию журнала во время выполнения полного резервного копирования. Дополнительные сведения о моделях восстановления см. в статье "Резервное копирование и восстановление баз данных SQL Server".

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

Чтобы ограничить количество резервных копий журналов, необходимых для восстановления, необходимо регулярно создавать резервные копии данных. Например, можно запланировать еженедельное создание полной резервной копии базы данных и ежедневное создание разностных резервных копий.

Учитывайте требуемые RTO и RPO при разработке стратегии восстановления, в частности частоту полного и разностного резервного копирования базы данных.

Дополнительные сведения о резервных копиях журналов транзакций см. в статье "Резервные копии журналов транзакций" (SQL Server).

Частота резервного копирования и бизнес-требования

Следует достаточно часто создавать резервные копии журнала транзакций, чтобы обеспечить соответствие требованиям бизнеса, в частности вашему допустимому объему потери данных, например в случае повреждения хранилища журнала.

Подходящая частота создания резервных копий журнала зависит от допустимого риска потери данных и от того, сколько резервных копий журнала вы можете хранить, администрировать и, при необходимости, восстанавливать. Думайте о требуемом целевом времени восстановления (RTO) и целевой точке восстановления (RPO) при реализации стратегии восстановления и, в частности, о времени резервного копирования журналов.

Возможно, создания резервных копий журналов один раз в 15-30 минут может оказаться достаточно. Если предприятию необходимо минимизировать вероятность потери данных, следует увеличить частоту создания резервных копий журнала. Более частое создание резервных копий журнала предоставляет преимущество за счет более частого усечения журнала, результатом которого является меньший размер файлов журнала.

Цепочка логов

Непрерывная последовательность резервных копий журналов называется цепочкой журналов. Цепочка журналов начинается с полной резервной копии базы данных. Обычно новая цепочка журналов запускается только при первом резервном копировании базы данных или после перехода модели восстановления с простого восстановления на полное или массовое восстановление. Существующая цепочка журналов остается без изменений, если не выбрана перезапись существующих наборов резервных копий при создании резервной копии базы данных целиком. Сохраняя неизменность цепочки журналов, базу данных можно восстановить из любой резервной копии полной базы данных в любом наборе носителей и из всех последующих резервных копий журналов до точки восстановления. Точка восстановления может быть концом последней резервной копии журналов или определенной точкой восстановления в любой из резервных копий журналов. Дополнительные сведения см. в статье о резервных копиях журналов транзакций (SQL Server).

Чтобы восстановить базу данных на момент точки сбоя, нужна неповрежденная цепочка журналов. То есть непрерывная последовательность резервных копий журнала транзакций должна продолжаться вплоть до момента сбоя. Где должна начинаться эта последовательность журналов, зависит от типа резервных копий данных, которые вы восстанавливаете: база данных, частичный или файл. В случае резервной копии базы данных или частичной резервной копии последовательность резервных копий журнала должна начинаться от конца резервной копии базы данных или частичной резервной копии. В наборе резервных копий файлов последовательность резервных копий журналов должна следовать от начала полного набора резервных копий файлов. Дополнительные сведения см. в разделе Применение резервных копий журналов транзакций (SQL Server).

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

Восстановление резервной копии журнала транзакций применяет изменения, записанные в журнале транзакций, чтобы воссоздать точное состояние базы данных на момент начала операции резервного копирования журнала транзакций. При восстановлении базы данных необходимо восстановить резервные копии журналов, созданные после полной резервной копии базы данных, которую вы восстановите, или с начала первой резервной копии файла, которую вы восстанавливаете. Обычно после восстановления последней резервной копии данных или разностной резервной копии необходимо произвести восстановление серии резервных копий журналов до точки восстановления. Затем производится восстановление базы данных. При этом откатываются все незавершенные на момент восстановления транзакции, и база данных переводится в режим «в сети». После восстановления базы данных больше не удается восстановить резервные копии. Дополнительные сведения см. в разделе Применение резервных копий журналов транзакций (SQL Server).

Контрольные точки и активная часть журнала

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

• Записи журнала изменений, не сброшенные на диск до остановки системы, восстанавливаются.
• Все изменения, связанные с незавершенными транзакциями, например с транзакциями, для которых отсутствуют записи журнала COMMIT или ROLLBACK, откатываются.

Операция контрольной точки

Контрольная точка выполняет в базе данных следующее.

• Записывает в файл журнала запись, отмечающую начало контрольной точки.

• Сохраняет данные, записанные для контрольной точки в цепи записей журнала контрольной точки.

  Одним из элементов данных, регистрируемых в записях контрольной точки, является номер LSN первой записи журнала, при отсутствии которой успешный откат в масштабе всей базы данных невозможен. Такой номер LSN называется минимальным номером LSN восстановления (MinLSN). MinLSN — это минимальное значение из:

  • Номер LSN начала контрольной точки.
  • LSN начала самой старой активной транзакции.
  • LSN начала самой старой транзакции репликации, которая еще не была доставлена в базу данных распространителя.

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

• Если база данных использует простую модель восстановления, она помечает для повторного использования пространство, расположенное до MinLSN.

• Записывает все грязные страницы журналов и данных на диск.

• Записывает в файл журнала запись, отмечающую конец контрольной точки.

• Записывает в страницу загрузки базы данных номер LSN начала соответствующей цепи.

Действия, вызывающие контрольную точку

Контрольные точки срабатывают в следующих ситуациях.

• Оператор CHECKPOINT выполняется явным образом. В текущей базе данных для данного соединения создаётся контрольная точка.

• В базе данных выполняется операция с минимальным протоколированием; например, операция пакетного копирования выполняется в базе данных, в которой используется модель восстановления с неполным протоколированием.

• Файлы базы данных были добавлены или удалены с помощью ALTER DATABASE.

• Экземпляр SQL Server останавливается инструкцией SHUTDOWN или остановкой службы SQL Server (MSSQLSERVER). И в том, и в другом случае будет создана контрольная точка для каждой базы данных на экземпляре SQL Server.

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

• Создается резервная копия базы данных.

• Выполняется действие, требующее остановки базы данных. Это может произойти, когда параметр AUTO_CLOSE имеет значение ON и последнее пользовательское подключение к базе данных закрывается. Еще одним примером является изменение параметра базы данных, требующее перезапуска базы данных.

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

Ядро СУБД SQL Server создает автоматические контрольные точки. Интервал между автоматическими контрольными точками определяется на основе использованного места в журнале и времени, прошедшего с момента создания последней контрольной точки. Интервал времени между автоматическими контрольными точками колеблется в широких пределах и может быть довольно длительным, если база данных изменяется редко. При крупномасштабных изменениях данных частота автоматических контрольных точек может быть гораздо выше.

Используйте параметр конфигурации сервера recovery interval , чтобы вычислить интервал между автоматическими контрольными точками для всех баз данных на экземпляре сервера. Значение этого параметра определяет максимальное время, отводимое ядру СУБД на восстановление базы данных при перезапуске системы. Ядро СУБД оценивает число записей журнала, которые оно может обработать за время recovery interval в ходе операции восстановления.

Кроме того, интервал между автоматическими контрольными точками зависит от модели восстановления, как показано ниже.

• Если в базе данных используется полная модель восстановления или модель восстановления с неполным протоколированием, автоматическая контрольная точка формируется всякий раз, когда число записей в журнале достигает количества, которое компонент ядро СУБД оценивает как обрабатываемое за время, указанное в параметре интервала восстановления.

• Если используется простая модель восстановления базы данных, автоматическая контрольная точка создается каждый раз, когда число записей в журнале достигает меньшего из двух предельных условий:

  • журнал заполняется на 70 процентов;
  • число записей в журнале достигает значения, определенного ядром СУБД в качестве количества записей, которое оно может обработать за время, заданное параметром recovery interval.

Сведения о настройке интервала восстановления см. в разделе "Конфигурация сервера": интервал восстановления (min).

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

Теперь оператор CHECKPOINT предоставляет необязательный аргумент checkpoint_duration, указывающий запрошенный период времени (в секундах) для завершения контрольных точек. Дополнительные сведения см. в статье CHECKPOINT.

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

Часть файла журнала, начинающаяся с номера MinLSN и заканчивающаяся последней зафиксированной записью, называется активной частью журнала или "активным журналом". Этот раздел журнала необходим для выполнения полного восстановления базы данных. Ни одна часть активного журнала не может быть усечена. Все записи журнала должны быть усечены в частях журнала, предшествующих MinLSN.

На следующей схеме показан упрощённый вариант журнала на момент завершения транзакции с двумя активными транзакциями. Записи контрольных точек были сжаты в одну запись.

Последней записью в журнале транзакций является запись с номером LSN, равным 148. На момент обработки записанной контрольной точки с номером LSN 147 транзакция 1 уже зафиксирована и единственной активной транзакцией является транзакция 2. В результате первая запись журнала для транзакции 2 становится самой старой записью журнала для транзакции, активной на момент последней контрольной точки. Таким образом, LSN 142, соответствующий записи Begin transaction для Tran 2, становится MinLSN.

Длительные транзакции

Активный журнал должен включать в себя все элементы всех незафиксированных транзакций. Приложение, которое запускает транзакцию и не фиксирует и не откатывает ее, не позволяет компоненту ядро СУБД продвигать MinLSN. Эта ситуация может привести к двум типам проблем:

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

Восстановление длительных транзакций и проблем, описанных в этой статье, можно избежать с помощью ускоренного восстановления базы данных, функции, доступной начиная с SQL Server 2019 (15.x) и в База данных SQL Azure.

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

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

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

• Журнал транзакций
• Управление размером файла журнала транзакций
• Резервные копии журналов транзакций (SQL Server)
• Контрольные точки базы данных (SQL Server)
• Конфигурация сервера: интервал восстановления (мин)
• Ускоренное восстановление баз данных
• sys.dm_db_log_info (Transact-SQL)
• sys.dm_db_log_space_usage (Transact-SQL)
• Общие сведения о ведении журнала и восстановлении в SQL Server