Переполнение лога в SQL Server

Итак, вы столкнулись с тем, что транзакционный лог вашей базы данных в SQL Server вырос до очень больших размеров, либо заполнил собой весь диск. Не стоит переживать, подобная неурядица случается даже у опытных ДБА и легко устранима.

Если проблему нужно решить оперативно – переходите к пункту “Что делать”, но я бы рекомендовал для начала разобраться, почему такая ситуация возникает.

Кто виноват?

К сожалению, виноваты в данной ситуации либо вы сами, либо ваша невнимательность. Нужно понимать, что в отличии от большинства программ и даже операционной системы, так называемый журнал транзакций (Transaction Log) СУБД не носит привычный характер хранилища событий и ошибок. Данный файл принимает непосредственное участие в работе SQL Server и хранит выполняющиеся транзакции до момента их попадания в файл данных, а также уже выполненные транзакции вплоть до их резервного копирования либо репликации.

Транзакционный лог состоит из небольших логических элементов, которые называют VLF (Virtual Log File). Их количество вы можете узнать выполнив следующую команду в контексте базы:

DBCC LOGINFO

Количество возвращённых строк говорит о том, на сколько виртуальных файлов сегментирован лог. Также обратите внимание на атрибут Status, который отвечает за текущее состояние сегмента. Значение ноль говорит о том, что сегмент в данный момент не занят и может быть использован, двойка говорит о том, что сегмент уже имеет статус используемого. В случае, если свободных сегментов нет, но файлу транзакционного лога разрешён рост, он будет увеличен и образуются новые VLF. Если же увеличение запрещено, либо свободное место на диске отсутствует – любые операции по модификации структуры базы данных, либо её содержимого станут недоступны. Скорее всего вы получите ошибку наподобие следующей:

The transaction log for database is full due to ‘OLDEST_PAGE’.

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

  1. В первую очередь виной тому незавершённые транзакции, содержащие операции по модификации данных и структуры базы. Виной тому может служить их большой размер, либо взаимные блокировки. Примером может послужить ситуация, когда администратор обнаружил отсутствие регламентных заданий по обслуживанию баз данных и решил осуществить проверку целостности, либо перестройку всех индексов в условиях ограниченного дискового пространства на диске с журналами транзакций.
  2. Второй, и, возможно, наиболее массовой причиной описываемой проблемы служит отсутствие резервного копирования транзакционного лога в режимах восстановления базы BULK LOGGED и FULL. Иногда по неопытности, либо в спешке о настройке регулярных резервных копий вообще забывают. Начинающие администраторы баз данных часто полагают, что полная резервная копия баз данных также включает в себя и резервную копию транзакционного лога. К сожалению, это не так. В некоторых случаях плановые задания по резервному копированию журнала транзакций отключают на время выполнения технических работ и забывают их включить по их завершению. Также подобная проблема может возникнуть при смене первичной реплики в группе доступности, когда задачи агента резервного копирования не продублированы на всех серверах в кластере. Более банальной причиной может послужить недоступность хранилища, либо его переполненность. Настоятельно рекомендуется настроить уведомления о неудачном выполнении любых плановых задач агента а также настроить ежедневную отправку отчётов о дате и времени всех типов резервных копий в разрезе каждой базы данных включая системные.
  3. Третьей, более редкой, но тем не менее вполне встречающейся причиной проблем с переполнением лога транзакций служит незавершённая репликация. Это может быть транзакционная репликация, зеркалирование, либо группа доступности Always On. Если репликация транзакции по какой-либо причине не была осуществлена, виртуальный файл лога не может быть использован повторно даже в случае завершения самой транзакции и её резервного копирования. Подобная проблема обычно требует более детального анализа и определённого уровня подготовки администратора, но наиболее частой причиной служит недоступность сервера назначения, либо ограниченность его аппаратных ресурсов (ощутимо меньшая вычислительная мощность, либо недостаточный объём дискового пространства).
  4. Четвёртой, наиболее редкой и наименее очевидной причиной переполнения журнала транзакций служит проблема с отслеживании изменённых данных (Change Data Capture, CDC). Данная технология использует механизм logreader, который также используется и для репликации данных, благодаря чему выявление источника проблемы усложняется. Устранение проблем связанных с CDC требует высокого уровня подготовки администратора.

Что делать?

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

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

Внимание! Не следует перезапускать сервис SQL Server Database Engine либо перезагружать сервер на котором установлена СУБД без понимания, зачем вы это делаете. Это решение способно решить проблему переполнения лога только в случае незавершённой транзакции, но её откат при запуске системы может занять несколько часов, всё это время база данных будет недоступной.

Вариант 1 (Быстрый, Рискованный)

Самый быстрый вариант решения проблемы – это перевод базы данных в режим восстановления SIMPLE и выполнение операции SHRINK для файла транзакционного лога. Данный режим позволяет повторно использовать виртуальные файлы лога даже без резервного копирования. Это решение не позволит решить проблему, если переполнение лога произошло из-за одной или нескольких больших незавершённых транзакций, либо в случае его переполнения из-за проблем с транзакционной репликацией или change data capture. Также для перевода базы данных в режим SIMPLE потребуется отключить зеркалирование и вывести её из группы доступности Always On. В последнем случае можно порекомендовать не выводить все базы если их несколько, а сделать это только для одной базы, размер файла с данными которой является наименьшим, а размер файла транзакционного лога – одним из самых больших. Это позволит вернуть работоспособность системы и выиграть дополнительное время на диагностику.

(Опционально) Для отключения зеркалирования:

ALTER DATABASE [ИмяБазы] SET PARTNER OFF]

(Опционально) Для вывода базы из группы доступности:

ALTER AVAILABILITY GROUP [ИмяГруппы] REMOVE DATABASE [ИмяБазы]

В случае, если ИмяГруппы вам неизвестно, выполните следующий запрос:

SELECT group_name FROM sys.dm_hadr_availability_replica_cluster_nodes

К сожалению, групп доступности может быть несколько.

Для перевода базы в режим восстановления SIMPLE:

USE [master]
GO
ALTER DATABASE [ИмяБазы] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Для урезания файла журнала транзакций:

USE [ИмяБазы]
GO
DBCC SHRINKFILE (N'ЛогическоеИмяФайлаЛога' , 0, TRUNCATEONLY)
GO

Чаще всего ЛогическоеИмяФайлаЛога это ИмяБазы_log, однако как как при создании базы данных можно указать любое значение, можно выполнить следующий запрос:

USE [ИмяБазы]
GO
SELECT name FROM sys.master_files
WHERE database_id = db_id() AND type = 1

К минусам вышеописанного подхода можно отнести то, что вы уже не сможете сделать резервную копию данных журнала транзакций. В случае нахождения проблем за тот промежуток времени, когда резервные копии не делались (повреждённые страницы, удаление данных, модификация структуры), вы не сможете вернуться на определённую точку времени путём разворачивания резервной копии и повторения цепочки лога. Более того, даже если после вышеописанных операций немедленно вернуть базу данных в режим восстановления FULL вы не сможете создавать резервные копии журнала транзакций. К сожалению, операция по изменению типа восстановления базы данных разрывает последовательность цепочки транзакций и мы можем восстановить её только созданием полной резервной копии базы данных, что не всегда возможно сделать оперативно. Тем самым мы ставим под угрозу восстановление базы данных в случае сбоя непосредственно после решения проблемы с переполнением лога. Также в случае отключения зеркалирования и вывода базы данных из группы доступности мы рискуем отказоустойчивостью нашего решения. Более того, в случае, если данные читаются с вторичных реплик мы сознательно переводим всю нагрузку на один сервер, более того, если масштабирование нагрузки не предусматривалось и чтение производилось сугубо с сервера-реплики, мы прерываем работу приложений на неё подвязанных. Будь то сервер отчётности, либо ETL-процессы. Также, как и упоминалось выше, данный способ не избавит от проблемы с незавершёнными транзакциями, либо “застрявшей” транзакционной репликацией.

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

Вариант 2 (Быстрый, Кропотливый)

Добавление файла транзакционного лога

Вариант 3 (Относительно быстрый)

Бекап файла транзакционного лога в NUL

Вариант 4 (Медленный, правильный)

Создание полноценных резервных копий журнала транзакций

Вариант 5 (Если не помогло)

Лечим репликацию

Вариант 6 (Последний)

Выключаем и включаем Change Data Capture

Что дальше?

Почему и для кого написана эта статья