Почему журнал транзакций продолжает расти или бежать из космоса?

Этот вопрос, по-видимому, является распространенным вопросом на большинстве форумов и по всему Интернету, он задается здесь во многих форматах, которые обычно звучат так:

  

В SQL Server -

     
  • По каким причинам журнал транзакций растет настолько большим?
  •   
  • Почему мой файл журнала настолько велик?
  •   
  • Каковы некоторые способы предотвращения возникновения этой проблемы?
  •   
  • Что мне делать, когда я нахожусь на пути с основной причиной и хочу поставить   мой файл журнала транзакций со здоровым размером?
  •   
235 голосов | спросил Mike Walsh 5 WedEurope/Moscow2012-12-05T06:11:22+04:00Europe/Moscow12bEurope/MoscowWed, 05 Dec 2012 06:11:22 +0400 2012, 06:11:22

4 ответа


296

Ответ более короткий:

Вероятно, у вас есть длительная работа с транзакциями (обслуживание индекса? Big batch delete или update?), или вы находитесь в режиме «по умолчанию» (более подробно о том, что подразумевается по умолчанию). Режим восстановления Full и не взяли резервную копию (или не принимают их достаточно часто).

Если это проблема с моделью восстановления, простым ответом может быть переход в режим восстановления Simple, если вам не нужно время восстановления времени и регулярные резервные копии журнала. Многие люди, тем не менее, делают этот ответ без понимания моделей восстановления. Читайте дальше, чтобы понять, почему это имеет значение, а затем решить, что вы делаете. Вы также можете просто запустить резервное копирование журналов и остаться в Full recovery.

Могут быть другие причины, но они наиболее распространены. Этот ответ начинает погружаться в наиболее распространенные две причины и дает вам некоторую справочную информацию о причинах и причинах причин, а также исследует некоторые другие причины.


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

Начальная причина 1/2: Непонимание моделей восстановления

( Быть в режиме полного восстановления и не принимать Резервные копии журнала - это наиболее распространенная причина - подавляющее большинство тех, кто испытывает эту проблему. )

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

В SQL Server существует три модели восстановления :

  • Полный,
  • Bulk-Logged и
  • Простой.

Мы проигнорируем Bulk-Logged, пока мы будем говорить, что это гибридная модель, и большинство людей, которые находятся в этой модели, существуют по какой-то причине и понимают модели восстановления.

Мы оба заботимся, и их замешательство является причиной большинства случаев, когда люди сталкиваются с этой проблемой: Simple и Full.

Прерывание: восстановление в целом

Прежде чем говорить о моделях восстановления: давайте поговорим о восстановлении в целом. Если вы хотите пойти еще глубже в эту тему, просто прочитайте блог Пола Рэндала и столько сообщений на нем, сколько хотите. Для этого вопроса:

  1. Восстановление при сбое /перезагрузке
    Одной из целей файла журнала транзакций является аварийное восстановление /перезагрузка . Для перемотки вперед и откат работы, которая была выполнена (перемотка вперед /назад) перед сбоем или перезапуском, и работа, которая была запущена, но не завершена после сбоя или перезагрузки (откат /отмена). Работа журнала транзакций заключается в том, что транзакция началась, но никогда не завершалась (откат или перезагрузка произошла до совершения транзакции). В этой ситуации задача журнала состоит в том, чтобы сказать «Эй, это никогда не закончилось, давайте откатимся» во время восстановления. Это также задача журнала, чтобы увидеть, что вы что-то закончили, и что вашему клиентскому приложению было сказано, что оно было закончено (даже если оно еще не затвердело для вашего файла данных) и сказать «Эй ... это действительно произошло, давайте переместим его вперед, давайте сделаем так, чтобы приложения считали, что это « после перезагрузки. Сейчас больше, но это основная цель.

  2. Точка восстановления времени
    Другая цель для файла журнала транзакций - предоставить нам возможность восстановления до момента времени из-за «oops» в базе данных или для гарантии точки восстановления в случае аппаратный сбой, связанный с данными и /или файлами журналов базы данных. Если этот журнал транзакций содержит записи о транзакциях, которые были запущены и завершены для восстановления, SQL Server может и затем использовать эту информацию, чтобы получить базу данных до того, как она была до того, как возникла проблема. Но это не всегда доступно для нас. Для этого мы должны иметь нашу базу данных в правой модели восстановления , и мы должны взять резервные копии журналов .

Модели восстановления

К моделям восстановления:

  • Простая модель восстановления
    Таким образом, с приведенным выше описанием, проще всего сначала поговорить о модели Simple Recovery. В этой модели вы сообщаете SQL Server: «Я в порядке с вами, используя файл журнала транзакций для сбоя и восстановления после восстановления ...» (У вас действительно нет выбора. Посмотрите свойства ACID , и это должно иметь смысл быстро.) "... но как только вы больше не нуждаетесь в этом для этой цели восстановления /перезагрузки, повторите попытку и повторное использованиефайл журнала. "

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

  • Полная модель восстановления
    С помощью Full Recovery вы сообщаете SQL Server, что вы хотите восстановить его в определенный момент времени, если файл журнала доступен или в определенный момент времени, который покрывается резервное копирование журнала. В этом случае, когда SQL Server достигает точки, где было бы безопасно обрезать файл журнала в Simple Recovery Model, это не будет сделано. Вместо этого Он позволяет файлу журнала продолжать расти и позволит ему продолжать расти, , пока вы не сделаете резервную копию журнала (или не закончите пространство на вашем диске с файлом журнала) при нормальных обстоятельствах.

Переключение с Simple на Full имеет Gotcha.

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

Но одно предостережение в отношении режима полного восстановления: если вы просто переключаетесь в режим Full Recovery, но никогда не принимаете начальное полное резервное копирование, SQL Server будет не почитайте свой запрос, чтобы быть в Full Recovery модели. Ваш журнал транзакций будет продолжать работать так, как он есть в Simple, пока вы не перейдете к модели полного восстановления и не получите свой первый Full Backup.

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

Итак, это самая распространенная причина неконтролируемого роста журнала? Ответ: В режиме полного восстановления без каких-либо резервных копий журнала.

Это означает все время для людей.

Почему такая распространенная ошибка?

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

Параметр начальной модели восстановления модели всегда Полная модель восстановления - до тех пор, пока кто-то не изменит это. Таким образом, вы можете сказать, что «Модель восстановления по умолчанию» - это Full. Многие люди не знают об этом и имеют свои базы данных в Full Recovery Model без резервных копий журналов и, следовательно, файл журнала транзакций намного больше, чем необходимо. Вот почему важно менять настройки по умолчанию, когда они не работают для вашей организации и ее потребностей)

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

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

Как узнать, какая частота резервного копирования журнала мне нужна?

Вам нужно учитывать частоту резервного копирования журнала с учетом двух факторов:

  1. Потребности в восстановлении . Это, надеюсь, будет первым. В случае, если накопитель в вашем журнале транзакций плохой, или вы получаете серьезное повреждение, которое влияет на резервное копирование журнала, сколько данных может быть потеряно? Если это число составляет не более 10-15 минут, вам необходимо сделать резервную копию журнала каждые 10-15 минут, в конце обсуждения.
  2. Рост журнала . Если ваша организация может потерять больше данных из-за возможности легко воссоздать этот день, у вас может быть хорошо, что резервная копия журнала будет реже менее 15 минут. Может быть, ваша организация в порядке с каждыми 4 часами. Но вы должны посмотреть, сколько транзакций вы создадите за 4 часа. Позволит ли журнал продолжать расти за эти четыре часа, делая слишком большой файл журнала? Означает ли это, что ваши резервные копии журналов занимают слишком много времени?

Основная причина 2/2: Длинные транзакции

( "Моя модель восстановления в порядке! Журнал все еще растет! )

Это также может быть причиной неконтролируемого и безудержного роста журнала. Независимо от модели восстановления, но она часто появляется как «Но я в Simple Recovery Model - почему мой журнал все еще растет?!

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

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

Это означает, что большое удаление, удаляющее миллионы строк в одном объявлении, является одной транзакцией, и журнал не может делать никаких усечений до тех пор, пока не будет выполнено все это удаление. В Full Recovery Model, это удаление регистрируется и может быть много записей журнала. То же самое с оптимизацией индекса в окнах обслуживания.Это также означает, что плохое управление транзакциями, а не просмотр и закрытие открытых транзакций может повредить вам и вашему файлу журнала.

Что я могу сделать для этих длительных транзакций?

Вы можете сохранить себя здесь:

  • Правильная настройка вашего файла журнала для учета наихудшего сценария - например, ваше обслуживание или известные крупные операции. И когда вы вырастите свой файл журнала, вы должны посмотреть на это руководство (и две ссылки, которые она посылает вам) Кимберли Трипп. Правильное определение размеров здесь очень критично.
  • Наблюдение за использованием транзакций. Не запускайте транзакцию на своем сервере приложений и не начинайте длительные разговоры с SQL Server и не оставляйте один слишком длинным.
  • Наблюдение за подразумеваемыми транзакциями в ваших операторах DML. Например: UPDATE TableName Set Col1 = 'Новое значение' - это транзакция. Я не размещал BEGIN TRAN, и мне не нужно, это еще одна транзакция, которая просто автоматически фиксируется, когда это делается. Поэтому, если вы выполняете операции с большим количеством строк, подумайте о том, чтобы довести эти операции до более управляемых блоков и дать время для восстановления журнала. Или рассмотрите правильный размер, чтобы справиться с этим. Или, возможно, изучите меняющиеся модели восстановления во время загрузки массовой загрузки.

Используются ли эти две причины для доставки журнала?

Короткий ответ: да. Более длинный ответ ниже.

Вопрос: «Я использую доставку журнала, поэтому мои резервные копии журнала автоматизированы ... Почему я все еще вижу рост журнала транзакций?»

Ответ: читайте дальше.

Что такое отправка журнала?

Доставка журналов - это то, на что это похоже - вы отправляете резервные копии журнала транзакций на другой сервер для целей DR. Существует некоторая инициализация, но после этого процесс довольно прост:

  • Задача резервного копирования журнала на одном сервере,
  • задание для копирования этой резервной копии журнала и
  • задание для восстановления без восстановления (либо NORECOVERY, либо STANDBY)) на целевом сервере.

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

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


Общее устранение неполадок с помощью кодов состояния

Есть другие причины, кроме этих двух, но они наиболее распространены. Независимо от причины: есть способ проанализировать причину этого необъяснимого роста журнала /отсутствия усечения и посмотреть, что это такое.

Запросив sys.databases в виде каталога вы можете увидеть информацию, описывающую причину, по которой ваш файл журнала может ждать при усечении /повторном использовании.

Существует столбец с именем log_reuse_wait с идентификатором поиска кода причины и столбцом log_reuse_wait_desc с описанием причины ожидания. Из ссылочных книг онлайн-статья - это большинство причин (те, которые вы, вероятно, увидите, и те, которые мы можем объяснить причины. Отсутствующие из них либо не используются, либо для внутреннего использования) с несколькими заметками о ожидании в курсив

  • 0 = Nothing Как это звучит ... Не следует ждать

  • 1 = Контрольная точка
    Ожидание появления контрольной точки. Это должно произойти, и вы должны быть в порядке - но есть некоторые случаи, чтобы искать здесь для более поздних ответов или изменений.

  • 2 = Резервное копирование журналов
    Ожидается резервное копирование журнала. Либо у вас их запланировано, и это произойдет в ближайшее время, либо у вас есть первая проблема, описанная здесь, и теперь вы знаете, как ее исправить.

  • 3 = Активное резервное копирование или восстановление
    Операция резервного копирования или восстановления выполняется в базе данных

  • 4 = Активная транзакция
    Существует активная транзакция, которая должна завершиться (в любом случае - ROLLBACK или COMMIT)), прежде чем резервная копия журнала будет скопирована. Это вторая причина, описанная в этом ответе.

  • 5 = Зеркалирование базы данных
    По какой-либо причине зеркало отстает или находится под некоторой задержкой в ​​ситуации зеркалирования с высокой производительностью, или по какой-то причине зеркальное отображение

  • 6 = Репликация Могут возникнуть проблемы с репликацией, которыевызывают это - как агент чтения журнала, не работает, база данных думает, что она помечена для репликации, которая больше не существует, и по другим причинам. Вы также можете увидеть эту причину, и это совершенно нормально, потому что вы смотрите на нужное время, так же, как транзакции потребляются читателем журнала

  • 7 = Создание моментального снимка базы данных
    Создается моментальный снимок базы данных, вы увидите это, если вы посмотрите на нужный момент при создании моментального снимка

  • 8 = Сканирование журналов
    Мне еще предстоит встретить проблему с этим бегом навсегда. Если вы посмотрите достаточно долго и достаточно часто, вы можете увидеть, как это происходит, но это не должно быть причиной чрезмерного роста журнала транзакций, который я видел.

  • 9 = Вторичная реплика AlwaysOn Availability Groups применяет записи журнала транзакций этой базы данных к соответствующей вторичной базе данных. О самом четком описании.

ответил Mike Walsh 5 WedEurope/Moscow2012-12-05T06:11:22+04:00Europe/Moscow12bEurope/MoscowWed, 05 Dec 2012 06:11:22 +0400 2012, 06:11:22
104

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

Будьте активны

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

Наихудшие возможные настройки здесь - рост 1 МБ или 10% роста. Довольно забавно, это стандартные значения для SQL Server (о которых я жаловался, и спросил для изменений безрезультатно ) - 1 МБ для файлов данных и 10% для файлов журналов. Первый из них слишком мал в этот день и в возрасте, а последний приводит к более длительным и продолжительным событиям каждый раз (скажем, ваш файл журнала 500 МБ, первый рост - 50 МБ, следующий рост - 55 МБ, следующий рост - 60,5 МБ , и т. д. - и при медленном вводе-выводе, поверьте, вы действительно заметите эту кривую).

Дальнейшее чтение

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

ответил Aaron Bertrand 18 AM00000050000000131 2013, 05:13:01
23

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

Он не показывает реорганизацию индекса, но он показывает все DML и различные события DDL: ALTER, CREATE, DROP, включение триггера /disable, grant /revoke, переименование объекта.

 ApexSQLLogProject.temp - ApexSQL.log

Отказ от ответственности: я работаю для ApexSQL в качестве инженера поддержки

ответил Milena Petrovic 31 J000000Thursday14 2014, 00:50:20
1

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

â € ¢ По каким причинам журнал транзакций растет настолько большим?

  1. Длинная активная транзакция
  2. Высокие протоколирующие транзакции, такие как перестройка индекса, реорганизация, массовая вставка, удаление и т. д.
  3. Любая функция HA, такая как репликация, зеркалирование, которая содержит журнал и не позволяет ему освобождать пространство журнала.

â € ¢ Почему мой файл журнала настолько большой?

Проверьте столбец log_reuse_wait_des c в таблице sys.databases, чтобы узнать, что удерживает журналы от усечения:

выберите имя, log_reuse_wait_desc
из sys.databases

â € ¢ Каковы некоторые способы предотвратить эту проблему?

Резервные копии журналов помогут вам контролировать рост журнала, если только что-то не позволяет повторно использовать журналы.

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

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

https: //www.brentozar. ком /архив /2016/03 /мой-любимый-системы колонного log_reuse_wait_desc /

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

ответил Ramakant Dadhichi 11 J000000Tuesday17 2017, 20:42:04

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132