Увидев название статьи читатель может подумать, ну вот — снова начнется борьба «за чистоту русского языка». Так правильно, а так говорить не следует. Окончание пошло туда, а ударение — сюда. Тут запятая не нужна, а здесь она пропущена. Ну и все в таком духе.
Пусть подобные мысли не остановят его от тщательного изучения представленного материала, ибо ничего подобного он в нем не обнаружит. Автор данной статьи и данного блога вообще придерживается той точки зрения, что если ваша терминология понятна вам, вашим товарищам по developer team, и вашим коллегам на профессиональных форумах — вперед, называйте вы этот лог хоть «горшком». Поэтому хотя официальное название героя нашего повествования журнал транзакций (и это, несомненно, лучшее название для него) вы можете сокращать эту довольно длинную формулировку до лог-файла, или даже log-файла, или вообще ldf-файла — вас, несомненно, поймут. В очень редких случаях потребуется уточнить что вы ведете речь о том log, что именно журнал транзакций, а не о том error log что создается движком сервера при каждом его рестарте, или вообще не о логе событий OS Windows. Однако в 99% случаев подразумеваемый log-файл совершенно очевиден из контекста вашего сообщения и никаких уточнений не требуется.
Так вот, статья вовсе не будет учить вас правильно говорить по-русски, это задача иных блогов и их авторов. Вместо этого мы попробуем разобраться:
- что, собственно, есть тот самый log-файл, при условии что мы, как это уже понятно, будем говорить исключительно о журнале транзакций и ни о каких иных логах;
- почему он чрезвычайно важен (возможно даже важнее самих данных) и как так получается, что ни одна база данных существовать без него просто не в состоянии;
- какое влияние на лог оказывают контрольные точки, они же CHECKPOINT;
- каким образом лог вовлечен в процесс восстановления (restore) баз данных;
- каким образом лог вовлечен в процесс возобновления (recovery) баз данных;
- и есть ли разница между этими двумя процессами;
- какое отношение имеет лог к «чистым» а равно «грязным» страницам данных (data page);
- и почему любая страница данных в обязательном порядке «привязана» к некоторой точке лога и как эту точку узнать;
- как устроен файл лога «под капотом»;
- что означают VLF, LSN, MinLSN и прочие «страшные» аббревиатуры;
- почему в вопросах работы лога столько неверных представлений (мне больше нравится английский термин misconception — так короче) о «физике» данного процесса. Ни один другой компонент сервера не содержит столько «мифов и преданий» как ни в чем не повинный журнал транзакций;
- наконец, почему при наличии буквально тонн материалов по каждому из указанных пунктов (в том числе и в свободном доступе), вопросы о правильном обращении с лог-файлом поднимаются на SQL-форумах снова и снова, причем формулируются они таким образом что сомнений не остается — их авторы крайне поверхностно знакомы с вопросом, в лучшем случае знают самые основы и не более того. Кстати, то же самое наблюдается и на SQL-курсах проводимых «вживую»: даже реально продвинутые администраторы баз данных которым официальный microsoft-курс, в сущности, ничего нового сообщить не может, откровенно «плавают» когда на «повестку» учебного дня выносится вопрос чуть более сложный чем создание стандартного бэкапа лога. Ну вот не наблюдается у них (а уж тем более у менее искушенных администраторов) уверенного понимания механизма логирования транзакций SQL сервера, и все тут!
Ну и совсем финальное замечание: поскольку один из самых популярных из вышеупомянутых вопросов звучит как «растет лог файл — почему и что делать?» то, разумеется, нельзя было упустить момент и не вынести данный вопрос в заголовок статьи. Он так же, несомненно, будет нами разобран. Причем в ряде (и даже большинстве) случаев ответ на него будет парадоксальным, но от этого не менее верным — ничего не делать, а прекратить эту бессмысленную борьбу, как и предлагается в заглавии данного материала. Заинтригованы? Не согласны? Возмущены? Добро пожаловать, и — приятного чтения. 
Ведение журнала транзакций. Основы.
Сразу определимся — данная статья на очень-очень начинающих SQL-администраторов/SQL-разработчиков все же не рассчитана. Хотя автор постарается все вопросы и концепции излагать языком максимально простым и далеким от академического стиля всеми нами любимого SQL Server Books Online, сами эти вопросы/концепции будут весьма и весьма «advanced» уровня. Некоторые так и вообще можно не колеблясь отнести к категории «внутренности SQL сервера». Нужно ли нам (и вам!) столь «глубокое погружение» в нюансы? С точки зрения автора для разбираемого вопроса (лог-файл и процессы с ним связанные) — безусловно. Хотелось бы ограничится общими вещами («вот тут вызываете контекстное меню, а дальше все понятно...»), да вот практика показывает что не дают они целостной картины — увы. Чуть в сторону от «главной магистрали» и сразу «непонятки» начинаются. Поэтому вот именно в разбираемом механизме без «большой науки» (хоть автор и сам ее недолюбливает) никак не обойтись — НАДО! А поскольку написать статью уровня 100 и 500 одним и тем же текстом не представляется возможным, то договоримся так: вообще-то статья будет уровня 100, но в ее «критических» местах эта цифра будет возрастать до 500. 
Одним словом это ни разу не учебник «как сделать бэкап и успешно из него восстановится» или «как установить для базы нужную recovery model». Вот это, как и ряд сопутствующих вещей, вам должно быть уже известно до чтения дальнейшего материала. Наше же исследование, образно говоря, будет посвящено (помимо вопросов упомянутых во введении) пониманию того «как лог-файл влияет на бэкап и как второй влияет на первый», т.е. вещам гораздо более тонким и концептуальным, нежели механическая реализация готового плана восстановления базы в сбойной ситуации. Поэтому совсем уж базовые вопросы связанные с лог-файлом будут излагаться одним-двумя предложениями (максимум абзацами), просто что бы помочь вам вспомнить то, что, согласно нашим договоренностям, вам и так известно. Вот первая из таких «напоминалок» — как работает лог-файл (упрощенно, и в первом приближении, и с опусканием нюансов о которых речь впереди):
- клиент посылает серверу команду модификации данных (тот же UPDATE, для определенности; так же допустим, что фильтр WHERE указанной команды предписывает ей изменить значение ровно одной ячейки в ровно одной строке таблицы).
- страница данных содержащая целевую ячейку загружается в специальную область памяти сервера называемую буферным пулом (buffer pool), а более точно — в тот «отсек» указанного пула, который некоторые называют буферным кэшем (buffer cache), а некоторые называют его же кэшем данных (data cache). Это, кстати, снова к вопросу «как правильно говорить по-русски, а равно по-английски»? Очень просто — говорите так, что бы вас понимали... Так вот, на этом этапе нужная страница оказывается в RAM и обязательно в RAM. Разумеется, если в силу любых причин нужная страница к началу данного шага уже находится в RAM, то шаг просто пропускается. Зачем нам (и SQL Server-у тоже) лишний раз попусту дергать HDD, верно?
- если клиент не предварил команду UPDATE транзакцией явной, то сервер делает это за него. Иными словами сервер исполняет команду BEGIN TRAN, «нравится» это клиенту или нет. Многие знают, что сама команда BEGIN TRAN помечается в логе персональной записью, а именно как операция LOP_BEGIN_XACT в терминах одной из недокументированных «фич» SQL сервера — команды DBCC LOG (она более чем подробно рассматривается далее). И это верно. Но совсем немногие знают, что эта самая запись вносится в лог не в текущем шаге. А чуть погодя. До текущего момента (включая и его самого) лог не изменился ни на йоту, если, конечно, его не изменила транзакция иного клиента. Для простоты можно положить, что у нас в принципе система доступна лишь с единственного рабочего места, а значит любые конкурентные транзакции исключены, и с таким допущением мы можем утверждать, что пока лог гарантированно не менялся.
- дабы обеспечить транзакционную целостность предстоящих модификаций движок накладывает необходимые блокировки (locks). Данная тема (блокировки и их влияние на ход транзакций) лежит совершенно за границами изучаемого вами в настоящий момент материала, а поэтому автор будет предельно краток — необходимые блокировки будут иметь место. Для нашего вопроса самого этого факта достаточно.
- целевая ячейка изменяется в памяти и только в памяти (в том самом buffer или data cache). Никакое «прямое редактирование» содержимого ячейки на диске невозможно. Иными словами, в течении всего описываемого процесса (за исключением его последнего шага) файлы данных (.mdf/.ndf) принципиально не могут измениться.
- страница в буферном кэше немедленно помечается как «грязная» (dirty). Ее же версия на диске никак особо не помечается. Пометка версии в кэше обусловлена необходимостью создать такое окружение, в котором операция CHECKPOINT (см. далее) выполнится правильно, а, главное, максимально эффективно. Версия страницы на диске не нуждается ни в каких пометках, если ей суждено быть затертой новой информацией (той самой страницей из RAM) — так и будет, никакие пометки помочь/воспрепятствовать этому не могут.
- информация о проведенных изменениях вносится в лог файл. Причем на этом шаге вносится сразу две записи: операция LOP_BEGIN_XACT (да, только сейчас!) и сразу же операция LOP_MODIFY_ROW. Но вот они меняют именно файл лога, т.е. .ldf, и такие изменения происходят практически в синхронном режиме.
- сервер исполняет команду COMMIT TRAN, опять же, «с подачи» клиента или без таковой. Такая команда снова имеет персональную запись в .ldf файле (которая незамедлительно и добавляется к последнему), и проходит там как операция LOP_COMMIT_XACT в терминах команды DBCC LOG. Разумеется, тут вполне может оказаться и альтернатива — ROLLBACK TRAN, а в лог добавится, соответственно, операция LOP_ABORT_XACT. Однако мы примем первый вариант — клиент именно фиксирует (commit) произведенные изменения, а с «ролбэком» у нас разговор отдельный будет.
- движок сервера проверяет, что все операции подлежащие внесению в лог-файл, включая и финальные LOP_COMMIT_XACT/LOP_ABORT_XACT успешно внесены именно в .ldf-файл, т.е. на диск.
- и лишь после этого упомянутые в шаге 4-м блокировки снимаются, а клиент получает уведомление об успешности запрошенной им транзакции.
- некоторое время (и в отдельных случаях довольно продолжительное) измененная страница данных будет существовать в двух «версиях»: «версии диска» (.mdf/.ndf файл) и «RAM версии» (та самая dirty page). Это потому, что движок сервера вовсе не бросается со всех ног записывать первую же замеченную им «грязную» страницу на диск, а накапливает их некоторый объем, дабы впоследствии провести синхронизацию упомянутых версий для всех накопившихся страниц «одним движением». Понятно что направление такой синхронизации всегда и только RAM → диск и никогда обратно, т.к. диск ни в какой момент времени не содержит версию данных более новую или более «правильную».
- это самое «движение» имеет название контрольной точки, она же CHECKPOINT. Суть этой команды (а это, помимо всего, и инструкция языка T-SQL) очень проста — измененные (они же «грязные») страницы данных записываются из буферного кэша текущей базы данных на диск. Дальнейшие наши исследования неопровержимо покажут, что в предыдущем предложении после определения «очень проста» обязано следовать уточнение «как кажется на первый взгляд». Однако на текущий момент будем считать что действительно ничего «такого» в чек-поинте нет, ну сбросились «грязные» страницы на диск и сбросились. И — да, как и было сказано в шаге 5-м, файлы данных меняются лишь сейчас и только сейчас. Предыдущие шаги могут максимум считать их информацию, но ни в коем случае не изменить ее.
Полагаю, для большинства читателей можно было данную «шпаргалку» и не приводить — она им прекрасно известна. В крайнем случае ее можно было бы сократить до фразы «все изменения в данных должны быть надежно зафиксированы в файле лога, прежде чем транзакция получит статус успешно проведенной». Этого вполне было бы достаточно что бы все описанные пункты возникли перед их мысленным взором. А для самых искушенных читателей и вообще можно было ограничиться термином write-ahead logging, что бы они моментально поняли суть происходящего. НО! Могут ли читатели любой из этих 3-х групп сказать что им понятны все нюансы описанного процесса? Тех кто может это заявить я искренне поздравляю (хоть и предлагаю чтение не бросать, а лишний раз убедиться в своей компетентности), а остальным спешу сообщить: 90% оставшегося объема статьи, по сути своей, сводится к разбору указанных нюансов. А так же ответу на вопрос: «как мне самому посмотреть/убедиться, что данный нюанс работает именно так, а не иначе». Потому что понимание этих самых нюансов автоматически означает получение ответов на все вопросы заданные во введении, что и является целью данного материала.
Внутреннее устройство некоторых структур хранения информации SQL Server.
Для успешного понимания означенных нюансов нам нужно «снизойти» до понимания того, как хранятся данные и записи журнала транзакций на довольно-таки низком уровне, в части случаев буквально на уровне отдельных байтов. Автор сразу предупредил, что совсем просто — не будет, хотя он и постарается смягчить «шок» от столкновения с суровой реальностью физического функционирования SQL сервера. Кроме того, автор вовсе не призывает безоглядно верить всем последующим объяснениям. Напротив, он предлагает (и даже настаивает), что бы вы проверяли их все, не пропуская ни одного. Это полезно как с целью лучшего понимания данного материала, так и с целью его дальнейшего применения на ваших реальных базах данных. А стало быть, требуется не только «знание байтов», но и знание того откуда они, байты эти, берутся и как могут быть просмотрены и проанализированы.
Эти знания, а так же наши первые эксперименты в области работы лога мы будем проводить вот с такой несложной базой:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | USE master go CREATE DATABASE TstLog ON PRIMARY ( NAME = 'TstLog_Data', FILENAME = 'c:\sqlCMD.ru\TstLog_Data.mdf', SIZE = 5 MB, MAXSIZE = 5 MB ) LOG ON ( NAME = 'TstLog_Log', FILENAME = 'c:\sqlCMD.ru\TstLog_Log.ldf', SIZE = 1 MB, MAXSIZE = 1 MB ) GO ALTER DATABASE TstLog SET RECOVERY SIMPLE GO USE TstLog GO CREATE TABLE T1 (C1 INT IDENTITY, C2 INT NOT NULL, C3 VARCHAR(10) NOT NULL) GO INSERT INTO T1 VALUES (10, 'A') INSERT INTO T1 VALUES (20, 'K') INSERT INTO T1 VALUES (30, 'S') GO |
Ничего особенного, как видите (только не забудьте перед запуском скрипта создать фолдер c:\sqlCMD.ru\, либо корректируйте скрипт, если предложенное название папки или ее диск вас не устроят). Одна база, одна таблица, три колонки и столько же строк. База, отметьте себе, находится режиме восстановления simple. А так же учтите, что если последняя фраза вам не сказала ровным счетом ничего — определенно ставьте чтение статьи «на паузу» и выясняйте, а что же вы должны были узнать из нее. Как упоминал автор во введении, материал пишется в расчете на читателя знакомого с базовыми концепциями SQL Server, и, в частности, с концепцией данной — recovery model, она же режим (или модель) восстановления. Если же короткая инструкция SET RECOVERY SIMPLE сказала вам очень многое — отлично, так и должно быть, переходим к сути вопроса.