Урезание (Shrink) логов в SQL Server

Режем логи в SQL Server 2012

Логи транзакций в MS SQL имеют обыкновение разрастаться, что иногда может привести к окончанию места на диске. Чтобы этого не происходило, в SQL Server существует операция урезания логов (Truncate). Урезание логов производится автоматически, в зависимости от модели восстановления:

• В простой модели (Simple) — после достижения контрольной точки;
• В модели полного восстановления (Full) — после создания бэкапа логов, при условии что со времени предыдущего бэкапа была достигнута контрольная точка.

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

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

Для урезания лога открываем Management Studio, выбираем нужную базу, кликаем на ней правой клавишей мыши и в открывшемся контекстном меню выбираем пункт «Properties». Переходим на вкладку «Options» и изменяем модель восстановления базы (Recovery model) на Simple.

Затем в том же контекстном меню переходим в раздел Tasks -> Shrink -> Files. В поле File type выбираем Log, в поле File name указываем имя файла логов. В поле «Shrink action» выбираем «Reorganize pages before releasing unused space», задаем желаемый размер файла и жмем ОК.

Затем в том же контекстном меню переходим в раздел Tasks -> Shrink -> Files. В поле File type выбираем Log, в поле File name указываем имя файла логов. В поле «Shrink action» выбираем «Reorganize pages before releasing unused space», задаем желаемый размер файла и жмем ОК.

Настройка DPM 2012 для бэкапа MS SQL

Для управления виртуальными машинами в производственной среде я использую Virtual Machine Manager, причем развернутый в отказоустойчивой конфигурации. Поэтому я немного удивился, когда в один прекрасный момент VMM оказался недоступен. Причину удалось выяснить довольно быстро —  на диске, где располагалась база данных VMM, закончилось место. В результате база свалилась, а вместе с ней стал недоступен и VMM. Как оказалось, место занял разросшийся до неприличных размеров лог транзакций, что довольно странно, ведь база регулярно бэкапилась с помощью DPM 2012.

А теперь небольшое отступление для тех, кто (как и я) не очень близко знаком с MS SQL.

Движок MS SQL Database Engine делит каждый физический лог-файл на несколько виртуальных файлов журнала. Виртуальные журналы не имеют фиксированного размера, количество их на один физический файл также не ограничено и определяется динамически. При создании базы логический журнал начинается в начале физического файла, а новые записи добавляются в конец логического журнала, который по мере заполнения приближается к концу физического файла.

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

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

Таким образом, при регулярном резервном копировании занимаемое журналом место должно регулярно освобождаться для повторного использования. При отсутствии же регулярного бэкапа для модели полного восстановления возможны два варианта:

• Если для лог-файла не установлено ограничение на максимальный размер, то он продолжит увеличиваться до тех пор, пока остается свободное место на диске;
• Если же размер его ограничен, то по достижении максимального размера будет выдана ошибка.

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

Перейдем к DPM.

При настройке резервного копирования в свойствах группы есть возможность выбрать тип бэкапа — полный (Full Express Backup) или инкрементальный (Incremental backup). По умолчанию отмечен пункт «Just before a recovery point». Это означает, что будет делаться только полный бэкап, инкрементальный бэкап при этом не создается.

А теперь внимание. SQL производит усечение лога каждый раз при синхронизации DPM, т.е. при инкрементальном бэкапе. При создании полного бэкапа усечение не производится и лог продолжает беспрепятственно расти. Чтобы лог усекался, необходимо в поле «Synchronization frequency» выбрать пункт «Every» и указать частоту синхронизации (периодичность создания инкрементальных копий).

Чтобы не быть голословным, приведу пример. Вот так выглядел лог до настройки синхронизации, как видите он занимал почти 70Гб, из которых было свободно 0%.

А вот так ситуация изменилась после правильной настройки и создания бэкапа. Как видите, теперь свободно 99% из занимаемого лог-файлом места и можно сжать его до приемлемого размера.