Причини заповнення журналу транзакцій SQL серверів 4.2x, 6.0, 6.5, 7.0, Мова запитів SQL, Бази даних, статті

sql.ru

За матеріалами статті Микрософт: 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.
Але навіть при включеній опцією «truncate log on checkpoint», не виключені випадки,
коли журнал транзакцій може переповнитися. Нижче ці випадки розглянуті
докладно:

1. При виконанні великої, неподільної транзакції, особливо при масових операціях
UPDATE, DELETE або INSERT: Кожен окрема SQL інструкція буде
розглядатися як самостійний, неподільний модуль, який повинен
застосовуватися або не застосовуватися цілком. З цієї причини, всі зміни рядків
повинні бути зареєстровані, і транзакція не може бути усічена на всьому
протягом її виконання. Наприклад, якщо відбувається масова операція з INSERT,
яка триває п'ять хвилин, записи журналу транзакцій, пов'язані з цією
транзакції, не можуть бути усічені протягом цього часу. Адміністратор бази
даних повинен забезпечити достатньо місця в журналі для найбільшої, масової
операції або має розбити масову операцію на менші блоки.

2. При наявності в журналі незавершених транзакцій: журнал транзакцій може бути
усічений тільки до найстарішої незавершеною транзакції. Є кілька
можливих причин появи незавершених транзакцій, більшість з яких
відносяться до помилок прикладного програмного забезпечення. Вони включають:
– Масові транзакції. Як показано вище, для великих і тривалих, масових
транзакцій відповідні реєстраційні записи в журналі не можуть бути
усічені протягом усього періоду виконання. Однак, така транзакція буде
перешкоджати усікання та інших більш коротких транзакцій, які виконуються
в той же самий час.
– Не продумана розробка прикладного програмного забезпечення, коли в
межах обумовленої користувачем транзакції можливе введення або
коригування даних користувачем або інші, тривалі дії
користувача. Наприклад, після BEGIN TRANSACTION, програма може запросити у
користувача введення даних, який може тривати довгий час, залежно від
поведінки користувача. Поки користувач відповідним чином не
відреагує, додаток не зможе виконати COMMIT, а усічення журналу не буде
можливим.
– Помилки прикладного програмного забезпечення, внаслідок яких транзакція
залишається не завершеною: звичайна причина цього – неправильна обробка виклику
dbcancel () DB-Library в межах обумовленої користувачем транзакції. Це
спостерігається, коли запит відмінено за допомогою dbcancel (), причому, исполняющаяся в
цей час SQL Інструкції переривається і відкочується назад, а зовнішня транзакція
не скасовується. Додаток повинен гарантувати виконання ROLLBACK TRANSACTION
або інструкції COMMIT TRANSACTION, щоб повністю відпрацювати і закрити
транзакцію. Не виконання цієї вимоги, часто призводить до появи помилки
3902: The commit transaction has no corresponding BEGIN TRANSACTION. Це можна
використовувати в програмі, щоб у відповідь виконати SELECT @ @ TRANCOUNT, і
визначити, яка вкладена транзакція існує на цьому рівні. Однак,
додаток не повинно робити це наосліп, що б все-таки виконати
COMMIT / ROLLBACK і отримати @ @ TRANCOUNT = 0. Це не бажано тому, що
@ @ TRANCOUNT може вказувати не на ту транзакцію, яку очікувалося визначити.
Тобто додаток може не побачити запис про транзакції вкладеного рівня,
щодо тієї транзакції, яка стала причиною помилки програми. І тоді,
ініціація COMMIT / ROLLBACK може привести до завершення або відкату не завершеної
транзакцій, так як програма не може знати, які транзакції завершені на
вкладеному рівні. Для вирішення таких проблем, програміст повинен так налагодити
програми та можливі процедури, що б повністю виключити
можливість ненавмисного
переривання вкладених транзакцій.
– Мережеві помилки, через які SQL сервер не отримує повідомлення про втрату
мережевого підключення. Якщо клієнтська робоча станція зависає,
перезавантажується, або буде вимкнено під час виконання обумовленої
користувачем транзакції, засоби мережевого рівня повинні повідомити про це SQL
серверу. Якщо мережа не відреагує на це належним чином, SQL сервер буде
вважати, що клієнт продовжує роботу, і виконується клієнтська транзакція
буде як і раніше активна. Ця мережева проблема повинна контролюватися
адміністратором, який може за допомогою sp_who, sp_lock, або мережевих утиліт
визначити такі обірвані мережеві з'єднання і знищувати їх вручну.
– Транзакції, не завершені через блокувань. У багатокористувацької середовищі
исполняющаяся транзакція може звернутися до блокованим іншим процесом
даними. У цьому випадку, транзакція залишиться відкритою і не дозволить виконати
усікання журналу. Щоб це виявити, програміст або адміністратор бази
даних повинен використовувати sp_who, sp_lock, або інші інструментальні засоби,
для відповідного аналізу. У більшості випадків, проблеми блокувань можуть
бути знижені або усунені через зміну запиту, індексу, і зміна
дизайну бази даних.
– Невдалі спроби скасування запитів на модифікацію даних. Якщо додаток
виконує dbcancel (), але запит не скасований через проблеми в мережі або SQL
сервера, запит продовжує виконуватися, і транзакція залишиться відкритою. Якщо
Ви підозрюєте наявність такої проблеми, використовуєте sp_who, щоб побачити,
відмінено чи запит. При спробі такого скасування для клієнта, що працює через
TCP / IP сокет, пробуйте перевірити підключення клієнта по named pipes, або
запустіть клієнтську програму на сервері, використовуючи local pipes. Це допоможе
визначити, чи викликана проблема мережею або SQL сервером.

3. Надмірна утилізація ресурсів сервера при усіканні журналу під час відпрацювання
Checkpoint: Хоча журнал транзакцій усікається кожні 60 секунд, кількість
усікається за цей час транзакцій обмежене. Ймовірність того, що контрольна
точка не встигне пробігти за відведений їй час весь журнал, надзвичайно мала,
і Ви повинні попередньо розглянути всі інші можливі причини
виникнення проблеми. Однак, перевищити максимальну норму усікання все-таки
можливо, якщо велика кількість
клієнтів буде одночасно виконувати модифікацію великої кількості
даних. Подібні проблеми вирішуються шляхом перегляду структури програми, з
метою зменшення числа модифікуються клієнтом рядків, що завжди має бути
однією з першорядних завдань дизайну для будь-якої бази даних.
Якщо це не здійснимо, система може бути масштабувати з метою збільшення
пропускної здатності дискової підсистеми (I / O), наприклад striping,
додаткові диски або дискові контролери, і т.д. Зазвичай, це видно тому,
що процес відпрацювання контрольної точки споживає більше часу, ніж належить
при виконанні команди DUMP TRANSACTION, оскільки він намагається не відставати від
усікання журналу. Як тільки поріг перевищено усікання (див. нижче) Ви побачите, що
обробник контрольної точки перестав робити спроби усікання журналу бази
даних, поки не відбудеться очищення журнал транзакцій.

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, відволікаючи на це додаткове місце. Така транзакція по базi tempdb буде продовжуватися
до завершення породила її транзакції в призначеній для користувача базі, що може
затримати усічення журналу tempdb протягом цього часу. Якщо транзакція в
користувацької базі припинена з якої-небудь причини, (включаючи блокування
або додаток, не обробляє dbnextrow () для завершення), транзакція в
tempdb, як і в попередньому випадку, залишиться відкритою, заважаючи усіканню журналу
tempdb. Для вирішення такої проблеми, програміст повинен відповідним чином
налагодити програми та / або вирішувати проблеми паралельного виконання
транзакцій, які породжують цю ситуацію.

Усічення журналу транзакцій SQL Server 7.0 відбувається шляхом усікання
віртуальних журналів Virtual Log Files (VLF) з яких, як з цеглинок,
складається журнал транзакцій. Якщо в журналі існує активна транзакція,
розташована резидентно в одному з VLF, цей віртуальний журнал не може бути
усічений. Якщо активні транзакції є в усіх віртуальних журналах, журнал
транзакцій не може бути усічений. Якщо включена опція автоматичного зростання
журналу і є достатньо місця на диску, де знаходиться журнал, і максимальний
розмір файлу журналу ще не досягнутий, журнал транзакцій буде збільшений на
величину, зазначену у властивостях журналу.

Нижченаведені зауваження розглядають той випадок, коли відбувається усікання журналу при
виконанні SQL запиту, залежно від того, чи включена опція TRUNCATE LOG
ON CHECKPOINT:
– Якщо опція TRUNCATE LOG ON CHECKPOINT включена, і буде встановлено під час
запуску сервера, що журнал транзакцій переповнений – його вміст буде
автоматично знищено опцією no_log.
– Опція TRUNCATE LOG ON CHECKPOINT – встановлена ​​за умовчанням в базі master,
тому що його журнал транзакцій не може бути винесений на окремий пристрій,
так що Ви будете вельми обмежені в можливостях при переповненні його
журналу. Єдина можливість подолати переповнення журналу бази master,
це відключити його.
– Якщо опція TRUNCATE LOG ON CHECKPOINT не встановлена, і буде встановлено під
час запуску, що журнал транзакцій переповнений – відновлення завершується,
але кінцева контрольна крапка не відпрацьовується. Адміністратор може увійти в
базу даних і резервувати журнал транзакцій за допомогою опції no_truncate,
щоб зберегти дані, а потім очистити його за допомогою опції no_log.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*