SQL-Ex blog
Требованием реляционных систем баз данных является надежность (durable) транзакций. Эта «D» присутствует в свойствах транзакций ACID. Система должна гарантировать, что при внезапном сбое транзакция может быть повторена. SQL Server выполняет это требование записью всех транзакций в физический файл, который называется файлом журнала транзакций.
В сущности, всякий раз, когда фиксируется транзакция, SQL Server записывает изменения, произведенные этой транзакцией в журнал транзакций. Даже если результаты выполнения транзакции отсутствуют в файле данных, они доступны в журнале транзакций и могут быть воспроизведены в случае внезапного сбоя.
Модели восстановления и журналы транзакций
SQL Server поддерживает три модели восстановления — полную (Full), простую (Simple) и с неполным протоколированием (Bulk Logged).
При полной модели восстановления ВСЕ транзакции записываются в журнал. Таким образом, база данных может быть полностью восстановлена после сбоя. Это также означает, что резервная копия базы данных может быть восстановлена к заданному моменту времени, если доступен журнал транзакций или соответствующий бэкап. При моделях восстановления Full и Bulk Logged журналы транзакций усекаются всякий раз, когда выполняется бэкап журнала.
При простой модели восстановления также ВСЕ транзакции записываются. Однако журнал транзакций усекается всякий раз, когда база данных выполняет контрольную точку.
Контрольная точка устанавливается, когда SQL Server сбрасывает «грязные» буферы в файл данных. Грязные буферы это дисковые страницы, хранящиеся в памяти, которые были изменены транзакциями, в результате чего состояние памяти не соответствует состоянию на диске. Но мы не будем здесь это обсуждать. В простой модели восстановления SQL Server сохраняет все эти изменения в журнале транзакций до тех пор, пока они не будут сброшены на диск.
Структура журнала транзакций
Журнал транзакций — это физический файл, видимый в операционной системе, где находится база данных SQL Server. Каждая база данных имеет один журнал транзакций, но возможно сконфигурировать и больше. Дело в том, что наличие нескольких журналов транзакций не дает каких-либо преимуществ с точки зрения производительности. SQL Server выполняет запись в журнал транзакций последовательно — один файл должен быть заполнен, прежде чем использовать следующий. Однако несколько файлов, размещаемых на разных дисках, могут спасти положение, если первый файл заполнится.
Изнутри файл журнала транзакций представляет собой набор виртуальных файлов журнала. Размер и число таких файлов влияет на время, требуемое на резервирование базы данных или вывод её в рабочий режим. Полезно правильно устанавливать размер журнала транзакций и быть уверенным, что установка автоматического прироста соответствует ожидаемому уровню активности. Т.е. рост файла не должен происходить очень часто.
Что вызывает рост журнала?
Давайте создадим небольшую базу данных, используя код в листинге 1. Файл данных вначале имеет размер 4Мб, файл журнала — 2Мб. Ваши промышленные базы данных никогда не будут иметь такой размер, особенно при популярной практике pre-allocation (предварительное выделение). Мы выбрали такой размер просто в демонстрационных целях.
-- Листинг 1: Создание небольшой базы данных
create database tranlogexperiment
on primary
( name = N'tranlogexperiment', filename = N'C:\MSSQL\Data\tranlogexperiment.mdf', size = 4MB , FILEGROWTH = 1024KB )
log on
( name = N'Test1_log', filename = N'E:\MSSQL\Log\Test1_log.ldf' , size = 2MB , FILEGROWTH = 1024KB );
go
В этой базе данных мы создаем единственную таблицу (листинг 2) для последующего выполнения операторов языка манипуляции данными (DML).
-- Листинг 2: Создание таблицы
use tranlogexperiment
go
create table txn_log (
ID int
, FName varchar(50)
, LName varchar(50)
, CountryCode char (2)
)
Выполнив код в листинге 3, проверим, что мы сделали.
-- Листинг 3: Проверка модели восстановления и размеров файлов
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name='tranlogexperiment';
select DB_NAME(database_id) [Database Name]
, type_desc [Database Name]
, name [Logical file Name]
, physical_name [Physical file Name]
, size*8/1024 [File Size (MB)]
, growth*8/1024 [File Growth (MB)]
from sys.master_files where database_id=DB_ID('tranlogexperiment');
Рис.1: Результаты выполнения кода в листинге 3, но до DML
Обратите внимание на столбец File size. Приступаем к наблюдению за ростом журнала транзакций при выполнении операторов INSERT и DELETE 100000 раз (листинг 4).
-- Листинг 4: Вставка и удаление строки в таблице
use tranlogexperiment
go
insert into txn_log values (1, 'Kenneth','Igiri', 'NG');
delete from txn_log where /> go 100000
В листинге 4 выполняется вставка одной строки в таблицу txn_log с последующим её удалением; это действие повторяется 100000 раз.
В целом таблица не увеличивается в результате этих действий, однако журнал транзакций растет существенно. Если повторить запрос в листинге 3 после выполнения операторов DML из листинга 4, то увидим, насколько вырос журнал транзакций:
Рис.2: Результаты выполнения кода из листинга 3 после операторов DML
Журнал транзакций вырос с 4Мб до 40Мб в результате этих манипуляций, хотя файл данных не изменился в размерах. Это ясно показывает, что размер журнала транзакций имеет мало общего с размером данных. На размер журнала оказывает влияние интенсивность, с которой происходит изменение (DML) базы данных.
Как обслуживать журнал транзакций?
Администраторы баз данных, которые обслуживают экземпляры SQL Server установок IaaS, должны регулярно делать резервные копии журналов транзакций. Полезно иметь конфигурации аварийного восстановления, такие как Log Shipping или AlwaysOn AG. Подобные конфигурации выполняют резервирование автоматически.
При режиме полного восстановления, создание резервной копии журнала усекает те части журнала транзакций, которые больше не требуются для восстановления. Усечение журнала удаляет неактивные виртуальные файлы журнала. Тем самым освобождается место в журналах транзакций для последующего использования.
Код в листинге 6 показывает размер журнала транзакций и сколько в нем свободного пространства.
-- Листинг 6: Изменение модели восстановления
USE [tranlogexperiment]
GO
SELECT DB_NAME() AS [Database Name],
name AS [Logical File Name],
type_desc,
size/128.0 AS [Current Size (MB)],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [Free Space (MB)]
FROM sys.database_files
WHERE type IN (0,1);
Рис. 3: Вывод кода в листинге 6
Мы можем также сжать физический журнал транзакций с помощью кода, приведенного в листинге 7. Перед сжатием проверьте, что у вас имеется резервная копия журнала транзакций. В условиях производства лучше сделать расписание создания бэкапов журнала, чтобы избежать неконтролируемого роста файла журнала транзакций и гарантировать сохранение данных. При сконфигурированной опции аварийного восстановления типа Log Shipping или AlwaysOn AG это уже гарантируется.
Вы можете обратиться к столбцу log_reuse_wait_desc представления каталога sys.databases, чтобы определить любые условия, которые препятствуют сжатию журнала транзакций. Обратите внимание на запрос этого столбца в листинге 3.
Такими условиями могут быть ожидание контрольной точки, ожидание резервирования журнала, идущие резервирование или восстановление, активная длительная транзакция, и подобные процессы в базе данных.
-- Листинг 7: Изменение модели восстановления
USE [tranlogexperiment]
GO
DBCC SHRINKFILE (N'Test1_log' , 0, TRUNCATEONLY)
GO
Рис.4: Используемое пространство после выполнения кода в листинге 7
Мы используем код в листинге 8 для создания резервной копии базы данных. В нашем конкретном случае мы должны сначала сделать полный бэкап, поскольку бэкапы журнала всегда ссылаются на полный бэкап. «Последний» полный бэкап начинает цепочку, когда происходит восстановление к заданному моменту времени.
-- Листинг 8: Создание резервной копии журнала транзакций
backup database tranlogexperiment to disk='tranlogexperiment.bkp';
backup log tranlogexperiment to disk='tranlogexperiment_log.trn';
При запуске базы данных в простом режиме восстановления журнал транзакций усекается при каждой контрольной точке. В этом режиме бэкапы журнала невозможны.
Местоположение файла журнала транзакций должно иметь надлежащий размер, чтобы удовлетворять длительным транзакциям, которые происходят время от времени. В противном случае журнал транзакций может заполнить все дисковое пространство. На рис.4 показано, что происходит с журналом транзакций, когда делается бэкап. Обратите внимание, что физический файл по-прежнему имеет размер 40Мб, но теперь у нас есть около 37Мб свободного пространства.
Рис.5: Журнал транзакций после создания резервной копии
Что происходит при простой модели восстановления?
Теперь давайте установим для базы данных tranlogexperiment простой режим восстановления.
-- Листинг 9: Изменение модели восстановления
use master
go
alter database tranlogexperiment set recovery simple;
Если выполнить код, представленный в листинге 4, мы получим несколько отличное поведение.
На рис.6 показан рост журнала транзакций при простом режиме восстановления, когда мы выполняем код из листинга 4. Размер физического файла журнала всего 15Мб. Это вдвое меньше, чем он был ранее при использовании полной модели восстановления. Также заметим, что свободное пространство составляет 11,5Мб.
Рис.6: Рост журнала после выполнения кода в листинге 4 при простом режиме восстановления
Означает ли это меньший рост журнала?
Нет. На рис.7 показано, что в процессе выполнения сессии SQL Server установил несколько контрольных точек. Это произвело усечение журнала и дало возможность транзакциям возобновлять рост журнала через определенные промежутки времени.
Рис.7: Захват контрольных точек при помощи расширенных событий
Заключение
Журнал транзакций, безусловно, важный компонент базы данных SQL Server. Он влияет на все, что требует восстановления или зависит от него — бэкапы, восстановление, аварийное восстановление и т.д.
В данной статье мы обсудили природу журнала транзакций, аспекты его надлежащего обслуживания и продемонстрировали поведение DML в базах данных при полной или простой моделях восстановления. Однако это далеко не все, что можно узнать о журнале транзакций. Начните с изучения документации.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Журнал транзакций
Каждая база данных SQL Server имеет журнал транзакций, в котором фиксируются все изменения данных, произведенные в каждой из транзакций.
Журнал транзакций — это важная составляющая базы данных. Если произошел сбой системы, вам потребуется этот журнал, чтобы вернуть базу данных в согласованное состояние.
Сведения об архитектуре и внутренних компонентах журнала транзакций см. в разделе Руководство по архитектуре журнала транзакций SQL Server и управлению им.
Удаляя или перемещая этот журнал, вы должны понимать все последствия этого действия.
Известные рабочие точки, от которых следует начинать применение журналов транзакций при восстановлении базы данных, создаются контрольными точками. Дополнительные сведения см. в статье Контрольные точки базы данных (SQL Server).
Операции, поддерживаемые журналом транзакций
Журнал транзакций поддерживает следующие операции:
- восстановление отдельных транзакций;
- Восстановление всех неполных транзакций при запуске SQL Server.
- накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя;
- поддержка репликации транзакций;
- Поддержка решений высокого уровня доступности и аварийного восстановления: группы доступности AlwaysOn, зеркало базы данных и доставка журналов.
Восстановление отдельных транзакций
Если приложение выполняет инструкцию ROLLBACK или ядро СУБД обнаруживает ошибку, например потерю связи с клиентом, записи журнала используются для отката изменений, сделанных незавершенной транзакцией.
Восстановление всех неполных транзакций при запуске SQL Server
Если на сервере происходит сбой, базы данных могут остаться в состоянии, когда часть изменений не переписана из буферного кэша в файлы данных, но в них имеются изменения, совершенные незаконченными транзакциями. Когда экземпляр SQL Server будет запущен, он выполнит восстановление каждой базы данных. Каждое изменение, записанное в журнале, которое, возможно, не было записано в файлы данных, накатывается. Чтобы сохранить целостность базы данных, будет также произведен откат каждой незавершенной транзакции, найденной в журнале транзакций. Дополнительные сведения см. в статье Обзор процессов восстановления (SQL Server).
Накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя
После потери оборудования или сбоя диска, затрагивающего файлы базы данных, можно восстановить базу данных на момент, предшествующий сбою. Сначала восстановите последнюю полную резервную копию и последнюю дифференциальную резервную копию базы данных, затем восстановите последующую серию резервных копий журнала транзакций до момента возникновения сбоя.
Поскольку восстанавливается каждая резервная копия журнала, ядро СУБД повторно применяет все модификации, записанные в журнале, для наката всех транзакций. После восстановления последней резервной копии журналов ядро СУБД затем использует сведения журнала для отката всех транзакций, которые не были завершены в этот момент. Дополнительные сведения см. в статье Обзор процессов восстановления (SQL Server).
Поддержка репликации транзакций
Агент чтения журнала следит за журналами транзакций всех баз данных, которые настроены для репликации транзакций, и копирует отмеченные для репликации транзакции из журнала транзакций в базу данных распространителя. Дополнительные сведения о репликации транзакций см. в разделе Как работает репликация транзакций.
Поддержка решений высокого уровня доступности и аварийного восстановления
Решения резервного сервера, группы доступности AlwaysOn, зеркало базы данных и доставка журналов используются в журнале транзакций.
В сценарии групп доступности AlwaysOn каждое обновление базы данных на первичном реплика немедленно воспроизводится в отдельных копиях базы данных во всех вторичных реплика. Основная реплика отправляет каждую запись журнала немедленно в вторичные реплика, которые применяют входящие записи журнала к базам данных доступности, постоянно перекатывая журнал вперед. Дополнительные сведения см. в разделе «Экземпляры отказоустойчивого кластера AlwaysOn».
В сценарии доставки журналов сервер-источник отправляет резервные копии журнала транзакций базы данных-источника в одно или несколько назначений. Каждый сервер-получатель восстанавливает резервные копии журналов в локальную базу данных-получатель. Дополнительные сведения см. в разделе Сведения о доставке журналов.
В сценарии зеркального отражения базы данных каждое изменение в базе данных (основной базе данных) немедленно воспроизводится в ее полной автономной копии (зеркальной базе данных). Экземпляр основного сервера немедленно отсылает каждую запись журнала в экземпляр зеркального сервера, который применяет входящие записи к зеркальной базе данных, путем ее непрерывного наката. Дополнительные сведения см. в разделе Зеркальное отображение базы данных.
Характеристики журнала транзакций
Характеристики журнала транзакций SQL Server ядро СУБД:
- Журнал транзакций выполнен как отдельный файл или набор файлов в базе данных. Кэш журналов управляется отдельно от кэша буфера для страниц данных, что приводит к простому, быстрому и надежному коду в ядро СУБД SQL Server. Дополнительные сведения см. в разделе Физическая архитектура журнала транзакций.
- Формат записей журнала и страниц не ограничивается форматом страниц данных.
- Журнал транзакций может располагаться в нескольких файлах. Вы можете задать для этих файлов автоматическое расширение, установив для журнала значение FILEGROWTH . Это снижает вероятность исчерпания пространства журнала транзакций, в то же самое время уменьшая административные издержки. Дополнительные сведения см. в разделе ALTER DATABASE (Transact-SQL) File и Filegroup Options.
- Механизм многократного использования пространства в файлах журналов действует быстро и оказывает минимальное влияние на пропускную способность транзакций.
Сведения об архитектуре и внутренних компонентах журнала транзакций см. в разделе Руководство по архитектуре журнала транзакций SQL Server и управлению им.
Усечение журнала транзакций
Процесс усечения журнала освобождает место в файле журнала для повторного использования журналом транзакций. Необходимо регулярно усекать журнал транзакций, чтобы предотвратить переполнение выделенного пространства. По ряду причин его усечение может быть отложено, поэтому очень важно следить за размером журнала. Некоторые операции можно выполнять с минимальным протоколированием, чтобы сократить их вклад в размер журнала транзакций.
Усечение журнала удаляет неактивные виртуальные файлы журналов (VLFs) из логического журнала транзакций базы данных SQL Server, освобождая место в логическом журнале для повторного использования журналом физических транзакций. Если усечение журнала транзакций не выполняется, со временем он заполняет все доступное место на диске, отведенное для файлов физического журнала.
В целях предотвращения этой проблемы усечение журнала выполняется автоматически после следующих событий, за исключением тех случаев, когда оно по каким-то причинам задерживается:
- В простой модели восстановления — после достижения контрольной точки.
- В модели полного восстановления или модели восстановления с массовым ведением журнала, если с предыдущей резервной копии произошла точка проверка point, усечение происходит после резервного копирования журнала (если только это резервная копия журнала только для копирования).
- При первом создании базы данных с помощью модели полного восстановления журнал транзакций будет повторно использоваться (аналогично базе данных простого восстановления) до тех пор, пока не будет создана полная резервная копия базы данных.
Дополнительные сведения см. в разделе «Факторы, которые могут отложить усечение журнала» далее в этой статье.
Усечение журнала не приводит к уменьшению размера физического файла журнала. Для уменьшения реального размера физического файла журнала необходимо выполнить его сжатие. Сведения о сжатии физического файла журнала см. в разделе Управление размером файла журнала транзакций.
Следует учитывать факторы, которые могут повлиять на задержку усечения журнала. Если после сжатия журнала снова потребуется дисковое пространство, размер журнала транзакций снова будет увеличиваться, что повлияет на производительность во время операций увеличения.
Факторы, которые могут вызвать задержку усечения журнала
Если записи журнала остаются активными в течение длительного времени, усечение журнала транзакций задерживается, и журнал транзакций может заполниться, как мы упоминание ранее в этой статье.
Сведения о том, как реагировать на полный журнал транзакций, см. в разделе «Устранение неполадок с полным журналом транзакций» (ошибка SQL Server 9002).
Действительно, усечение журнала может быть отложено по различным причинам. Чтобы узнать причину, препятствующую усечению журнала транзакций в конкретном случае, выполните запрос по столбцам log_reuse_wait и log_reuse_wait_desc представления каталога sys.database. В следующей таблице описаны значения этих столбцов.
| Значение столбца log_reuse_wait | Значение столбца log_reuse_wait_desc | Description |
|---|---|---|
| 0 | NOTHING | Сейчас есть как минимум один виртуальный файл журнала (VLF), доступный для повторного использования. |
| 1 | CHECKPOINT | С момента последнего усечения журнала не произошло проверка point, или голова журнала еще не перемещена за рамки виртуального файла журнала (VLF). (Все модели восстановления) |
Во время начала создания резервной копии журнала может существовать длительная транзакция. В этом случае, чтобы освободить пространство, может потребоваться создание другой резервной копии журнала. Длительные транзакции препятствуют усечению журнала во всех моделях восстановления, включая простую модель восстановления, при которой журнал транзакций обычно усечен в каждой автоматической проверка point.
Транзакция отложена. Отложенная транзакция — это активная транзакция, откат которой был заблокирован по причине недоступности какого-либо ресурса. Сведения о причинах отложенных транзакций и их переносе из отложенного состояния см. в разделе «Отложенные транзакции» (SQL Server).
Операции, для которых возможно минимальное протоколирование
Минимальное протоколирование — это протоколирование только информации, необходимой для восстановления транзакции без поддержки восстановления на момент времени. В этой статье определяются операции, которые минимально регистрируются в модели восстановления с массовым журналом (а также в простой модели восстановления, за исключением случаев, когда выполняется резервное копирование).
Минимальное протоколирование не поддерживается для оптимизированных для памяти таблиц.
В модели полного восстановлениявсе массовые операции полностью протоколируются. Однако для набора массовых операций можно использовать минимальное протоколирование, временно переключив базу данных на модель восстановления с неполным протоколированием во время массовых операций. Минимальное протоколирование более эффективно, чем полное, и снижает вероятность того, что во время массовой операции большого объема будет заполнено все доступное пространство журнала транзакций. Однако, если при включенном минимальном протоколировании база данных будет повреждена или потеряна, ее нельзя будет восстановить до точки сбоя.
Следующие операции, выполняемые с полным протоколированием в модели полного восстановления, осуществляются с минимальным протоколированием в простой модели восстановления и модели восстановления с неполным протоколированием:
- Операции массового импорта (bcp, BULK INSERTи INSERT. SELECT). Дополнительные сведения о том, когда массовый импорт в таблицу подлежит минимальному протоколированию, см. в разделе Prerequisites for Minimal Logging in Bulk Import.
Если включена репликация транзакций, операции BULK INSERT протоколируются полностью даже в модели восстановления с неполным протоколированием.
Если включена репликация транзакций, операции SELECT INTO протоколируются полностью даже в модели восстановления с неполным протоколированием.
- Частичные изменения типов данных с большими значениями с помощью предложения .WRITE инструкции UPDATE при вставке или добавлении новых данных. Минимальное ведение журнала не используется при обновлении существующих значений. Дополнительные сведения о типах данных больших значений см. в разделе «Типы данных» (Transact-SQL).
- ИнструкцииWRITETEXT и UPDATETEXT при вставке или добавлении новых данных в столбцы с типом данных text, ntext, и image . Минимальное ведение журнала не используется при обновлении существующих значений.
Предупреждение Инструкции WRITETEXT и UPDATETEXT являются устаревшими, поэтому старайтесь не использовать их в новых приложениях.
- ОперацииCREATE INDEX (включая индексированные представления).
- ОперацииALTER INDEX REBUILD или DBCC DBREINDEX.
Предупреждение Инструкция DBCC DBREINDEX является устаревшей. Не используйте ее в новых приложениях.
Примечание. Операции построения индекса используют минимальное ведение журнала, но могут быть отложены при одновременном выполнении резервного копирования. Эта задержка вызвана требованиями к синхронизации страниц буферного пула с минимальным протоколированием при использовании простой модели восстановления или модели восстановления с неполным протоколированием.
Связанные задачи
Управление журналом транзакций
- Управление размером файла журнала транзакций
- Устранение неполадок при переполнении журнала транзакций (ошибка SQL Server 9002)
Резервное копирование журнала транзакций (модель полного восстановления)
- Создание резервной копии журнала транзакций (SQL Server)
- Создание резервной копии журнала транзакций при повреждении базы данных (SQL Server)
Восстановление журнала транзакций (модель полного восстановления)
См. также
- Руководство по архитектуре журнала транзакций SQL Server и управлению им
- Управление устойчивостью транзакций
- Предварительные условия для минимального протоколирования массового импорта данных
- Резервное копирование и восстановление баз данных SQL Server
- Обзор процессов восстановления (SQL Server)
- Контрольные точки базы данных (SQL Server)
- Просмотр или изменение свойств базы данных
- Модели восстановления (SQL Server)
- Резервные копии журналов транзакций (SQL Server)
- sys.dm_db_log_info (Transact-SQL)
- sys.dm_db_log_space_usage (Transact-SQL)
Какая информация записывается в журнал транзакций
Каждая база данных SQL Server имеет как минимум два файла, с ней ассоциирующихся: один файл данных, в котором непосредственно хранятся данные и как минимум один файл журнала транзакций. Журнал транзакций это основной компонент системы управления базами данных (СУБД). Все изменения в базе данных записываются в журнал транзакций. Используя эту информацию, СУБД может определить какая транзакция какие изменения внесла в данные SQL Server.
Оператор CREATE DATABASE используется для создания базы данных Microsoft SQL Server. Опция этой команды LOG ON используется для определения журнала транзакций создаваемой базы данных. Впервые созданные данные помещаются в файл данных, а запись изменений этих данных помещается в файле журнала транзакций.
Как только делаются изменения в базе, журнал транзакций растет. Поскольку большинство изменений вносимых в базу, журналируются, Вам нужно будет отслеживать размер журнала транзакций, потому что, если данные постоянно меняются, журнал соответственно вырастает.
Каждая контрольная точка Microsoft SQL Server гарантирует что все записи в журнале и все модифицированные страницы данных корректно записаны на диск. Файл журнала транзакций используется Microsoft SQL Server в процессе операции восстановления базы данных, чтобы зафиксировать завершенные транзакции и откатить незавершенные. Информация, записывающаяся в журнал транзакций, включает:
- Время начала каждой транзакции;
- Изменения внутри каждой транзакции и информацию для их отката (для этого используются снимки страниц данных до, и после транзакции);
- Информация о распределении памяти для страниц БД (выделении и изъятии экстента);
- Информация о завершении или откате каждой транзакции.
Эти данные Microsoft SQL Server использует в целях повышения целостности данных. Журнал транзакций используется при старте SQL Server, для того чтобы отменить сделанные изменения и установить состояние базы данных на момент, предшествующий началу изменений.
При запуске SQL Server для каждой БД начинается процесс регенерации (recovery). SQL Server определяет те транзакции, которые необходимо откатить. Это происходит в том случае, когда неизвестно все ли изменения из кэша записаны на диск. Поскольку при выполнении контрольной точки все изменения сбрасываются на диск, то с нее и стартует процесс регенерации, который производит фиксацию транзакций на диск. Все изменения на страницах, сделанные до контрольной точки, уже записаны на диск, поэтому нет смысла для сброса их на диск еще раз и изменения, выполненные до контрольной точки, не берутся к рассмотрению.
При необходимости отката транзакции SQL Server копирует снимки страниц данных до изменений, сделанных с момента запуска оператора BEGIN TRANSACTION.
Вы можете использовать журнал транзакций при восстановлении базы данных. В этом случае журналируется фиксация транзакций. В процессе фиксации транзакций SQL Server сохраняет все сделанные изменения в базе данных на диске.
Журнал транзакций полезен для устранения ошибок в базе данных, ошибок транзакций и позволяет обеспечить целостность данных.
Некоторые операции не всегда журналируются
Microsoft SQL Server не выполняет журналирование в тех случаях, когда могут возникнуть проблему с нехваткой дискового пространства при быстром увеличении журнала транзакций.
Для некоторых операций, таких как CREATE INDEX, Microsoft SQL Server не ведет протоколирование для каждой новой страницы. Вместо этого SQL Server записывает достаточно информации, чтобы определить, как CREATE INDEX отработал, и принять решение о том фиксировать изменения или сделать откат.
Если опция базы данных select into/bulkcopy установлены в TRUE, Microsoft SQL Server не записывает в журнал транзакций информацию о следующих операциях: операции массового копирования, Select into, WRITETEXT и UPDATETEXT. Поскольку эти операции не регистрируются в журнале транзакций, то SQL Server не сможет использовать восстановление журнала транзакций для отмены этих операций.
Если же выполняется одно из этих действий, когда опции select into/bulkcopy установлены в TRUE, то необходимо убедиться в том что резервная копия содержала изменения, сделанные этими операциями, в случае если потребуется последующее восстановление.
Резервное копирование журнала транзакций
Для того чтобы повысить эффективность стратегии резервирования и восстановления БД, необходимо периодически делать резервные копии журнала транзакций. Создать резервную копию журнала транзакций можно с помощью команды BACKUP LOG. При использовании копирования журнала транзакций, при необходимости, базу данных можно восстановить на любой момент времени, содержащийся в копии журнала. Если Вы не резервируете журнал перед его усечением, то восстановить сможете только последнюю копию базы данных, все изменения прошедшие с этого времени будут потеряны.
После того как Microsoft SQL Server заканчивает резервное копирование журнала транзакций, он усекает его неактивную часть, тем самым, высвобождая место. SQL Server может повторно использовать высвобожденное место, т.к. журнал транзакций непрерывно растет и ему требуется свободное пространство. Активная часть журнала содержит изменения, которые были сделаны в базе и еще не зафиксированы на диске.
Microsoft SQL Server пытается запустить процесс контрольной точки всякий раз когда журнал транзакций заполняется более чем на 70 процентов, или при получении ошибки переполнения журнала транзакций, а также при останове SQL Server (если используется SHUTDOWN WITH NOWAIT) операция контрольной точки будет запущена для каждой базы данных. При включенной опции ‘trunc. log on chkpt.’ становится бесполезным выполнение резервного копирования журнала транзакций, поскольку информация о производимых изменениях постоянно уничтожается и неактивная часть журнала транзакций урезается каждый раз после выполнении процесса контрольной точки. По существу эта опция показывает, что Вы не сможете использовать журнал транзакций при восстановлении. Журнал транзакций необходим для отката изменений и в процессе регенерации при старте SQL Server. Используйте эту опцию только для тех систем, для которых не важны потери изменений, сделанных в течение всего дня, потому что в этом случае Вы сможете восстановить только последнюю копию базы данных, а сделанные позже изменения восстановить будет невозможно. Применяется это редко.
Если журнал транзакций урезается с помощью оператора BACKUP LOG , то нельзя делать его копию до тех пор, пока не будет создана полная копия базы данных или дифференциальная копия. Дифференциальная копия содержит в себе только те изменения, которые произошли с момента последней полной копии базы данных.
Также желательно избегать резервирования журнала транзакций после любых не журналируемых операций, которые произошли после последнего полного резервного копирования базы данных. Сделайте лучше полную копию базы данных или разностное резервное копирование.
И в заключении, при добавлении или удалении любого файла из базы данных Вы должны создать полную копию. Восстановить в этом случае базу данных на момент, предшествующий ее изменению, используя журнал транзакций, не удастся.
Изменение опций базы данных
Усечение журнала транзакций после запуска процесса контрольной точки может быть выполнено на уровне базы данных, используя хранимую процедуру sp_dboption, которая изменяет конфигурационные настройки базы. Например:
exec sp_dboption pubs ‘trunc. log on chkpt.’, ‘false’
Эта команда отменит усечение журнала транзакций для базы данных pubs. Чтобы увидеть список всех текущих настроек базы данных, можно просто запустить эту процедуру без дополнительных параметров. Например:
exec sp_dboption pubs
Также опции БД можно изменить в Enterprise Manager. Для впервые созданной базы данных наибольшая часть опций установлена в значение False. В Microsoft SQL Server Desktop edition, однако, опция усечения журнала транзакций в контрольной точке установлена в значение True. На практике это может и не создавать проблем с восстановлением данных, все зависит от схемы резервного копирования и восстановления.
Также Вы можете установить опцию усечения журнала транзакций после контрольной точки на серверах разработчиков прикладных программ, поскольку в этом случае не так важно сохранять каждую тестовую транзакцию.
Эта статья дает лишь сжатое представление о том, как использовать журнал транзакций Microsoft SQL Server. Тема резервного копирования и восстановления баз данных достаточно сложна и мы ее коснулись лишь только поверхностно. Главная задача этой статьи показать какое важное значение имеет журнал транзакций. Часто новые базы данных создаются с очень маленьким размером журнала транзакций и с использованием опции ‘trunc. log on chkpt.’. Эта опасная комбинация потому как в этом случае журнал транзакций нельзя будет использовать после сбоев оборудования или программных ошибок, а также ошибок системы. Убедитесь в том, что Ваши базы данных SQL Server надежно защищены, планируя и осуществляя резервное копирование журнала транзакций, а также продумав эффективный план восстановления.
SQL-Ex blog
Журнал транзакций — это файл, который имеет каждая база данных SQL Server. Его можно представить как журнал активности обновлений, которые происходят в базе данных. Журнал транзакций используется для поддержания целостности базы данных. Хранящаяся информация о транзакции может быть использована для её отката, если по каким-то причинам он не была успешно зафиксирована или произошел системный сбой. В этой статье я рассмотрю архитектуру журнала транзакций.
Принцип ACID
Журнал транзакций используется для поддержания целостного состояния базы данных. При выполнении транзакций они должны записываться полностью и точно в файлы данных базы данных SQL Server. Если они не на 100% завершены успешно, то всю транзакцию необходимо откатить, чтобы гарантировать содержание в базе данных только завершенных транзакций.
- Введенные в систему данные являются неточными или не отвечают правилам целостности данных.
- Произошел сбой движка базы данных в критичное время, когда не все обновления были зафиксированы на диске в файлах ДАННЫХ.
- Произошел системный сбой диска, который привел к тому, что только частичная информация транзакции была записана на диск.
Атомарность
Свойство атомарности означает, что на диск будет записана завершенная транзакция, или ничего из транзакции записано не будет. Т.е. реализуется подход «все или ничего». Простой пример работы атомарности можно показать на переводе денег банком при обработке письменного чека. Обработка обналичивания чека требует как дебетовой, так и кредитной операции. Дебетовая операция удаляет средства из аккаунта, с которым связан чек. Кредитная операция заносит средства на аккаунт, на который выписан чек. Для поддержания атомарности необходимо, чтобы были успешно завершены обе операции. Если кредитная или дебетовая операция терпит неудачу по какой-либо причине, то свойство атомарности гарантирует, что никакая операция выполнена не будет. Когда выполняется только часть транзакции реализуется откат всех операций, связанных с обработкой чека, для обеспечения атомарности.
Согласованность
Свойство согласованности гарантирует, что все данные, записанные в базу данных в транзакции отвечают всем ограничениям, триггерам и другим правилам базы данных или приложения. Если какая-либо часть транзакции не выполняет успешного обновления базы данных, то данные не будут отвечать правилам согласованности, а транзакцию следует прервать и выполнить откат для обеспечения согласованности данных в пределах базы данных.
Процесс обналичивания чеков требует успешного выполнения обеих, кредитной и дебетовой, операций. Если происходит только «дебетовая» обработка процесса обналичивания чека, то только 50% операций завершено. Без выполнения кредитной операции все правила обработки чека не завершились бы успешно, и «дебетовая» операция должна быть отменена, чтобы гарантировать, что данные в базе не содержат неполной информации. Свойство согласованности обеспечивает успешное выполнение всех частей транзакции, а если нет, то будет выполнен откат незавершенных транзакций.
Изоляция
Свойство изоляции связано с изоляцией незафиксированных изменений данных в одной транзакции от чтения или обновления их другой транзакцией. Свойство изоляции гарантирует, что новая транзакция не читает или не обновляет еще незафиксированные данные некоторой другой транзакции.
В примере процесса обналичивания чека принцип изоляции гарантирует, что вторая банковская транзакция не сможет получить доступ к аккаунту в середине транзакции обналичивания чека, прежде чем транзакция полностью не завершится успешно. Представьте финансовые последствия, если средства, связанные с процессом обналичивания чека, могут быть сняты до успешного завершения процесса обналичивания чека. Это может привести к ситуации перерасхода в случае отката процесса обналичивания чека.
SQL Server имеет несколько различных уровней изоляции, одни из которых обеспечивают большую или меньшую изоляцию, чем другие. По поводу различных уровней изоляции обратитесь к документации Microsoft.
Длительность
Свойство длительности подразумевает, что изменения данных будут постоянны в базе данных, если транзакция зафиксирована. Поддержка длительности означает, что изменения не исчезнут в результате сбоя системы, если транзакция была успешно зафиксирована. Т.е. SQL Server гарантирует, что коль скоро операция «дебет» и «кредит» в примере обналичивания чека были завершены и зафиксированы, изменения останутся, даже если ядро базы данных даст сбой. Без длительности изменения могут исчезнуть при сбое системы, что было бы нехорошо. При использовании принципов ACID и информации, хранящейся в журнале транзакций, ядро базы данных в состоянии поддерживать целостность информации в базах данных SQL Server.
Архитектура журнала транзакций
Журнал транзакций представляет собой последовательный файл, который содержит транзакции в процессе их обработки. Журнал транзакций имеет как логическую, так и физическую архитектуру. Логическая архитектура описывает работу транзакции с логической точки зрения. Физическая архитектура показывает, как журнал транзакций физически реализован и управляется ядром базы данных.
Логическая архитектура
Логически журнал транзакций можно представить как последовательный файл, который содержит набор записей, содержащих различные виды модификаций, выполненных в базе данных. При всяком изменении базы данных одна или более записей записываются в логический конец журнала транзакций. Записи либо описывают выполненную логическую операцию, либо содержат образы до и после изменения фактических данных. Образы «после» — это образы данных после модификации базы данных. Логическая операция и образы до и после используются не только для обновления базы данных, но и для отмены транзакции и/или восстановления базы данных в случае краха системы.
Каждая запись журнала идентифицируется регистрационным номером транзакции (LSN). LSN определяет порядок, к котором записи записываются в журнал. Это означает, что запись журнала с LSN = 1 должна была быть записана в журнал до записи журнала с значением LSN, равным 2.
Каждый LSN связывается с транзакцией и может быть активным или неактивным. Активный LSN связан с транзакцией, которая еще не была зафиксирована. Самый старый LSN (наименьший номер LSN) среди активных номеров называется минимальным номером LSN восстановления или MinLSN. На рис.1 показано логическое представление журнала транзакций, где желтым цветом выделены активные LSN.
Рис.1: Логическая архитектура
Физическая архитектура
Физически журнал транзакций состоит из одного или более файлов журнала транзакций. Файл журнала разбит на куски, которые называются виртуальными файлами журнала (VLF). Журнал транзакций может иметь несколько или большое число VLF, в зависимости от величины журнала транзакций и интенсивности его роста. Всякий раз, когда требуется увеличить журнал транзакций, создается дополнительный VLF и добавляется к цепочке VLF. Величина пространства диска, добавляемого к журналу транзакций при каждом событии увеличения его размера, будет определяться числом вновь созданных VLF.
Журнал транзакций можно рассматривать как циркулярный файл, состоящий из связанных в цепочку VLF. Записи журнала транзакций пишутся от начала в конец, а затем возвращаются и начинают запись с самого начала. На диаграмме рис.2 показан журнал транзакций с 4-мя различными файлами VLF, которые логически связаны в циклическую цепочку. Красная пунктирная линия представляет то, как выполняется запись в журнал транзакций, начиная с первого VLF к последнему, и, возвращаясь по кругу обратно, опять начинают запись с первого VLF.
Рис.2: Циркулярный файл журнала
Начальным файлом циркулярного журнала может быть любой из VLF в журнале транзакций. Когда SQL Server достигает конца последовательного файла журнала при добавлении записей в журнал транзакций, он делает круг и начинает писать в журнал от начала последовательного файла журнала до тех пор, пока конец файла журнала когда-нибудь не достигнет логического начала файла журнала, как показано на Рис.3.
Если конец файла журнала когда-нибудь достигнет начала логического файла, SQL Server прекратит запись в базу данных до тех пор, пока некоторые записи не будут удалены из файла журнала транзакций в результате его усечения, или физический файл не будет расширен. Если все VLF активны, когда конец журнала совпадет с началом, то файл журнала транзакций будет расширен, и будут созданы дополнительные VLF. Если некоторые VLF не являются активными при достижении концом журнала его начала, ядро базы данных усечет журнал, очищая неактивные VLF, чтобы освободить место для записи в журнал транзакций. Освобождение неактивных VLF делается автоматически, когда база данных находится в простом режиме восстановления (simple recovery mode ), но потребует резервирования журнала транзакций, если база данных использует полную модель восстановления или модель с неполным протоколированием.
Рис.3: Частично заполненный журнал транзакций
Усечение журнала
Файл журнала должен периодически усекаться, чтобы избежать переполнения. Процесс усечения очищает файлы VLF, которые содержат только те записи журнала, которые уже были зафиксированы. Процесс очистки VLF файлов стартует с VLF, находящегося в начале файла журнала, пока не достигнет VLF, который содержит минимальный номер восстановления (MinLSN). MinLSN — это самая старая запись в журнал, которая необходима для успешного отката самой старой незафиксированной транзакции. Прежде чем журнал может быть усечен, необходимо выполнить команду контрольной точки. На рис.4 показано, как полные VLF очищаются в процессе усечения, и после очистки сбрасывается начало журнала. На рис. 4 видно, что файл журнала начинается с VLF3, а MinLSN находится в VLF4 до усечения журнала. Когда происходит процесс усечения, очищаются только те VLF, которые содержат зафиксированные записи журнала между началом файла журнала и VLF, который содержит MinLSN. В моем примере очистился VLF3, и начало файла журнала было перепозиционировано на начало VLF4.
Рис.4: Усечение журнала транзакций
- После контрольной точки, когда база данных работает в простом режиме восстановления.
- После создания резервной копии журнала, если используется модель полного восстановления или с неполным протоколированием, при условии, что была выполнена контрольная точка после предыдущего бэкапа (более подробно о моделях восстановления смотрите статью Понимание моделей восстановления SQL Server).
Что делает контрольная точка?
Изменения в базе данных сначала записываются в памяти в буферный кэш в целях повышения производительности, и фиксируются на диске, когда происходит операция контрольной точки. Когда страница обновилась в памяти, но еще не записана на диск, её называют грязной страницей. При выполнении контрольной точки грязные страницы, которые содержат только зафиксированные транзакции, записываются на диск.
- Добавлены или удалены файлы базы данных с помощью ALTER DATABASE.
- Создается резервная копия базы данных.
- Создается снимок базы данных, неявно или явно для DBCC CHECKDB.
- Деятельность, требующая выключения базы данных. Например, AUTO_CLOSE включен, и закрыто последнее подключение к базе данных, или изменяется опция базы данных, которая требует перезапуска базы данных.
- Экземпляр SQL Server останавливается в результате остановки службы SQL Server (MSSQLSERVER). Это действие вызывает контрольную точку в каждой базе данных в экземпляре SQL Server.
- Перевод экземпляра отказоустойчивого кластера SQL Server в режим офлайн.
Архитектура журнала транзакций SQL Server
Журнал транзакций — это журнал активности обновлений базы данных. SQL Server поддерживает эту информацию об обновлении базы данных до тех пор, пока она не будет постоянно зафиксирована и записана на диск. Журнал также используется для отката некорректных или незавершенных транзакций, которые могут иметь место в результате проблем в приложении или системе. Его также можно использовать для наката базы данных к определенному моменту времени, если потребуется восстановление. Если не обслуживать журнал транзакций, он может переполниться. Чтобы избежать этого, необходимо периодически выполнять контрольные точки и резервные копии журнала транзакций. В качестве администратора баз данных вам необходимо понимать архитектуру журнала транзакций, как управлять размером и ростом журнала, и периодически выполнять резервирование журнала транзакций базы данных, которая не находится в простом режиме восстановления.