SQL Server. Ведение журнала и восстановление в SQL Server

Источник: oszone

Одними из самых непонятных составных частей SQL Server являются механизмы ведения журнала и восстановления. Складывается впечатление, что сам факт существования журнала транзакций и то, что неправильное управление этим журналом может приводить к неполадкам, ставит в тупик многих "невольных администраторов баз данных" (DBA). Почему журнал транзакций может неограниченно увеличиваться в размере? Почему в некоторых ситуациях требуется слишком много времени для того, чтобы база данных стала доступной после сбоя системы? Почему невозможно полностью отключить ведение журнала? Почему не удается надлежащим образом восстановить базу данных? Что из себя представляет журнал транзакций, и зачем он существует?

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

Что из себя представляет ведение журнала?

Ведение журнала и процедура восстановления присущи не только SQL Server - во все коммерческие системы управления реляционными базами данных (RDBMS) должны входить эти средства для обеспечения поддержки различных свойств ACID транзакций. Сокращение ACID обозначает Atomicity (атомарность), Consistency (согласованность), Isolation (изоляция) и Durability (устойчивость), являющиеся фундаментальными свойствами систем обработки транзакций (таких как RDBMS). Подробнее об этом можно прочитать в разделе "Свойства ACID" библиотеки MSDN.

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

Набор из одного или нескольких изменений можно сгруппировать (и, фактически, так всегда и делается) в транзакцию, обеспечивающую базовую единицу процесса внесения изменений (атомарность) в базу данных, когда речь идет о пользователях, разработчиках приложений и DBA. Транзакция завершается успешно (фиксация) или завершается аварийно или отменяется (откат). В первом случае гарантируется, что операции, формирующие транзакцию, отражены в базе данных. Во втором случае гарантируется, что операции не будут отражены в базе данных.

Транзакции в SQL Server бывают явными и неявными. При явной транзакции пользователь или приложение выдает оператор BEGIN TRANSACTION T-SQL, оповещающий о запуске данным сеансом группы связанных изменений. Явная транзакция успешно завершается, когда выдается оператор COMMIT TRANSACTION, оповещающий об успешном выполнении группы изменений. Если вместо него выдается оператор ROLLBACK TRANSACTION, все изменения, выполненные в данном сеансе с момента выдачи оператора BEGIN TRANSACTION, обращаются (откатываются), и транзакция отменяется. Откат транзакции может быть принудительно вызван внешним событием, например, нехваткой для базы данных свободного места на диске или выходом из строя сервера. Эти случаи будут рассмотрены далее.

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

Вам может показаться, что в этом нет необходимости, поскольку один оператор T-SQL не может генерировать большое число изменений в структурах хранилища базы данных, но рассмотрите, например, оператор ALTER INDEX REBUILD. Хотя этот оператор не может содержаться в явной транзакции, он может генерировать огромное число изменений в базе данных. Поэтому необходим механизм, обеспечивающий в случае неправильного развития событий (например, если отменяется выполнение оператора) надлежащее выполнение обращения изменений.

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

UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';

Выполняются следующие операции.

Страницы данных из SimpleTable считываются с диска в память (буферный пул), поэтому можно выполнять поиск соответствующих строк. Оказывается, что на трех страницах данных имеется пять строк, соответствующих предикату предложения WHERE.
Модуль хранилищ автоматически запускает неявную транзакцию.
Эти три страницы и пять строк данных блокируются для выполнения обновлений.
Изменения вносятся в пять записей данных на трех страницах данных, находящихся в памяти.
Изменения записываются также в записи журнала транзакций на диске.
Модуль хранилищ автоматически фиксирует эту неявную транзакцию.

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

Контрольные точки существуют по двум причинам - для группирования операций ввода/вывода с целью повышения производительности и для сокращения вермени, требуемого для восстановления после сбоя. В терминах производительности, если бы страница данных вытеснялясь на диск при каждом ее обновлении, число операций ввода/вывода в активно используемой системе могло бы превысить возможности подсистемы ввода/вывода. Разумнее с некоторой периодичностью записывать на диск "грязные" страницы (те, которые были изменены с момента их считывания с диска), чем записывать на диск страницы незамедлительно после внесения в них изменений. Чуть ниже я рассмотрю контрольные точки с точки зрения восстановления.

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

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

В чем заключается восстановление?

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

Большинство таких применений журнала влечет использование механизма, называемого восстановлением. Восстановление представляет собой процесс воспроизведения в базе данных изменений, описанных в записях журнала, или возврат базы данных к состоянию до этих изменений. Воспроизведение записей журнала называется фазой REDO (или наката) восстановления. Обращение изменений записей журнала называется фазой UNDO (или отката) восстановления. Другими словами, процедура восстановления обеспечиваете для транзакции и всех соответствующих записей журнала либо полное воспроизведение, либо полную отмену.

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

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

Каким образом процедура восстановления узнает, что следует делать? Все процедуры восстановления зависят от того факта, что каждая запись журнала помечена регистрационным номером транзакции в журнале (LSN). Регистрационный номер транзакции в журнале является возрастающим номером из трех частей, однозначно определяющим положение записи журнала в журнале транзакций. Все записи журнала в транзакции хранятся в последовательном порядке в журнале транзакций и содержат код транзакции и LSN предыдущей записи транзакции. Другими словами, каждая операция, зарегистрированная в качестве части транзакции, имеет обратную "ссылку" на операцию, непосредственно ей предшествующую.

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

Во время восстановления после сбоя механизм более сложен. Тот факт, что страницы базы данных не записываются на диск при фиксации транзакции, означает, что нет гарантии того, что набор страниц базы данных на диске точно отражает набор изменений, описанных в журнале транзакций - как для зафиксированных, так и для незафиксированных транзакций. Однако, имеется последний кусочек паззла, о котором я еще не упоминал - в заголовке страницы всех страниц базы данных имеется поле (96-байтная часть 8192-байтной страницы, содержащая метаданные с информацией о странице), содержащее LSN последней записи журнала, оказавшей влияние на страницу. Это дает возможность системе восстановления принять решение относительно конкретной записи журнала, которую необходимо восстановить.

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

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

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

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

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

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

Журнал транзакций представляет собой специальный файл, необходимой любой базе данных для надлежащего функционирования. В нем содержатся записи журнала, создаваемые в процессе ведения журнала, и журнал используется для повторного чтения этих записей во время восстановления (или любого другого, упоминавшегося ранее, использования процедуры ведения журнала). Так же, как пространство, занятое собственно записями журнала, транзакция в журнале транзакций резервирует также пространство для любых потенциальных записей журнала, которые потребовались бы в случае необходимости отменить транзакцию и выполнить откат. Этим объясняется поведение, которое можно наблюдать, когда, например, для транзакции, обновляющей 50 МБ данных в базе данных, на деле в журнале транзакций может потребоваться пространство в 100 МБ.

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

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

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

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

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

Итак, как осуществляется отбрасывание записей журнала, позволяющее занимаемое ими пространство использовать повторно? Запись журнала транзакций становится не нужной, если имеют место следующие факты.

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

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

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

Рис. 2 Журнал транзакций после усечения журнала

Активные VLF образуют логический журнал, являющийся частью журнала транзакций, содержащей активные записи журналов. Сама база данных знает, с какого места процедура восстановления после сбоя должна начинать чтение записей журнала в активной части журнала - с начала самой старой активной транзакции в журнале, MinLSN (она хранится в загрузочной странице базы данных).

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

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

Рис. 3 Циклический характер журнала транзакций

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

При возникновении контрольной точки в модели восстановления SIMPLE или в других моделях восстановления, если никогда не выполнялось полное резервное копирование. (При этом предполагается, что база данных, после вывода из модели SIMPLE, остается в модели восстановления псевдо-SIMPLE до тех пор, пока не будет выполнено полное резервное копирование базы данных.)
При завершении резервного копирования журнала.

Следует помнить, что усечение журнала может оказаться невозможным, поскольку существует много причин, по которым запись журнала должна оставаться активной. Если усечение журнала невозможно, файлы VLF не могут быть отброшены, и, в конечном счете, журнал должен увеличить свой размер (или должен быть добавлен еще один журнал транзакций). Чрезмерное разрастание журнала транзакций может привести к проблемам с производительностью вследствие эффекта, известного под названием фрагментация VLF. Устранение фрагментации VLF иногда может привести к впечатляющему повышению производительности действий, связанных с журналом.

Дополнительные сведения по этой теме см. в записи блога Кимберли Трипп (Kimberly Tripp) "8 Steps to Better Transaction Log Throughput" (8 шагов к повышению пропускной способности журнала транзакций). Кимберли обсуждает оптимальные методики, относящиеся к планированию объема журнала транзакций, усовершенствованию управления и повышению производительности - эту запись определенно стоит прочитать!

Усечению журнала могут воспрепятствовать две широко известные проблемы.

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

Полный список факторов и рекомендаций по определению причины, мешающей усечению журнала, см. в разделе "Factors that Can Delay Log Truncation" (Факторы, задерживающие усечение журнала) ресурса SQL Server Books Online (Электронные книги по SQL Server). Кроме этого, я создал демонстрационный видеоролик, в котором показаны последствия неуправляемого разрастания журнала транзакций и способ устранения фрагментации VLF - ознакомьтесь с этой видеотрансляцией (и предыдущими трансляциями по вопросам SQL) по адресу technetmagazine.com/videos.

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

Ни при каких обстоятельствах не следует удалять журнал транзакций, пытаться восстановить его с помощью недокументированных команд или просто обрезать его с помощью параметров NO_LOG или TRUNCATE_ONLY команды BACKUP LOG (которая удалена из SQL Server 2008). Эти параметры приведут либо к несогласованности с точки зрения транзакций (и, что более вероятно, к повреждению файла), либо лишат возможности надлежащего восстановления базы данных.

Подробнее о способах устранения неполадок в переполненном журнале транзакций ознакомьтесь с разделом "Troubleshooting a Full Transaction Log (Error 9002)" (Устранение неполадок в переполненном журнале транзакций (ошибка 9002)) электронной документации.

Модели восстановления

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

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

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

Но в модели восстановления BULK_LOGGED регистрируются только изменения распределения, что радикально сокращает число создаваемых записей журнала и, в свою очередь, сокращает потенциал разрастания журнала транзакций. Подробнее об операциях с минимальной регистрацией см. в разделе "Operations that Can Be Minimally Logged" (Операции, допускающие минимальную регистрацию) электронной документации.

Модель восстановления SIMPLE, фактически ведет себя с точки зрения ведения журнала так же, как и модель восстановления BULK_LOGGED, но имеет совершенно другую семантику усечения журнала транзакций. В модели восстановления SIMPLE невозможны резервные копии журнала, что означает, что журнал может быть усечен (если ничто не удерживает записи журнала в активном состоянии) при возникновении контрольной точки. Для каждой из этих моделей имеются доводы за и против, выраженные в терминах возможных (или требуемых) вариантов резервных копий и возможности восстановления состояния на разные моменты времени (эти вопросы будут обсуждаться позднее в этом году в другой статье).

Заключение

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

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

В моем блоге содержится намного больше сведений о журнале транзакций и факторах, оказывающих на него влияние - подробнее см. в "Shrinking the database before taking a backup" (Сжатие базы данных перед резервным копированием). Различные разделы электронной документации, относящиеся к журналу транзакций, также очень полезны - начиная с раздела Transaction Log Management (Управление журналом транзакций).

Ссылки по теме