Журнал транзакций SQL Server 2000

Автор: Ирина Наумова

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

1.Создание
2.Внутренняя структура
3.Механизм протоколирования
4.Усечение журнала транзакций
5.Полное и минимальное протоколирование
6.Дополнительные материалы

1. Создание

Журнал транзакций создается при создании базы данных. Указать расположение журнала транзакций можно в команде CREATE DATABASE, используя ключевое слово LOG ON, например:

CREATE DATABASE test ON (Name='test_data', FileName='C:\MSSQL\Data\Test_Data.mdf'), LOG ON (Name='test_log', FileName='D:\MSSQL\Log\Test_Log.ldf') GO

У базы данных всегда имеется как минимум один файл журнала транзакций, причем он создается, даже если не задан в инструкции CREATE DATABASE. При этом его имя генерируется автоматически, а размер задается равным 25% суммы всех файлов данных в базе.
При желании можно создать несколько файлов журнала транзакций, определив их сразу в инструкции CREATE DATABASE или добавив позже с помощью инструкции ALTER DATABASE.
Пример. Создадим базу данных с двумя журналами транзакций:

1. При использовании CREATE DATABASE:

ON (Name='test', FileName=' 'C:\MSSQL\Data\Test_Data.mdf'), LOG ON (NAME = test_log_1, FILENAME = 'D:\MSSQL\Log\Test_Log_1.ldf'), (NAME = test_log_2, FILENAME = 'E:\MSSQL\Log\Test_Log_2.ldf') GO

2. Используя ALTER TABLE

CREATE DATABASE test ON (Name='test', FileName='C:\MSSQL\Data\Test_Data.mdf'), LOG ON (NAME = test_log_1, FILENAME = ' D:\MSSQL\Log\Test_Log_1.ldf') GO ALTER DATABASE test ADD LOG FILE (NAME = test_log_2, FILENAME = ' E:\MSSQL\Log\Test_Log_2.ldf') GO

Для чего может понадобиться несколько журналов транзакций?
Во первых, может кончиться место на диске, где находится журнал и тогда, чтобы продолжить работу, нужно будет увеличить журнал, создав дополнительный файл на другом диске.
Во вторых, если разбить журнал на несколько дисков, он будет работать быстрее, т.к. у сервера есть механизм равномерного распределения нагрузки на файлы журнала.
Файлы журнала не могут объединяться в группы как файлы данных, они администрируются по отдельности.
В Enterprize Manager файлы журнала транзакций создаются и добавляются в окне Database Properties на вкладке Transaction Log.
Однако, создавая несколько журналов транзакций для одной базы данных, Вы должны учитывать особенности прикрепления базы с помощью системной хранимой процедуры sp_attach_db, которые были описаны в статье: " Метод быстрого усечения журнала транзакций и перевода БД в offline"
Стандартной рекомендацией, для повышения производительности сервера баз данных, является размещение журнала транзакций на отдельном физическом диске. Журнал является файлом последовательного доступа, и скорость работы с ним зависима от того, насколько оптимально перемещаются головки его диска. Когда к диску обращаются только процессы, обслуживающие журнал транзакций, перемещение головок вдоль поверхности диска будет самым оптимальным. Наличие других процессов, конкурирующих за диск с операциями журналирования, увеличивает количество позиционирований головок между секторами диска и этим значительно снижает производительность сервера баз данных.

[В начало]

2. Внутренняя структура

Для улучшения производительности и управляемости журнал транзакций физически разбивается на более мелкие блоки - виртуальные журналы (Virtual Log Files - VLF). Минимальный размер виртуального журнала составляет 256 Кбайт, тогда как размер самого журнала транзакций не может быть меньше 512 Кбайт (при этом он состоит из двух виртуальных журналов). С увеличением размера журнала транзакций, соответственно происходит увеличение количества и размера виртуальных журналов. Администратор не может регулировать размер и количество виртуальных журналов, они регулируются динамически самим сервером и здесь большое влияние оказывает шаг прироста самого журнала транзакций.
Этот параметр регулируется следующим образом:

a) В T-SQL опцией FILEGROWTH в операторе ALTER DATABASE:

ALTER DATABASE test MODIFY FILE (NAME = test_log_1, SIZE = 10MB, MAXSIZE = 25MB, FILEGROWTH = 1MB ) GO

b) Через Enterprise Manager в окне Database Properties на вкладке Transaction Log.
Приращение задается либо в мегабайтах (Mб, Kб, Гб, Tб), либо в процентах от текущего размера файла журнала, по умолчанию Мб. Если значение приращения не установлено, по умолчанию оно равняется 10% , а минимальное значение - 64 Кб.

Сервер старается поддерживать размер виртуальных журналов по возможности минимальным, поэтому следует внимательно планировать шаг прироста журнала транзакций.
Логически журнал транзакций представляет из себя последовательность записей, содержащих информацию, включающую время начала транзакции, изменения внутри каждой транзакции и информацию для их отката (для этого используются снимки страниц данных до, и после транзакции), информацию о выделении и изъятии экстента, информацию о завершении или откате транзакции. Каждой записи присваивается свой порядковый номер (Log Sequence Number - LSN) и ID транзакции, которой она принадлежит. LSN последовательно увеличивается, когда в журнал добавляется новая запись и не зависит от того, в каком месте файла журнала транзакций эта запись физически сохраняется.

[В начало]

3. Механизм протоколирования

У каждого экземпляра SQL Server имеется адресное пространство, состоящее из двух основных компонентов: буферного и не буферного пула. Буферный пул выделяет оперативную память порциями (или буферами) по 8 Кбайт и используется это пространство для хранения страниц данных и индексов, которые SQL Server считывает с жесткого диска; кэша журнала транзакций; планов исполнения запросов и хранимых процедур; системных конструкций, таких как таблица блокировок; а также для информации пользовательских процессов.
Второй компонент адресного пространства SQL Server представляет собой область памяти, которая зарезервирована, прежде всего, для компонентов исполняемого кода или для компонентов, требующих выделения больших объемов оперативной памяти порциями, превосходящими 8 Кбайт, эта область памяти может также содержать планы исполнения запросов и хранимых процедур, которым требуются большие объемы памяти.
Все изменения выполняются транзакцией в буферном кэше, и только потом сбрасываются на диск.
Доступ к страницам данных осуществляется с помощью менеджера буферов (Buffer Manager). Любое изменение данных менеджер буферов сначала записывает в журнал транзакций. Такое опережающее протоколирование обеспечивает целостность данных в случае сбоя системы. Запись в журнал транзакций выполняется в синхронном режиме: только после того как информация о транзакции будет записана в журнал транзакций и сохранена в нем на диске, рабочий процесс оповещается о сохранении транзакции и только тогда продолжает свою работу. А вот запись страниц данных может выполняться и в асинхронном режиме, т.е. выдав запрос на запись, процесс может продолжать свою работу, не дожидаясь окончания этой операции, ведь в случае сбоя транзакцию можно будет восстановить по журналу.

Рассмотрим порядок записи информации об изменениях в журнал транзакций.

Информация о транзакциях записывается последовательно во все виртуальные журналы, начиная с первого. После заполнения первого, заполняется второй и так далее. Повторное использование освобожденных виртуальных журналов происходит после заполнения последнего. Если свободных виртуальных журналов не осталось, происходит автоматическое увеличение файла журнала транзакций на величину шага прироста. В случае если возможность автоматического прироста размера журнала транзакций не реализована или на диске нет достаточно места для его увеличения, то сервер выдает сообщение об ошибке. Чтобы разрешить эту проблему можно вручную увеличить размер файла журнала транзакций или выполнить его усечение. Увеличить размер журнала можно в Enterprise Manager в окне свойств базы данных, на вкладке Transaction Log, или с помощью команды Alter Database. Усечение журнала рассмотрим позже, в главе 4.
Если для журнала транзакций используется несколько файлов, то информация о транзакциях записывается в них последовательно следующим образом: сначала заполняется первый файл, только после того как он полностью будет заполнен, начинается заполнение второго и т.д. После заполнения всех файлов, сервер начинает повторно использовать освободившиеся виртуальные журналы. Если свободных виртуальных журналов нет, то происходит автоматическое увеличение первого файла журнала транзакций. Когда первый файл журнала заполнен и нет свободных виртуальных журналов, происходит увеличение размера второго журнала и т.д. После увеличения всех журналов, при отсутствии свободных виртуальных журналов сервер снова увеличит размер первого файла журнала транзакций.
Запись изменений управляется потоком отложенной записи (lazy-writer thread), изменения ставятся в очередь и запись ведется постепенно, начиная с самых старых страниц.
Помимо потока отложенной записи, запись на диск также осуществляется потоком контрольной точки. Это операция синхронизирует физические файлы данных с текущим состоянием кэша с целью уменьшения времени восстановления после сбоя, поскольку уменьшается количество изменений, которые процесс восстановления, запускающийся при старте SQL Server, должен зафиксировать или отменить.
Процесс контрольной точки запускается в следующих случаях:

С помощью инструкции CHECKPOINT.
При изменении опции базы данных с помощью команды ALTER DATABASE.
Если SQL Server нормально завершил работу, при останове контрольная точка запускается для каждой базы данных. В тех случаях, если для останова используется команда SHUTDOWN WITH NOWAIT, процесс контрольной точки не запускается и при старте сервера процесс восстановления может занять больше времени.
Предполагаемое время восстановления превысило время, заданное параметром recovery interval системной хранимой процедуры sp_configure.
Когда установлена опция базы данных 'trunc. log on chkpt' и журнал транзакций заполнен на 70%. В этом случае сначала выполняется операция контрольной точки, затем урезается журнал транзакций, т.е. из него удаляются старые записи и освобождается место для новых.

Процесс контрольной точки включает в себя выполнение следующих операций:

Запись в журнал транзакций факта начала процесса контрольной точки.
Запись на диск всех "грязных страниц", т.е. тех страниц, которые были изменены, но не были записаны со времени выполнения последней контрольной точки.
Сохраняется последовательный номер записи (LSN), с которой будет начат процесс восстановления базы данных. (Процесс восстановления запускается при старте SQL Server, для того чтобы сохранить изменения, сделанные до момента сбоя, либо сделать откат.) Этот номер называется минимальным номером и принимает самое маленькое значение (MinLSN) из:
Сохраняется информация о выполнении процесса контрольной точки. Журнал транзакций хранит информацию обо всех контрольных точках, выполняемых в базе данных, связывая их в цепочку. Номер (LSN) самой первой записи этой цепочки хранится в загрузочной странице (boot page) базы данных.
Удаление записей обо всех зафиксированных транзакциях, если выбрана модель восстановления Simple.
Запись факта окончания процесса контрольной точки.

[В начало]

4. Усечение журнала транзакций

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

Рисунок 1.

На рисунке 2 показано как будет выглядеть журнал транзакций после его усечения. Виртуальные журналы 1 и 2 очищаются, поскольку все транзакции, информация о которых в них записана, уже окончены и изменения сохранены на диске, об этом говорит то, что Min LSN находится в третьем виртуальном журнале.

Рисунок 2.

Усечение не уменьшает размер физического файла журнала транзакций, оно только очищает виртуальные журналы для обеспечения возможности повторного их использования.
Журнал транзакций может быть сокращен только до границы виртуального журнала. Невозможно сократить его до размера, меньшего, чем размер виртуального журнала, даже если это место не используется или используется частично.
Активная часть журнала транзакций не может быть усечена. Активная часть - эта та часть журнала, которая необходима при восстановлении базы данных на определенный момент времени, а также информация необходимая для отката незавершенных транзакций в случае сбоя и перезапуска сервера. Номер первой записи активной части журнала транзакций и есть MinLSN.
При выборе модели восстановления Simple, неактивная часть освобождается при исполнении контрольной точки или при использовании опций NO_LOG и TRANCATE_ONLY оператора BACKUP LOG.
При выборе Full или Bulk-Logged неактивная часть освобождается при выполнении резервного копирования журнала транзакций (BACKUP LOG), поскольку эти записи могут потом понадобиться для восстановления базы данных на определенный момент времени.
В SQL Server 2000 опция truncate log on checkpoint существует только для обратной совместимости. Микрософт рекомендует использовать модель восстановления Simple вместо этой опции.
Также имеется еще несколько способов для усечения журнала, подробнее об этом можно прочитать по следующим ссылкам:
http://www.sql.ru/articles/mssql/01070901QuicklyShrinkingTransactionLog.shtml
http://www.sql.ru/articles/mssql/01062802ShrinkingTransactionLogSQLServer2K.shtml

[В начало]

5. Полное и минимальное протоколирование

Большинство операций в SQL Server, связанных с изменением данных, подробно протоколируются в журнале транзакций.
Однако, некоторые операции, в которых обрабатываются большие объемы данных, это такие операции как SELECT INTO, Bulk Copy Program (BCP), некоторые операций Data Transformation Services (DTS), где используется BCP, BULK INSERT, CREATE INDEX, WRITETEXT и UPDATETEXT, протоколируются минимально.
Способ протоколирования зависит от выбора модели восстановления для базы данных:

Simple

При выборе модели Simple журнал транзакций усекается при выполнении операции контрольной точки, поэтому резервирование журнала транзакций невозможно и восстановить базу данных можно только на момент последней полной или дифференциальной копии. Кроме того, разрешены не регистрируемые операции, такие, как массовое копирование.
Этот режим подходит только в том случае, если потеря изменений в случае сбоя не критична.
Более подробно вопросы отличия простой модели резервирования от полной описаны в статье Кэлен Дилани: "Модели восстановления изнутри".

Full

Если используется модель восстановления Full, то журналированию подлежит каждая запись, вставленная BCP или BULK INSERT. В журнале фиксируются все индексные операции, генерируемые командой CREATE INDEX, а также, максимально журналируется вставка (WRITETEXT) и изменение (UPDATETEXT) полей таких типов как: Image и Text.
В модели Full место в журнале транзакций высвобождается только когда будет сделана его резервная копия. Когда это происходит, все изменения, зарегистрированные в журнале транзакций, будут записаны в резервную копию, и занимаемое ими в журнале место освободится. Поэтому базы данных, эксплуатируемые в этом режиме, должны иметь достаточно места, доступного для журнала транзакций, чтобы хранить все транзакции, которые исполняются между каждым резервным копированием. Кроме того, не допускаются нерегистрируемые операции.

Bulk-Logged

При выборе модели Bulk-Logged протоколирование массовых операций минимально. В модели Bulk-Logged при выполнении массовой операции SQL Server регистрирует факт возникновения такой операции и фиксирует, в каких экстентах файлов базы данных эта операция отражена.
Модель Bulk-Logged введена для того, что бы повысить производительность операций массового копирования, при этом, размер журнала транзакций будет меньше, чем при выборе Full модели. В этой модели журналируется только изменение на уровне экстента, отражаемые в специальной битовой карте журнала. После каждого успешного резервного копирования эта карта обнуляется, а в резервную копию попадают все изменённые экстенты. Именно этим объясняется большой рост журнала транзакций, после проведения массовых операций. Такое минимальное резервирование делает также невозможным восстановление состояния базы данных на заданный момент времени, т.к. необходимо восстанавливать все копии журнала целиком.
Операции массового копирования не регистрируются в журнале транзакций при выполнении следующих условий:

Опция базы данных select into/bulkcopy установлена в true.
Целевая таблица не имеет индексов или, в случае наличия индексов она пустая на момент начала операции массового копирования.
Целевая таблица не должна участвовать в репликации.
У таблицы, в которую импортируются данные, не должно быть триггеров.
Для массовых операций должен использоваться уровень блокировки: TABLOCK (для утилиты BCP используется ключ -h).

[В начало]

6. Дополнительные материалы

01. Журнал транзакций для "Чайников"
02. Модели восстановления изнутри
03. Выбор модели восстановления SQL Server 2000
04. Метод быстрого усечения журнала транзакций и перевода БД в offline
05. Усечение transaction log в SQL Server 2000 с помощью команды DBCC SHRINKFILE
06. Усечение журнала транзакций в SQL Server 7.0
07. Причины заполнения журнала транзакций SQL серверов 4.2x, 6.0, 6.5, 7.0
08. Что делать, если журнал транзакций не очищается, даже после DUMP TRAN WITH NO_LOG
09. Шпаргалки для теста Микрософт №70-028 «Администрирование MS SQL Server 7.0»
10. Восстановление баз данных Microsoft SQL Server
11. Сокращение времени восстановления баз данных с помощью дифференциальных резервных копий
12. Восстановление файлов и резервное копирование filegroup
13. Как резервировать оставшиеся в Transaction Log записи, если база Master и файлы пользовательской базы повреждены
14. Добавление и удаление файла базы данных между резервированиями журнала
15. Проблемы резервного копирования и восстановления баз данных SQL Server

[В начало]