☰

Как может пространство журнала SQL Server использоваться более чем на 100 процентов?

tag-icon • tag-icon ms-sql
Как может пространство журнала SQL Server использоваться более чем на 100 процентов?

Мы видим ошибки приложения (исключения), которые сообщают журнал транзакций "заполнен из-за ACTIVE_TRANSACTION". Нет никаких ожидающих транзакций, сообщенных пользователем DBCC OPENTRAN.

Запуская, DBCC SQLPERF('logspace')я вижу, что размер журнала составляет всего 1,3 МБ, но используемое пространство журнала показывает 107,7%

Эта база данных настроена с файлом журнала Maxsize более 2 ТБ, начальный размер составляет 2 МБ, с Autogrowth, установленным на 10%. Модель восстановления установлена ​​на Simple.

Как может использоваться более 100% пространства журнала, и почему при таком большом количестве доступного пространства может быть сгенерировано исключение?

решение1

Найдите виновника(ов)

Ты мог бы бежатьsp_WhoIsActiveи посмотрите, кто что запускает, и просмотрите логику этих запущенных процессов/активных транзакций. Проверьте, можно ли оптимизировать какие-либо запросы T-SQL для более быстрого запуска или в качестве меньших транзакций, чтобы это быстрее освободило неиспользуемое свободное пространство в файле журнала для повторного использования.

Это возможно благодаря замыслу

Простая модель восстановления SQL Server

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

источник

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

После того, как место выделено для SIMPLEфайла журнала транзакций базы данных модели восстановления с помощьюнеиспользуемое свободное пространство журнала транзакцийилиза автоматический рост свободного пространства на уровне ОС, файл журнала транзакций сохранит новое пространство до тех пор, пока не будет выполнена операция сжатия файла, например DBCC SHRINKFILE (database_log, 2048).

Важный:Когда происходит операция сжатия файла, например DBCC SHRINKFILE (database_log, 2048), она только освобождает неиспользуемое пространство журнала в журнале транзакций как свободное пространство обратно в ОС. Активные запущенные транзакции, записанные в журнале транзакций, не будут освобождены во время операции сжатия файла.

Сжатие файла журнала

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

Устранить основную причину

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

  • Отслеживание первопричины

    Иногда первопричина не в долгой транзакции — например, кто-то настроил репликацию и не отменил ее должным образом. Начните с проверкиlog_reuse_wait_desc in sys.databases:

    SELECT name, log_reuse_wait_desc FROM sys.databases;

    Однако имейте в виду, что это лишь краткий обзор причин, по которым журнал не может быть сжат прямо СЕЙЧАС.

    Затем, если вы не найдете там ничего интересного, вы можетезаписать sp_WhoIsActive в таблицу чтобы поймать, когда люди делают BEGIN TRAN, и оставляют свою сессию открытой на несколько часов. Ищите длительные транзакции, поговорите с владельцем и посмотрите, могут ли они выполнять свою работу небольшими порциями вместо одной гигантской транзакции.

  • Метаданные пространства файла журнала

    DBCC SQLPERF(logspace)абсолютно функциональная команда, если вас интересует только потребление файлов журнала вашей базы данных. Она предоставляет совокупный размер для каждого файла журнала для каждой базы данных на экземпляре SQL Server, а также объем потребляемого пространства (в процентах от общего размера файла журнала). Недостатком является тот факт, что результаты представляют собой совокупность для базы данных. Если у вас несколько файлов журнала, результаты отображаются на уровне базы данных, а не на уровне файлов.

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

    Источник

Связанный контент