По материалам статьи Craig S. Mullins: Transaction Log Guidelines
Перевод: Ирины Наумовой

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

[В начало]