Файл журнала транзакций sql server имеет расширение

Обновлено: 05.07.2024

Главные и дополнительные файлы баз данных в SQL Server 2005, применение неформатированных разделов, оптимальное размещение файлов баз данных и журналов транзакций

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

Вначале приведем немного общих сведений.

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

В каждой базе данных обязательно есть один главный ( primary ) файл. По умолчанию для него используется расширение mdf (хотя использовать именно такое расширение не обязательно — для любых файлов баз данных и журналов транзакций расширения могут быть любыми, а могут и отсутствовать). Удалять этот файл нельзя. Для базы данных можно создать и дополнительные файлы ( secondary ), для которых по умолчанию используется расширение ndf . Точно так же есть главный и дополнительные файлы у журналов транзакций, для них по умолчанию используется расширение ldf .

В принципе, база данных может вообще обходиться без файлов. Вся необходимая информация при этом будет храниться на неформатрированном диске. Такой вариант называется использованием неформатированных разделов ( raw partitions ). Однако, в отличие от связки Unix / Oracle , связка Windows / SQL Server ничего не выигрывает от применения неразмеченных разделов, и поэтому этот вариант используется редко.

А теперь подробнее остановимся на тех решениях, которые вам придется принимать при создании базы данных.

Первое решение — это где будут размещены файлы баз данных и журналов транзакций. По умолчанию и файлы баз данных, и файлы журналов помещаются в каталог С:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL\Data (где X — номер экземпляра SQL Server 2005). Такое размещение, конечно, не оптимально.

Идеальный вариант, с точки зрения размещения файлов баз данных, — поместить их на отдельный внешний аппаратный RAID -массив. Причем на этом RAID -массиве не должно быть ничего, кроме файлов базы данных, и, кроме того, на нем должно быть как минимум 50% пустого пространства. Такой вариант обеспечивает ряд преимуществ:

q RAID -массив (в зависимости от выбранного уровня) обеспечивает высокую производительность и отказоустойчивость;

q внешний RAID -массив делает процесс восстановления больших баз данных предельно простым и быстрым. Если на сервере возникли какие-то проблемы, а с файлами базы данных все в порядке, достаточно просто подключить RAID -массив к другому серверу и присоединить базу данных (см. разд. 4.2.3);

q если вы выберете внешний RAID -массив, который входит в список совместимого оборудования ( Hardware Compatibility List ) для кластеров Windows Server , то при необходимости вы сможете еще больше повысить отказоустойчивость за счет создания кластера;

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

Конечно, внешний RAID -массив — это идеальный вариант. Но на многих предприятиях денег на него может просто не быть. В этом случае рекомендуется, по крайней мере, использовать для файлов баз данных отдельный быстрый жесткий диск. Категорически не рекомендуется помещать на тот же диск, где находятся файлы баз данных, программные файлы операционной системы и SQL Server . Помните также, что на контроллерах доменов для разделов, на которые помещается база данных Active Directory (по умолчанию она находится в каталоге C:\Windows\NTDS ), отключается кэширование на запись: падение производительности может быть просто устрашающим.

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

Теперь — о размещении файлов журналов транзакций.

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

q второй RAID -массив;

q отдельный набор дисков на том же RAID -массиве, что и файлы баз данных;

q два обычных диска, которые зазеркалированы по отношению друг к другу;

q просто обычный отдельный диск;

q размещение на том же диске, на котором размещены файлы баз данных (этот вариант наименее желателен, но именно он по умолчанию выбирается SQL Server 2005 в расчете на однодисковые системы).

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

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

Конечно, размер файлов баз данных полностью зависит от задачи, для которой используется эта база данных. Однако у вас есть выбор — сразу создать большие файлы баз данных или настроить для них режим автоматического приращения, когда файлы при необходимости будут автоматически увеличиваться. Если такая возможность имеется, всегда нужно с самого начала создавать файлы максимального размера (или, по крайней мере, настраивать автоприращение сразу большими частями, например, в несколько Гбайт), даже несмотря на то, что в течение продолжительного времени значительная часть этих файлов использоваться не будет. Аргументация здесь проста — таким образом вы снижаете фрагментацию файлов баз данных, повышая производительность. По умолчанию для файлов баз данных настраивается худший вариант — автоприращение маленькими "порциями": 1 Мбайт для файлов базы данных и 10% от существующего размера для файлов журналов транзакций.

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

Настройку режима автоприращения при помощи графического интерфейса можно выполнить в окне New Database при создании новой базы данных или на вкладке General свойств базы данных в SQL Server Management Studio . Режим автоприращения устанавливается в соответствующей строке для каждого файла базы данных нажатием на кнопку в столбце Autogrowth (Автоматический рост) свойств данного файла (см. рис. 4.1).

Если вы уже создавали файлы баз данных большого размера (гигабайты и десятки гигабайт) в SQL Server предыдущих версий, то могли заметить, что их создание требует довольно длительного времени. Связано это было с тем, что SQL Server предыдущих версий "форматировал" пространство внутри создаваемых файлов данных, заполняя его двоичными нулями. В SQL Server 2005 появилась новая возможность, которая называется немедленной инициализацией файлов ( instant file initialization ). Она позволяет не заполнять файлы данных нулями, что резко сокращает время, требуемое для создания файлов баз данных или их увеличения. Однако эта возможность используется только при двух условиях:

q SQL Server работает под управлением операционной системы Windows Server 2003 или Windows XP ;

q учетная запись, от имени которой работает SQL Server , обладает специальной привилегией операционной системы SE_MANAGE_VOLUME_NAME (по умолчанию такая привилегия есть у встроенной группы Administrators ).

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

q первый фактор — в каком режиме используется база данных. Если это база данных OLTP (т. е. данные в ней изменяются постоянно, чаще всего пользователями при помощи клиентских приложений), к которой относится абсолютное большинство используемых на предприятиях баз данных, то Microsoft рекомендует устанавливать для журналов транзакций размер от 10 до 25% от общего размера файлов баз данных. Для баз данных Data Warehouse (архивные хранилища, которые в обычном режиме используются только на чтение и пополняются, как правило, средствами массовой загрузки данных или пакетами SSIS / DTS ) достаточно будет и нескольких процентов от объема файлов баз данных;

q второй фактор — какой режим восстановления настроен для базы данных. Режим восстановления ( recovery model ) настраивается на вкладке Options свойств базы данных, подробнее о нем будет рассказано в разд. 4.5. Режим восстановления S imple предъявляет минимальные требования к размеру файлов журнала (поскольку старые записи в журнале сразу же перезаписываются), а режим восстановления F ull требует файлов журнала намного большего размера;

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

Создание любой БД начинается с создания файла данных. Рассмотрим этот процесс в "Microsoft SQL Server 2008" на примере создания простой БД по учету успеваемости студентов.

Для начала необходимо запустить среду разработки " SQL Server Management Studio". Для этого в меню "Пуск" выбираем пункт "Программы\Microsoft SQL Server 2008\SQL Server Management Studio" ( рис. 4.1).

После запуска среды разработки появится окно подключения к серверу "Connect to Server" ( рис. 4.2).

В этом окне необходимо нажать кнопку "Connect"

Замечание: Если при установке "Microsoft SQL Server 2008" был задан логин и пароль подключения к серверу, то перед нажатием кнопки "Connect", в выпадающем списке "Authentication" нужно выбрать "SQL Server Authentication", а затем необходимо ввести заданные при установке логин и пароль .

После нажатия кнопки "Connect" появится окно среды разработки "SQL Server Management Studio" ( рис. 4.3).

Данное окно имеет следующую структуру ( рис. 4.3):

Оконное меню - содержит полный набор команд для управления сервером и выполнения различных операций.
Панель инструментов - содержит кнопки для выполнения наиболее часто производимых операций. Внешний вид данной панели зависит от выполняемой операции.
Панель "Object Explorer" - обозреватель объектов. Обозреватель объектов - это панель с древовидной структурой, отображающая все объекты сервера, а также позволяющая производить различные операции, как с самим сервером, так и с БД. Обозреватель объектов является основным инструментом для разработки БД.
Рабочая область. В рабочей области производятся все действия с БД, а также отображается ее содержимое.

Замечание: В обозревателе объектов сами объекты находятся в папках. Чтобы открыть папку необходимо щелкнуть по знаку "+" слева от изображения папки.

Теперь перейдем непосредственно к созданию файла данных. Для этого в обозревателе объектов щелкните ПКМ на папке "Databases" ( Базы данных ) ( рис. 4.3) и в появившемся меню выберите пункт "New Database" (Новая БД ). Появится окно настроек параметров файла данных новой БД "New Database" ( рис. 4.4). В левой части окна настроек имеется список "Select a page". Этот список позволяет переключаться между группами настроек.

Для начала настроим основные настройки "General". Для выбора основных настроек нужно просто щелкнуть мышью по пункту "General" в списке "Select a page". В правой части окна "New Database" появятся основные настройки ( рис. 4.4)

Рассмотрим их более подробно. В верхней части окна расположено два параметра: "Database name" (Имя БД ) и "Owner" (Владелец). Задайте параметр "Database name" равным "Students". Параметр "Owner" оставьте без изменений.

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

Logical Name - логическое имя файла данных и журнала транзакций. По этим именам будет происходить обращение к вышеприведенным файлам в БД. Можно заметить, что файл данных имеет то же имя что и БД, а имя файла журнала транзакций составлено из имени БД и суффикса "_log".
File Type - тип файла. Этот параметр показывает, является ли файл файлом данных или журналом транзакций.
Filegroup - группа файлов, показывает к какой группе файлов относится файл. Группы файлов настраиваются в группе настроек "Filegroups".
Initial Size (MB) - начальный размер файла данных и журнала транзакций в мегабайтах.
Autogrowth - автоувеличение размера файла. Как только файл заполняется информацией его размер автоматически увеличивается на величину, указанную в параметре "Autogrowth". Увеличение можно задавать как в мегабайтах так и в процентах. Здесь же можно задать максимальный размер файлов. Для изменения этого параметра надо нажать кнопку "…". В нашем случае ( рис. 4.4) размер файлов не ограничен. Файл данных увеличивается на 1 мегабайт, а файл журнала транзакций на 10%.
Path - путь к папке, где хранятся файлы. Для изменения этого параметра также надо нажать кнопку "…".
File Name - имена файлов. По умолчанию имена файлов аналогичны логическим именам. Однако файл данных имеет расширение "mdf", а файл журнала транзакций - расширение "ldf".

Замечание: Для добавления новых файлов данных или журналов транзакций используется кнопка "Add", а для удаления кнопка "Remove".

Каждая база данных SQL Server имеет как минимум два рабочих системных файла: файл данных и файл журнала. Файлы данных содержат данные и объекты, такие как таблицы, индексы, хранимые процедуры и представления. Файлы журнала содержат сведения, необходимые для восстановления всех транзакций в базе данных. Файлы данных могут быть объединены в файловые группы для удобства распределения и администрирования.

Файлы базы данных

SQL Server имеют три типа файлов.

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

Например, простая база данных с именем Sales включает один первичный файл, содержащий все данные и объекты, и один файл журнала, содержащий сведения журнала транзакций. Более сложная база данных с именем Orders может содержать один первичный файл и пять вторичных файлов. Данные и объекты внутри базы данных распределяются по всем шести файлам, а четыре файла журнала содержат сведения журнала транзакций.

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

Логические и физические имена файлов

Файлы SQL Server имеют два типа имен файлов.

logical_file_name: имя, используемое для ссылки на физический файл во всех инструкциях Transact-SQL. Логическое имя файла должно соответствовать правилам для идентификаторов SQL Server и быть уникальным среди логических имен файлов в соответствующей базе данных.

os_file_name: имя физического файла, включающее путь к каталогу. Оно должно соответствовать правилам для имен файлов операционной системы.

Дополнительные сведения об аргументах NAME и FILENAME см. в статье Параметры ALTER DATABASE ((Transact-SQL)) для файлов и файловых групп.

Файлы данных и файлы журналов SQL Server могут использоваться как в файловой системе FAT, так и в системе NTFS. В системах Windows рекомендуется использовать файловую систему NTFS по причинам ее большей безопасности.

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

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

Размер файла

Файлы SQL Server могут автоматически увеличиваться в размерах, превосходя первоначально заданные показатели. При определении файла пользователь может указывать требуемый шаг роста. Каждый раз при заполнении файла его размер увеличивается на указанный шаг роста. Если в файловой группе имеется несколько файлов, их автоматический рост начинается лишь по заполнении всех файлов.

Дополнительные сведения о страницах и их типах см. в разделе Руководство по архитектуре страниц и экстентов.

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

Дополнительные сведения об управлении файлами журнала транзакций см. в разделе Управление размером файла журнала транзакций.

Файлы моментального снимка базы данных

Вид файла, используемый для хранения копируемых во время записи данных моментального снимка базы данных, зависит от того, создается ли моментальный снимок пользователем или используется внутренними механизмами.

Данные моментального снимка базы данных, созданного пользователем, хранятся в одном или нескольких разреженных файлах. Технология разреженных файлов является свойством файловой системы NTFS. Изначально разреженный файл не содержит данных пользователя, и место на диске под него не выделяется. Общие сведения об использовании разреженных файлов в моментальных снимках базы данных и о том, как растут моментальные снимки базы данных, см. в разделе Просмотр размера разреженного файла моментального снимка базы данных.
Моментальные снимки базы данных могут использоваться внутренними механизмами при выполнении определенных команд DBCC. Эти команды включают DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC и DBCC CHECKFILEGROUP. Внутренним моментальным снимком базы данных используются разреженные дополнительные потоки данных исходных файлов базы данных. Подобно разреженным файлам, дополнительные потоки данных являются свойством файловой системы NTFS. Использование разреженных дополнительных потоков данных позволяет связать несколько расположений данных с одним файлом или папкой, не затрагивая при этом размер файла или статистику тома.

Файловые группы

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

Например, Data1.ndf , Data2.ndf и Data3.ndf могут быть созданы на трех дисках соответственно и отнесены к файловой группе fgroup1 . В этом случае можно создать таблицу на основе файловой группы fgroup1 . Запросы данных из таблицы будут распределены по трем дискам, и это улучшит производительность. Подобного улучшения производительности можно достичь и с помощью одного файла, созданного на чередующемся наборе дискового массива RAID. Тем не менее файлы и файловые группы позволяют без труда добавлять новые файлы на новые диски.

Все файлы данных хранятся в файловых группах, перечисленных в следующей таблице.

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

Файловая группа по умолчанию (первичная)

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

Файловая группа PRIMARY является группой по умолчанию, если только она не была изменена инструкцией ALTER DATABASE. Системные объекты и таблицы распределяются внутри первичной файловой группы, а не новой файловой группой по умолчанию.

Файловая группа данных, оптимизированных для памяти

Дополнительные сведения об оптимизированных для памяти файловых группах см. в разделе Оптимизированные для памяти файловые группы.

Файловая группа файлового потока

Дополнительные сведения о файловых группах файлового потока см. в статьях FILESTREAM и Создание базы данных с поддержкой FILESTREAM.

Пример файлов и файловых групп

В следующем примере создается база данных на основе экземпляра SQL Server. База данных содержит первичный файл данных, пользовательскую файловую группу и файл журнала. Первичный файл данных входит в состав первичной файловой группы, а пользовательская файловая группа состоит из двух вторичных файлов данных. Инструкция ALTER DATABASE придает пользовательской файловой группе статус файловой группы по умолчанию. Затем создается таблица, определяющая пользовательскую файловую группу. (В этом примере используется универсальный путь к c:\Program Files\Microsoft SQL Server\MSSQL.1 , чтобы не указывать версию SQL Server.)

Данная иллюстрация обобщает все вышесказанное (кроме данных файлового потока).

Стратегия заполнения файлов и файловых групп

В файловых группах для каждого файла используется стратегия пропорционального заполнения. При записи данных в файловую группу компонент Компонент SQL Server Database Engine записывает в каждый файл количество данных, пропорциональное свободному пространству этого файла, вместо записи всех данных в первый файл до его заполнения. Затем запись производится в следующий файл. Например, если в файле f1 свободно 100 МБ, а в файле f2 — 200 МБ, то в файл f1 записывается одна часть данных, а в файл f2 — две части, и так далее. Таким образом, оба файла будут заполнены примерно в одно и то же время, и достигается простейшее распределение данных между хранилищами.

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

Правила проектирования файлов и файловых групп

Для файлов и файловых групп действуют следующие правила:

файл или файловая группа не могут использоваться несколькими базами данных. Например, файлы sales.mdf и sales.ndf, содержащие данные и объекты базы данных sales, не могут использоваться никакой другой базой данных.
файл может быть элементом только одной файловой группы;
файлы журнала транзакций не могут входить ни в какие файловые группы.

Модели восстановления и журналы транзакций

SQL Server поддерживает три модели восстановления - полную (Full), простую (Simple) и с неполным протоколированием (Bulk Logged).

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

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

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

Структура журнала транзакций

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

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

Что вызывает рост журнала?

Давайте создадим небольшую базу данных, используя код в листинге 1. Файл данных вначале имеет размер 4Мб, файл журнала - 2Мб. Ваши промышленные базы данных никогда не будут иметь такой размер, особенно при популярной практике pre-allocation (предварительное выделение). Мы выбрали такой размер просто в демонстрационных целях.

В этой базе данных мы создаем единственную таблицу (листинг 2) для последующего выполнения операторов языка манипуляции данными (DML).

Выполнив код в листинге 3, проверим, что мы сделали.

Рис.1: Результаты выполнения кода в листинге 3, но до DML

Обратите внимание на столбец File size. Приступаем к наблюдению за ростом журнала транзакций при выполнении операторов INSERT и DELETE 100000 раз (листинг 4).

В листинге 4 выполняется вставка одной строки в таблицу txn_log с последующим её удалением; это действие повторяется 100000 раз.

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

Рис.2: Результаты выполнения кода из листинга 3 после операторов DML

Журнал транзакций вырос с 4Мб до 40Мб в результате этих манипуляций, хотя файл данных не изменился в размерах. Это ясно показывает, что размер журнала транзакций имеет мало общего с размером данных. На размер журнала оказывает влияние интенсивность, с которой происходит изменение (DML) базы данных.

Как обслуживать журнал транзакций?

Администраторы баз данных, которые обслуживают экземпляры SQL Server установок IaaS, должны регулярно делать резервные копии журналов транзакций. Полезно иметь конфигурации аварийного восстановления, такие как Log Shipping или AlwaysOn AG. Подобные конфигурации выполняют резервирование автоматически.

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

Код в листинге 6 показывает размер журнала транзакций и сколько в нем свободного пространства.

Рис. 3: Вывод кода в листинге 6

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

Вы можете обратиться к столбцу log_reuse_wait_desc представления каталога sys.databases, чтобы определить любые условия, которые препятствуют сжатию журнала транзакций. Обратите внимание на запрос этого столбца в листинге 3.

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

Рис.4: Используемое пространство после выполнения кода в листинге 7

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

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

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

Рис.5: Журнал транзакций после создания резервной копии

Что происходит при простой модели восстановления?

Теперь давайте установим для базы данных tranlogexperiment простой режим восстановления.

Если выполнить код, представленный в листинге 4, мы получим несколько отличное поведение.

На рис.6 показан рост журнала транзакций при простом режиме восстановления, когда мы выполняем код из листинга 4. Размер физического файла журнала всего 15Мб. Это вдвое меньше, чем он был ранее при использовании полной модели восстановления. Также заметим, что свободное пространство составляет 11,5Мб.

Рис.6: Рост журнала после выполнения кода в листинге 4 при простом режиме восстановления

Означает ли это меньший рост журнала?

Нет. На рис.7 показано, что в процессе выполнения сессии SQL Server установил несколько контрольных точек. Это произвело усечение журнала и дало возможность транзакциям возобновлять рост журнала через определенные промежутки времени.

Рис.7: Захват контрольных точек при помощи расширенных событий

Заключение

Журнал транзакций, безусловно, важный компонент базы данных SQL Server. Он влияет на все, что требует восстановления или зависит от него - бэкапы, восстановление, аварийное восстановление и т.д.

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

Читайте также: