| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
|
|
Причины заполнения журнала транзакций SQL серверов 4.2x, 6.0, 6.5, 7.0 По материалам статьи Микрософт: Q110139 - INF Causes of SQL Transaction Log Filling Up Журнал транзакций может полностью заполнится, что сделает невозможным операции UPDATE, DELETE или INSERT, включая CHECKPOINT. Обычно это проявляется, как ошибка 1105, имеющая следующее содержание: Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment. От этого может пострадать любая база данных, включая master и tempdb. Эта статья рассматривает возможные причины и решение проблем, приводящих к ошибке 1105. Если Ваш журнал транзакций заполнен полностью, и Вы получили сообщение об ошибке 1105, Вы должны очистить журнал, используя команду DUMP TRANSACTION. Для получения дополнительной информации об использовании DUMP TRANSACTION, см. документацию SQL сервера. Фундаментальной характеристикой реляционных баз данных, таких как Microsoft SQL Server, является поддержка целостности. Любая транзакция должна быть абсолютно неделима и все вносимые ей изменения должны быть применены полностью или не применены вообще, даже в случае отказа системы. В определяемой пользователем транзакции, транзакционным блоком считаются все инструкции между BEGIN TRANSACTION и COMMIT TRANSACTION. В неявной транзакции, каждая отдельная инструкция SQL рассматривается неделимым модулем. Это дает возможность серверу баз данных выдерживать сбои питания, аварийные отказы операционной системы, и т.д., когда, после перезапуска, происходит автоматическое (без участия обслуживающего персонала) восстановление баз данных к непротиворечивому состоянию. Эту возможность обеспечивает механизм журнала транзакций. Так как целостность данных является фундаментальным свойством SQL сервера, регистрация транзакций не может быть заблокирована. Некоторые утилиты или операции, типа BCP или SELECT INTO, имеют минимальную регистрацию в журнале, но даже этого достаточно, чтобы обратный откат таких операций был возможен. Требования к количеству дискового пространства для поддержки регистрации транзакций могут быть весьма высокими. Например, в большинстве случаев, модификация каждой строки данных должна быть зарегистрирована, а также регистрируются и все модификации затронутых индексов. Так как запись в журнале может содержать фиксированную часть данных изменяемой строки, количество занимаемого журналом места зависит от ширины строки. Для коротких строк, количество занимаемого в журнале места, при операциях UPDATE, DELETE или INSERT, может в десятки раз увеличивать занимаемое базой место на диске. При использовании достаточно широких строк, размер базы данных будет пропорционально больше величины журнала транзакций. Поэтому, старайтесь тщательно отслеживать потребление журналом транзакций дискового пространства, что поможет Вам гарантировать целостность данных. Обеспечение нормальной работы механизма регистрации транзакций является одной из первейших обязанностей DBA. Количество занимаемого журналом места может зависеть от многих факторов и его очень трудно предсказать заранее. Стандартное руководство администратора предлагает, в качестве отправной точки, использовать величины порядка 15 - 30 процентов от размера базы данных. В действительности этого может оказаться недостаточно или наоборот, такие значения будут не оправдано избыточны. Как правило, более точные результаты даёт эмпирическое испытание, дающие грубую оценку размера журнала для реальных данных и прикладных программ. Попытки вычислить размер журнала транзакций, без подобного моделирования реальных условий, достаточно трудны и дают очень не точеные результаты. Несколько факторов могут влиять на размер журнала транзакций. Первый из таких факторов - оптимизатор запросов (query optimizer). Для одного и того же SQL запроса, модифицирующего данные, через какое-то время план выполнения может измениться в зависимости от статистики распределения данных. Разные планы выполнения могут по разному использовать место в журнале. Следующий фактор - неизбежная внутренняя фрагментация базы данных, которая может привести к разному количеству имеющихся разбитых страницы. Не существует стандартных методов отслеживания подобных процессов, поскольку SQL сервер управляет пользовательскими данными автоматически. Самым простым методом такой оценки является выполнение команды DBCC CHECKTABLE (syslogs), которая возвращает число 2048-байтовых страниц данных в журнале, до и после выполнения тестовых или реальных, модифицирующих данные запросов. Это может дать приблизительную оценку требующегося места в журнале для таких запросов. Обычно, лучше допустить ошибку в сторону избыточности размера журнала, при определении его размера или дискового пространства для него.
У SQL Server 7.0, журнал транзакций имеет возможность расшириться автоматически.
Дискретность прироста может задаваться пользователем или может быть разрешено
использование всего доступного дискового пространства. Журнал состоит из
виртуальных журналов (Virtual Log files, далее VLF). Количество и размер этих
виртуальных журналов определяет SQL сервер и это не может быть изменено
конфигурационными параметрами. После создания новой базы данных, каждый её
физический журнал имеет не менее двух VLF. Иногда администраторы базы данных
включают опцию базы данных «truncate log on checkpoint», чтобы избежать
переполнения журнала. Назначение этой опции состоит в том, чтобы обеспечить автоматическое
усечения журнала транзакций, главным образом для разрабатываемых или тестовых
баз данных, которым не обязательна регистрация транзакций для последующего их
резервирования. Эта опция не отключает регистрацию транзакций или поддержку
целостности, а только заставляет обработчик контрольной точки делать попытку
усечения журнала приблизительно каждые 60 секунд. Обратите внимание, что журнал
транзакций не будет усекаться при ручной установке команды «truncate log on
checkpoint», которая автоматически запускает контрольную точку в базе данных.
Эта опция всегда включена для базы данных tempdb, хотя Вы не увидите этого с
помощью хранимой процедуры sp_help. 1. При исполнении большой, неделимой транзакции, особенно при массовых операциях UPDATE, DELETE или INSERT: Каждый отдельная SQL инструкция будет рассматриваться как самостоятельный, неделимый модуль, который должен применяться или не применяться целиком. По этой причине, все изменения строк должны быть зарегистрированы, и транзакция не может быть усечена на всём протяжении её исполнения. Например, если происходит массовая операция с INSERT, которая продолжается пять минут, записи журнала транзакций, относящиеся к этой транзакции, не могут быть усечены в течении этого времени. Администратор базы данных должен обеспечить достаточно места в журнале для самой большой, массовой операции или должен разбить массовую операцию на меньшие блоки.
2. При наличии в журнале незавершённых транзакций: журнал транзакций может быть
усечен только до самой старой незавершённой транзакции. Есть несколько
возможных причин появления незавершённых транзакций, большинство из которых
относятся к ошибкам прикладного программного обеспечения. Они включают:
3. Чрезмерная утилизация ресурсов сервера при усечении журнала во время отработки
Checkpoint: Хотя журнал транзакций усекается каждые 60 секунд, количество
усекаемых за это время транзакций ограничено. Вероятность того, что контрольная
точка не успеет пробежать за отведённое ей время весь журнал, чрезвычайно мала,
и Вы должны предварительно рассмотреть все другие возможные причины
возникновения проблемы. Однако, превысить максимальную норму усечения всё-таки
возможно, если большое количество
клиентов будет одновременно выполнять модификацию большого количества
данных. Подобные проблемы решаются путём пересмотра структуры приложения, с
целью уменьшения числа модифицируемых клиентом строк, что всегда должно быть
одной из первостепенных задач дизайна для любой базы данных. 4. Превышение порога усечения. Обработчик контрольной точки по существу делает DUMP TRANSACTION WITH TRUNCATE_ONLY. Так же, как это делается вручную. Но контрольная точка не всегда будет успевать появится до того, как журнал будет заполнен до некоторого критического значения. Например, чрезмерный объём модификаций данных, выполненный за очень короткое время, может заполнить журнал транзакций на 95% между двумя очередными контрольными точками. Когда обработчик контрольной точки сделает попытку усечения почти заполненного журнала транзакций, это может привести к невозможности самого усечения. Такое случается потому, что усечение журнала само является регистрируемой операцией. Единственное решение в этом случае состоит в том, чтобы использовать DUMP TRANSACTION WITH NO_LOG, т.е. вручную запустить усечение журнала транзакций. Не рекомендуется использование опции NO_LOG кроме случаев, когда это абсолютно необходимо, поскольку операция не будет регистрироваться в журнале и произошедший во время выполнения такой операции отказ системы, может породить ошибки в базе данных. 5. Комбинации представленных выше четырёх причин. Например, при нормальных условиях в интенсивно модифицируемой среде, норма усечения обработчика контрольной точки может препятствовать заполнению журнала. Если временно открытая транзакция, вызванная любой из вышеупомянутых причин (например, блокировки) приведёт к заполнению журнала, к примеру, до 50 %, останется гораздо меньшее возможностей для обработки других возможных модификаций данных, что делает более вероятным достижение порога усечения, после которого автоматическое усечение журнала будет невозможно. Транзакции в tempdb регистрируются так же, как и в любой другой базе данных. Опция TRUNCATE LOG ON CHECKPOINT, в большинстве случаев, остаётся включённой для tempdb. Из-за этого, журнал транзакций постоянно усекается и не переполняется. Однако, любая из вышеупомянутых причин может заставить журнал базы tempdb переполниться. Конфигурация Tempdb обычно подразумевает размещение базы и журнала в одном файле (sysusages.segmap=7), вследствие чего данные и регистрационные операции будут конкурировать за одно и то же доступное дисковое пространство. Некоторые конструкции Transact-SQL, такие, как GROUP BY, ORDER BY DESC и т.д., будут автоматически требовать место в tempdb для своей работы. Это порождает неявные записи BEGIN TRANSACTION в журнале tempdb, отвлекая на это дополнительное место. Такая транзакция по базе tempdb будет продолжаться до завершения породившей её транзакции в пользовательской базе, что может задержать усечение журнала tempdb в течение этого времени. Если транзакция в пользовательской базе приостановлена по какой-либо причине, (включая блокировки или приложение, не обрабатывающее dbnextrow() для завершения), транзакция в tempdb, как и в предыдущем случае, останется открытой, мешая усечению журнала tempdb. Для решения такой проблемы, программист должен соответствующим образом отладить приложение и/или разрешать проблемы параллельного исполнения транзакций, которые порождают эту ситуацию.
Усечение журнала транзакций SQL Server 7.0 происходит путём усечения
виртуальных журналов Virtual Log Files (VLF) из которых, как из кирпичиков,
состоит журнал транзакций. Если в журнале существует активная транзакция,
расположенная резидентно в одном из VLF, этот виртуальный журнал не может быть
усечен. Если активные транзакции есть во всех виртуальных журналах, журнал
транзакций не может быть усечен. Если включена опция автоматического роста
журнала и есть достаточно места на диске, где находится журнал, и максимальный
размер файла журнала ещё не достигнут, журнал транзакций будет увеличен на
величину, указанную в свойствах журнала.
Нижеследующие замечания рассматривают тот случай, когда происходит усечение журнала при
исполнении SQL запроса, в зависимости от того, включена ли опция TRUNCATE LOG
ON CHECKPOINT: |
|
|
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|