Я столкнулся с проблемой «переполнения журнала транзакций» с SQL Server 2008 R2.
Моя база данных включает 80 000 таблиц, которые содержат до 5 столбцов BIGINT, и для каждого столбца есть индекс.
Модель восстановления для базы данных задана как простая.
Схема работы с базой данных заключается в выполнении большого количества команд массовой вставки, обновления и удаления в течение примерно 50 часов, в то время как в данный момент времени за один и тот же период времени выполняется только 8 команд.
Файл журнала транзакций был настроен на автоматический рост на 10 процентов и был ограничен до 2 000 ГБ (это было определено по ошибке, поскольку размер диска составляет всего 500 ГБ).
Исключение «журнал транзакций переполнен» возникло, когда база данных выполняла описанные выше команды, и размер ее файла составлял 41 ГБ, а файл журнала - 270 ГБ, которые занимают все дисковое пространство.
Чтобы предотвратить повторение проблемы, мы установили максимальный размер файла журнала на 20 ГБ и удалили все транзакции в командах, описанных выше (поскольку использование транзакции не требовалось).
Но, к сожалению, мы снова столкнулись с этим исключением, когда размер файла журнала достиг 20 ГБ.
Возможно ли, что SQL-сервер не усекает файл журнала из-за того, что сервер всегда выполняет команды и, следовательно, нет ни одного момента, когда журнал транзакций не использовался?
В чем может быть причина такого поведения?
Журнал транзакций растет, вероятно, потому, что вы выполняли эти операции в одной транзакции, а не разбивали их на более мелкие транзакции. Например, если вы удалите все записи 1 ML, операция будет сначала вставлена в журнал транзакций, а затем зафиксирована в базе данных. Но если вы разделите удаление на более мелкие транзакции, скажем, по 10 000 записей каждая, журнал транзакций будет меньше, и вы будете в безопасности с доступным дисковым пространством.
Возможно ли, что у вас была одна транзакция, которая выполнялась до того, как вы начали этот запуск данных? Журналы транзакций записываются последовательно, и разделы файла можно повторно использовать только в том случае, если все транзакций в этом разделе завершены. Одна длительная транзакция (даже если она ничего не делает) может остановить повторное использование журнала.