SQL-Ex blog

В сущности, всякий раз, когда фиксируется транзакция, 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 ID=1;

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