Если разрастается журнал транзакций *_log.ldf для MS SQL
Бывает такое что файл «*_log.ldf» увеличивается до больших размеров. И тогда возникает вопрос, как его уменьшить?
Для этого необходимо выставить корректные настройки базы данных.
Шаг 1. Установить модель восстановления Простая (Simple). Правой кнопкой на базе — Свойства(Properties) — Параметры(Options) — 4-й сверху пункт Модель восстановления (Recovery model) — Простая(Simple) — OK.
Шаг 2. Выполнить сжатие( shrink ) лога транзакций. Правой кнопкой на базе — Задачи(Tasks) — Сжать(Shrink) — Файлы(Files) — установить Тип файла (File type) — Журнал(Log) — в Операция сжатия (Shrink action) — выбрать «Реорганизовать страницы, перед тем освободить неиспользуемое место» (Reorganize pages before releseasing unused space) — Сжать файл (Shrink file to) — указать приемлемый размер лога
Можно поставить 0, тогда он выставит для себя автоматически минимальный размер.
После этих манипуляций размер log файла уменьшится до минимума и не будет так сильно расти.
Рассмотрим, как избежать роста log-файла MS SQL.
Посмотрите, какой режим восстановления (Recovery) стоит на закладке Options в свойствах базы данных. Он бывает Simple или Full.
- Простой — требует наименьшего администрирования
- Полный — обеспечивает наилучшую возможность восстановления данных после сбоя
В режиме Full возможен рост журнала транзакций (LDF), поскольку изменения базы данных накапливаются в этом журнале.
Уменьшение журнала транзакций зависит от операции резервного копирования (backup): если не делать резервное копирование, то лог транзакций в режиме Full будет расти.
Обратите внимание на пункт контекстного меню «Shrink Database» (shrink — англ. усадка, усушка, уменьшение). Эта операция уменьшает размер базы данных путем «удаления неиспользуемых страниц» («remove unused pages»). В свойствах базы данных есть опция «Auto Shrink», которая активизирует автоматическое уменьшение базы, во время периодических проверок неиспользуемого места («during periodic checks for unused space»).
Режим восстановления базы данных:
Теперь рассмотрим подробно три режима восстановления базы данных:
Full (режим полного протоколирования) — в этом режиме максимальное количество операций записывается в журнал транзакций. Журнал транзакций автоматически не обрезается. Этот режим обеспечивает максимальные возможности восстановления (за счет снижения производительности). Только в этом режиме вы можете использовать зеркальное отображение баз данных и автоматическую доставку журналов (log shipping). Именно этот режим выбирается по умолчанию для пользовательских баз данных, поскольку он настроен для базы данных model. Если изменить режим восстановления для базы данных model, то для создаваемых баз данных по умолчанию будет выбираться новый режим.
Bulk-logged (режим неполного протоколирования) — это компромисс между требованиями производительности и возможностями восстановления. При использовании этого режима запись в журнал практически отключается (в терминологии Microsoft — проводится минимальное протоколирование) для операций следующих типов:
- массовой вставки (команды BULK I_nsert, S_elect INTO, загрузка средствами bcp и т. п.);
- вставка/изменение больших двоичных данных (text, ntext, image);
- операции по созданию, перестроению и удалению индексов.
Автоматическая перезапись журналов транзакций при этом не производится, работа с транзакциями, не включающими в себя перечисленные операции, производится как обычно.
При работе в этом режиме вы лишаетесь возможности использовать журнал транзакций для восстановления (при утрате файлов данных, на момент времени или на метку транзакции), если в нем была хотя бы одна запись о перечисленных ранее операциях. Microsoft рекомендует не использовать этот режим восстановления на постоянной основе, а переключаться в него из режима Full на время выполнения больших операций массовой вставки, а потом возвращаться обратно.
Simple (простая модель восстановления) — максимальный выигрыш в производительности и удобстве работы за счет возможностей восстановления. Минимально протоколируются те же операции, что и в режиме восстановления Bulk-logged, а кроме этого, журнал транзакций автоматически очищается (блоками, размер которых изначально равен 256 Кбайт, но при необходимости он может быть автоматически увеличен). В результате вы получаете максимальную производительность и возможность не думать о потенциальной нехватке места в журнале транзакций. Но в этом режиме использовать журнал транзакций для восстановления уже не удастся. Вы не сможете даже выполнить резервное копирование журнала транзакций: команда BACKUP LOG в этом режиме сразу вернет ошибку.
Какой же режим восстановления выбрать?
Microsoft (в своих учебных курсах) рекомендует для рабочих баз данных выбирать только режим Full. Однако из опыта проведения автором этих самых учебных курсов и общения со слушателями можно сказать, что очень многие опытные администраторы сознательно настраивают для своих баз данных режим восстановления Simple. Значительное повышение производительности при операциях массовой вставки и при работе с большими двоичными данными вполне оправдывает некоторое снижение возможностей резервного копирования и восстановления. Что важнее для вашей задачи — дополнительные возможности восстановления или максимальная производительность, решение за пользователем.
Урезание (Shrink) логов в SQL Server
Логи транзакций в 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 (сжатие), которая уменьшает физический размер лог-файла за счет удаления неактивных виртуальных файлов журнала. Поскольку при сжатии лог-файла удаляются только неактивные (т.е. не содержащие логического журнала) виртуальные файлы журнала, то сжатие невозможно до тех пор, пока процедура усечения не пометит один или несколько логических журналов как неактивные.
При настройке резервного копирования в свойствах группы есть возможность выбрать тип бэкапа — полный (Full Express Backup) или инкрементальный (Incremental backup). По умолчанию отмечен пункт «Just before a recovery point». Это означает, что будет делаться только полный бэкап, инкрементальный бэкап при этом не создается.
А теперь внимание. SQL производит усечение лога каждый раз при синхронизации DPM, т.е. при инкрементальном бэкапе. При создании полного бэкапа усечение не производится и лог продолжает беспрепятственно расти. Чтобы лог усекался, необходимо в поле «Synchronization frequency» выбрать пункт «Every» и указать частоту синхронизации (периодичность создания инкрементальных копий).
Чтобы не быть голословным, приведу пример. Вот так выглядел лог до настройки синхронизации, как видите он занимал почти 70Гб, из которых было свободно 0%.
А вот так ситуация изменилась после правильной настройки и создания бэкапа. Как видите, теперь свободно 99% из занимаемого лог-файлом места и можно сжать его до приемлемого размера.
Как уменьшить размер логов sql
Войти
Нет аккаунта? Зарегистрироваться
Авторизуясь в LiveJournal с помощью стороннего сервиса вы принимаете условия Пользовательского соглашения LiveJournal
как уменьшить размер лог-файлов транзакций SQL-сервера для базы данных
- Dec. 11th, 2014 at 8:24 PM
В нашу техподдержку часто приходит вопрос, как уменьшить размер лог-файлов транзакций SQL-сервера для базы данных FossDoc. Для решения этого вопроса выполните следующее:
1. Запустите SQL Server Management Studio.
2. Выберите базу данных FossDoc и выполните команду Properties из контекстного меню на данной БД:
3. В появившемся диалоге на странице Options установите значение поля Recovery Model в Simple . Нажмите ОК .
4. Снова выберите базу данных FossDoc и выполните команду Tasks/Shrink/database из контекстного меню на данной БД.
5. В появившемся диалоге нажмите ОК :
Размер лог-файла транзакций будет существенно уменьшен.
Как в MS SQL Server уменьшить размер логов
В процессе работы с базами данных MS SQL с полным логом транзаций через некоторое время этот лог разрастается до внушительных размеров и начинает занимать места больше, чем сама база данных. Есть несколько путей очистки этого лога.
1. Короткий и скорее нерекомендуемый путь
backup log [dbname] TO DISK='NUL' go DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY) go
2. Более длинный, но скорее более надёжный способ (из обсуждения)
USE dbname; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE dbname SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (2, 1); -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log) GO -- Reset the database recovery model. ALTER DATABASE dbname SET RECOVERY FULL; GO
PS: Естественно, экспериментировать с БД и обрезать логи необходимо после полного бэкапа БД заранее.