Журнал транзакций во всех версиях Microsoft SQL Server имеет тенденцию расти со временем. Иногда он может заполнить все свободное место на сервере. Чтобы избежать этого, в SQL Server есть операция усечения журнала транзакций.
Журналы транзакций SQL Server и модель восстановления базы данных
Журналы транзакций используются для записи всех транзакций перед передачей данных в файл базы данных. Файлы журнала транзакций необходимы для отката базы данных до предыдущего состояния. Как правило, в журнале хранится порядковый номер, тип изменения, выполненные операции и т. д. Скорее всего, вы можете использовать SQL Server и без журнала транзакций, но тогда в в случае сбоя возможна потеря базы данных.
Журнал транзакций состоит из небольших логических элементов, называемых VLF (Virtual Log File). Вы можете узнать их количество, выполнив следующий запрос в контексте базы данных SQL Server:
DBCC LOGINFO
Количество возвращаемых строк указывает на то, сколько виртуальных файлов сегментировано в журнале. В поле «Status» отображается текущее состояние сегмента. Значение 0 означает, что сегмент в настоящее время не занят и может использоваться. 2 означает, что сегмент используется. Если свободных сегментов нет и в настройках базы данных SQL Server разрешено увеличение журнала транзакций, он будет увеличен и будут созданы новые VLF. Если размер журнала транзакций фиксирован или на диске недостаточно места, все операции по изменению структуры базы данных или ее содержимого станут недоступными. Скорее всего, вы получите ошибку: Журнал транзакций для базы данных переполнен.
- Простая модель восстановления – файлы журнала автоматически обрезаются после достижения контрольной точки (самый простой вариант, требующий администрирования базы данных). При использовании модели простого восстановления журнал транзакций очищается сразу после завершения транзакции. В этом режиме вы можете откатить вашу базу данных только до времени полного резервного копирования базы данных.
- Модель полного восстановления – журнал транзакций не будет очищен, пока не будет завершено резервное копирование журнала транзакций. Этот режим обеспечивает наилучшую возможность восстановления данных после сбоя. В полном режиме журнал транзакций (LDF) может увеличиваться (поскольку изменения базы данных накапливаются в этом журнале). В модели полного восстановления все транзакции SQL записываются в файлы журнала на диске и сохраняются там до создания резервной копии. Хранение журналов позволяет вам при необходимости вернуться к более ранней копии базы данных, и вы можете выполнить восстановление для каждой транзакции.
- С неполным протоколированием – этот режим позволяет сократить занимаемое пространство используя минимальные настройки ведения журнала. Если журнал был поврежден или с момента создания последней резервной копии журналов выполнялись операции с неполным протоколированием, все изменения после этого резервного копирования необходимо внести повторно. Иначе результаты работы потеряны не будут.
Когда используется модель полного восстановления базы данных, необходимо регулярно выполнять резервное копирование журнала транзакций, в противном случае он будет чрезмерно увеличиваться до тех пор, пока не займет все место на диске, и SQL Server не начнет сообщать об ошибке.
Как обрезать журналы транзакций на MS SQL Server?
Мы получили вот такое сообщение:
Microsoft OLE DB Provider for SQL Server: Журнал транзакций для базы данных “buh” заполнен. Чтобы обнаружить причину, по которой место в журнале не может быть повторно использовано, обратитесь к столбцу log_reuse_wait_desc таблицы
sys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1
Это означает, что на диске, где хранится журнал транзакций SQL, недостаточно места, и SQL не может записать новые данные транзакции. В этом случае вы можете обрезать файлы журналов SQL вручную (с помощью графического интерфейса Management Studio).
Чтобы обрезать журналы транзакций SQL, запустите SQL Server Management Studio (SSMS), выберите нужную базу данных, щелкните ее правой кнопкой мыши и выберите «Свойства» в контекстном меню. Перейдите в Параметры и переключите модель восстановления базы .
Меняем модель восстановления с Полной на Простую
Затем в главном меню перейдите в раздел «Задачи» -> «Сжать» -> «Файлы» .
В поле Тип файла выберите Журнал, в поле Имя файла укажите имя файла журнала. В поле «Операции сжатия» выберите « Реорганизовать страницы, перед тем как освободить неиспользуемое пространство» , установите нужный размер файла и нажмите «OK».
Нажимаем «OK». После завершения операции обязательно измените режим восстановления базы данных обратно на Полный.
Это только один из способов быстрого уменьшения размера логов. Не самый лучший, но очень простой и эффективный.
Кроме того, имейте в виду, что при усечении журналов транзакций SQL с помощью этого руководства все предыдущие резервные копии, сделанные в модели полного восстановления, могут быть отброшены. Вот почему этот метод следует использовать только в критических случаях, когда невозможно быстро очистить дисковое пространство другими способами.
Резервная копия журнала транзакций – применяется только при полной модели восстановления и содержит копию журнала транзакций начиная с момента создания предыдущей копии.