Журнал транзакций для базы данных переполнен: Переполнение Журнала транзакций для БД BASE

Как усечь журнал транзакций MSSQL Server?

Журнал транзакций во всех версиях Microsoft SQL Server  имеет тенденцию расти со временем. Иногда он может заполнить все свободное место на сервере. Чтобы избежать этого, в SQL Server есть операция усечения журнала транзакций.

Журналы транзакций SQL Server и модель восстановления базы данных

Журналы транзакций используются для записи всех транзакций перед передачей данных в файл базы данных. Файлы журнала транзакций необходимы для отката базы данных до предыдущего состояния. Как правило, в журнале хранится порядковый номер, тип изменения, выполненные операции и т. д. Скорее всего, вы можете использовать SQL Server и без журнала транзакций, но тогда в в случае сбоя возможна потеря базы данных.

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

DBCC LOGINFO

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

Файлы журнала усекаются автоматически, в зависимости от модели восстановления, используемой в настройках SQL Server:

• Простая модель восстановления – файлы журнала автоматически обрезаются после достижения контрольной точки (самый простой вариант, требующий администрирования базы данных). При использовании модели простого восстановления журнал транзакций очищается сразу после завершения транзакции.  В этом режиме вы можете откатить вашу базу данных только до времени полного резервного копирования базы данных.
• Модель полного восстановления – журнал транзакций не будет очищен, пока не будет завершено резервное копирование журнала транзакций. Этот режим обеспечивает наилучшую возможность восстановления данных после сбоя. В полном режиме журнал транзакций (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 с помощью этого руководства все предыдущие резервные копии, сделанные в модели полного восстановления, могут быть отброшены. Вот почему этот метод следует использовать только в критических случаях, когда невозможно быстро очистить дисковое пространство другими способами.

Как усечь журнал транзакций MSSQL Server?

Метки: MIcrosoft SQL    MSSQL    Windows

Быстро растет база MS SQL

Очень часто это связано с тем, что очень быстро увеличивается LOG-файл базы данных MS SQL.

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

Любая база данных MS SQL содержит файлы с двумя расширениями *.mdf и *.log.
MDF-файл — это файл с данными. Содержит сведения, необходимые для запуска базы данных, и ссылки на другие файлы в базе данных. Их может быть несколько.
LOG-файл — это файл журнала транзакций. Файлы журнала транзакций содержат сведения, используемые для восстановления базы данных. Для файлов журнала транзакций рекомендуется расширение LDF. LOG-файлов может быть несколько.

Справедливости ради, еще выделяют и NDF-файлы, но при работе в 1С они не используются (вторичные файлы данных, являются не обязательными).

Как это работает

Теперь рассмотрим как работает запись в БД MS SQL.

Сервер 1С:Предприятия записывает данные в mdf-файл(ы) и параллельно все транзакции связанные с изменением данных в mdf фиксируются в журнал транзакций или log-файл(ы). Причем если по какой-то причине в базе данных произойдет сбой, log-файл поможет нам восстановить данные практически на любой момент времени.

Важно понимать, что в самих log-файлах нет данных, там фиксируются ТОЛЬКО транзакции (действия). Грубо говоря запросы, которые изменяют данные в MDF. Упрощенно, это «тетрадь» куда записываются все изменения (добавления, изменения, удаления) в таблицах базы данных.

Все, конечно, гораздо сложнее, но тут мы приводим упрощенную схему.

Имея такую «тетрадочку» с логом транзакций можно «листать» изменения и добиться того, что система может «откатиться» на нужное время.

Почему растет LOG-файл (ldf)?

Понятное дело, что если записываются все изменения то лог-файл просто обязан расти. Всякие фоновые задания, которые пишут по одной записи в какой-нибудь регистр в 1С делают изменения в данных, а следовательно, растет размер лога. Причем, чем больше изменений, тем больше растет ldf-файл. А такая операция, как обновление информационной базы часто ведет вообще к огромному росту, так как при обновлении информационной базы происходит много изменений в данных и это все фиксируется.

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

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

Посмотреть на вашу модель можно открыв Microsoft SQL Server Managment Studio, щелкнув на нашу БД правой кнопкой:

Методы борьбы с размерами файла транзакций MS SQL

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

SHRINK (сжатие) лога транзакций

В простонародье это «шринк» файла. Это «обрезка» файла и удаление оттуда данных транзакций. Действительно, если журнал транзакций нам нужен только при возникновении сбоев, может имеет смысл настроить частое резервное копирование, а лог транзакций вообще отключить? Это возможно.

Шаг 1. Сжатие log-файла

Откроем Microsoft SQL Server Managment Studio и «сожмем» log-файл.

После этого откроется окно:

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

Шаг 2. Переключение на простую модель восстановления

Если вы хотите на корню решить вопрос с ростом логов, то вы можете переключить модель восстановления на простую (Simple). На самом первом скриншоте выше, переключите модель на простую и нажмите OK.

Так же возможно выполнения вот такого запроса:

USE [TestUIT] BACKUP LOG [TestUIT]  TO DISK='NULL'
GO
DBCC SHRINKFILE ([TestUIT_log], 1)
GO

Этот способ наладить работу с размером логов имеет как плюсы (быстро и навсегда решает проблему роста логов), так и минусы. Например, вы теряете возможность оперативно откатывать изменения.
Важно, что речь не о полных бэкапах, а именно когда речь идет о восстановлении по данным журнала транзакций. Как пример, бэкап был вчера, а сегодня после обеда, когда было внесено 50 документов, вы случайно очистили важный документ и хотите вернуть его. Это можно сделать с помощью логов.

Некоторые считают такой метод, не верным и отчасти это так, но если вы и раньше не пользовались восстановлением данных по данным журнала транзакций, то я не думаю, что это вообще проблема для вас 🙂

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

Кроме способа описанного выше в MS SQL есть возможность создавать резервные копии журнала транзакций. Это можно сделать из Microsoft SQL Server Managment Studio:

А следующим шагом:

Важно! Делая бэкап журнала транзакций мы усекаем его. MS SQL понимает, что копия журнала сделана, а значит можно уменьшить размер log-файла.

Это же самое можно выполнить запросом:

BACKUP LOG TestUIT
TO DISK = 'C:\Backups\Logs\TestUIT.TRN'
WITH STATS
GO

Плюсы второго вариант очевидны, вы всегда можете восстановить данные (надо вам сделать эксперименты самостоятельно с этим), написать скрипт, который может это сделать автоматически. При этом после каждого бэкапа размер журнала транзакций будем сокращен. Если вам не нужно, вы всегда можете использовать первый вариант и простую модель восстановления БД.

Вот такие дела, друзья.

Всем удачи и берегите ваш MS SQL!

Барилко Виталий

Основатель и директор по развитию Софтонит. Практикующий руководитель разработки. Эксперт в области автоматизации техподдержки

sql — Журнал транзакций для базы данных заполнен

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

У меня нет контроля над тем, как это выполняется.

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

Таким образом, процесс завершается с ошибкой "Журнал транзакций для базы данных "xxx" заполнен" .

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

Не знаю, что мне делать дальше. Процесс длится несколько часов, так что играть методом проб и ошибок непросто.

Есть идеи?

Если кому интересно, процесс организации импорта в Microsoft Dynamics CRM 4.0.

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

-=-=-=-=- ОБНОВЛЕНИЕ -=-=-=-=-

Спасибо всем за комментарии. Вот что заставило меня поверить, что журнал не будет расти из-за открытой транзакции:

Я получаю следующую ошибку. ..

 Организация импорта (имя = xxx, идентификатор = 560d04e7-98ed-e211-9759-0050569d6d39) не удалось с исключением:
System.Data.SqlClient.SqlException: журнал транзакций для базы данных «xxx» заполнен. Чтобы узнать, почему пространство в журнале нельзя использовать повторно, см. столбец log_reuse_wait_desc в sys.databases.
 

Итак, следуя этому совету, я перешел к столбцу « log_reuse_wait_desc в sys.databases », и он содержал значение « ACTIVE_TRANSACTION 9».0010".

Согласно Microsoft:
http://msdn.microsoft.com/en-us/library/ms345414(v=sql.105).aspx

Это означает следующее:

Транзакция активна (все модели восстановления).
• В начале резервного копирования журнала может существовать длительная транзакция. В этом случае для освобождения места может потребоваться еще одна резервная копия журнала. Дополнительные сведения см. в разделе «Длительные активные транзакции» далее в этом разделе.

• Транзакция отложена (только для SQL Server 2005 Enterprise Edition и более поздних версий). Отложенная транзакция фактически является активной транзакцией, откат которой заблокирован из-за недоступности какого-либо ресурса. Сведения о причинах отложенных транзакций и о том, как вывести их из отложенного состояния, см. в разделе Отложенные транзакции.

Я что-то неправильно понял?

-=-=-=- ОБНОВЛЕНИЕ 2 -=-=-=-

Только что запустил процесс с начальным размером файла журнала, установленным на 30 ГБ. Это займет пару часов.

-=-=-=- Final UPDATE -=-=-=-

На самом деле проблема была вызвана тем, что файл журнала занимает все доступное место на диске. В последней попытке я освободил 120 ГБ, и он все еще использовал все это и в конечном итоге потерпел неудачу.

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

Спасибо всем за ваш вклад.

SQL-сервер - журнал транзакций заполнен (из-за НИЧЕГО) ..

. но эта база данных находится в простом режиме восстановления

Я поддерживаю допотопное веб-приложение (скоро будет удалено), которое все еще использует «aspnetdb» для своей системы аутентификации . Я выполнял некоторую работу по подготовке к его выводу из эксплуатации в своей тестовой среде, когда обнаружил, что мой тестовый сервер жалуется на следующую ошибку:

.

Журнал транзакций для базы данных «aspnetdb» заполнен из-за «НИЧЕГО».

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

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

Это на SQL Server 2016, работающем в режиме совместимости с 2008, потому что aspnetdb устарел.

• SQL-сервер
• журнал транзакций
• aspnetdb

6

Понял, помощь получена от stackexchange.

https://dba.stackexchange.com/questions/241172/transaction-log-is-full-due-to-nothing-but-this-database-is-in-simple-recov?noredirect=1#comment475763_241172

Autogrowth был установлен на 0. К сожалению, нет возможности увидеть это в SSMS, потому что он скрывает такие настройки для простых баз данных в режиме восстановления.

Запрос, чтобы увидеть реальную ценность Autogrowth, спасибо @HandyD:

 SELECT
    db.name AS [База данных],
    mf.name AS [Файл],
    CASE mf.[type_desc]
        КОГДА «СТРОКИ», ТОГДА «Файл данных»
        КОГДА «ЖУРНАЛ», ТОГДА «Файл журнала»
    КОНЕЦ КАК [ТипФайла],
    CAST(mf.[size] AS BIGINT)*8/1024 AS [SizeMB],
    СЛУЧАЙ
        КОГДА mf.[max_size] = -1, ТО 'Неограниченно'
        КОГДА mf.[max_size] = 268435456 ТОГДА 'Неограниченно'
        ELSE CAST(mf.[max_size]*8/1024 AS NVARCHAR(25)) + 'MB'
    КОНЕЦ КАК [MaxSize],
    CASE [is_percent_growth]
        WHEN 0 THEN CONVERT(VARCHAR(6), CAST(mf.growth*8/1024 AS BIGINT)) + 'MB'
        WHEN 1 THEN CONVERT(VARCHAR(6), CAST(mf. growth AS BIGINT)) + '%'
    КОНЕЦ КАК [Прирост]
ИЗ sys.databases db
LEFT JOIN sys.master_files mf ON mf.database_id = db.database_id
где mf.name как «aspnetdb%»
 

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

 ИЗМЕНИТЬ БАЗУ ДАННЫХ aspnetdb ИЗМЕНИТЬ ФАЙЛ (
    ИМЯ = aspnetdb_log
    , РАЗМЕР = 1 ГБ
) -- это решает проблему
ИДТИ
ИЗМЕНИТЬ БАЗУ ДАННЫХ aspnetdb ИЗМЕНИТЬ ФАЙЛ (
    ИМЯ = aspnetdb_log
    , РАЗМЕР = 1025 МБ
    , МАКСИМАЛЬНЫЙ РАЗМЕР = НЕОГРАНИЧЕННЫЙ
    , РАЗМЕР ФАЙЛА = 10 МБ
) -- теперь у нас есть авторост
ИДТИ
ИСПОЛЬЗОВАТЬ aspnetdb
DBCC SHRINKFILE(aspnetdb_log,1) -- теперь мы можем сжать БД до разумного минимума, поскольку включено автоматическое увеличение.
ИДТИ
 

Даже в простом режиме восстановления можно получить полный журнал транзакций. Простой режим восстановления просто означает, что журнал транзакций усекается после каждой завершенной транзакции.

Журналу транзакций по-прежнему требуется место для размещения всех активных транзакций и всех транзакций, для которых выполняется откат.

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

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

2

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.