Шринк лога транзакцій MS SQL 2008
Коли під час підключення до бази MS SQL з'являються помилки:
Помилка СУБД:Microsoft OLE DB Provider for SQL Server: Журнал транзакцій для бази даних "ReportServer" заповнений. Щоб знайти причину, з якої місце в журналі не може бути повторно використано, зверніться до стовпця log_reuse_wait_desc таблиціsys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1
Помилка СУБД:Microsoft OLE Provider для SQL Server: Transaction log for database “ReportServer” is full. Щоб побачити, який простір в log cannot be reused, viz log_reuse_wait_desc column is sys.databaseHRESULT=80040E14, SQLSTATE=4 2000, native=9002
це означає, що на диску, де розташований лог транзакцій, закінчилося місце і тепер СУБД нікуди записувати дані про нові транзакції. Найчастіше таке відбувається, коли не встановлено жодних обмежень на розмір лога та у MS SQL не створено відповідних планів обслуговування.
У такому випадку необхідно зменшити розмір самого файлу транзакцій (*.ldf), тобто створити шринк (стиснення) лога. Для цього можна використовувати як запит, так і стискування вручну.
Розглянемо стиск лога транзакцій вручну:
Крок 1. Встановити модель відновлення проста (Simple). Правою кнопкою на базі – Властивості (Properties) – Параметри (Options) – 4-й зверху пункт Модель відновлення (Recovery model) – Проста (Simple) – OK.
Крок 2. Виконати шринк (стиснення) лога транзакцій. Правою кнопкою на базі - Завдання(Tasks) - Стиснути(Shrink) - Файли(Files) - встановити Тип файлу(File type) - Журнал(Log) - у Операціястиснення (Shrink action) - вибрати Реорганізувати сторінки, перед тим виводити місце, що не використовується (Reorganize pages before releseasing unused space) - Стиснути файл (Shrink file to) - вказати прийнятний розмір лога.
Крок 3. Встановити модель відновлення Повна (Full). Правою кнопкою на базі – Властивості (Properties) – Параметри (Options) – 4-й зверху пункт Модель відновлення (Recovery model) – Повна (Full) – OK.
P.S.: У цій статті наведено рекомендації для вирішення конкретної проблеми. Налаштування самого MS SQL тут не розглядається!
Спеціальні пропозиції
Ідея непогана, але в заголовку статті не вистачає напису "в екстреному випадку" або "коли штатні кошти не дозволяють" Справді, бувають запущені ситуації, коли з ліг уже нічого зробити не можна (ні бекап, ні шринк).
Я правильно розумію, що ця операція допоможе, якщо на диску вже майже не залишилося вільного місця?
Я зазвичай роблю скрипт. Швидше виходить. Також можна налаштувати виконання за розкладом.
Маю базу 90 Гб. У режимі Siple, бо те що роблять користувачі і програмісти з базою часто в моделі Full збільшувало базу разів у 5. Навіть у Siple моделі лог збільшується, якщо є велика кількість змін (15-30 Гб буває).
Для відкатів, робите диференційовані бекапи бази протягом (періодичність скажемо годину) і все що потрібно їсти.
Шрінк і основі 90 Гб при повній моделі при робочих користувачах (лог файл 50-150 Гб) життя не пройде, або буде робитися дуже довго. Так як при повній моделі в основній базі ще немає змін, які зберігаютьсяу лог-файлі, і якщо користувачі працюють із даними, зміни якими мають бути записані. Та й важливий факт - кількість користувачів. У мене їх за 100 водночас працюючих. Транзакції ніхто таки не скасовував.
Як одноразовий захід, щоб якщо база вижрала все місце на диску і відмовляється працювати, цілком воно. Все одно ніхто працюватиме вже не буде. Тоді так, це найефективніший варіант.
саме така ситуація прийшла. База була кинута, франчі встановили, поїхали і нікому до неї не було справи - ніякого бекапу - так іноді тільки DT-шники вивантажували і коли згадають. АЛЕ після проведення реіндексації реструктуризації бал виріс до 300 ГБ при базі в 45 ГБ. і майже закінчилося місце на диску.
причому реально база 19,5 ГБ у локальній файловій копії. (45 ГБ стала після завантаження в існуючу DT-архіву - може підкажете як правильно повернути назад.).
Зробив усе як у статті (не став БЕКАП робити бо нікуди. обмежився вивантаженням у ДТ-архів.) при непрацюючих користувачах. розмір вказав 30 ГБ. Шрінк пройшов дуже швидко. Зараз налаштовую плани обслуговування (поки на тестовій базі) і ставлю питання про придбання гвинта спеціально під бекапи.
Але в мене таке питання Начебто встановив модель назад у FULL, але навіть зробивши реіндексацію таблиць після цього розмір журналу не змінився. і мало того вже робочий день закінчується - купа проведених документів була, але розмір який був такий залишився і дата зміни не змінюється (останні зміни бази - 0:22 - вночі в базі ніхто не працює крім мене:) а лога аж 22 числа, т .е. 2 дні тому) - у мене встановлено автозбільшення лога на 200 МБ, а бази на 500 МБ, можливо дата зміни змінюється тільки під час збільшення розміру. Хоча після реіндексації розмір лога мав збільшитися на 25 ГБ(Так було до шринка) - може хтось роз'яснити чому ліг на місці стоїть? - плани бекапів ще не підключав, тобто ніякого архівування немає.
Є не найпростіший і найшвидший, але дуже надійний рецепт усічення "порожнього" (тобто даних там немає, але файл не зменшується) файлу ldf. Застосовую коли ніщо інше не допомагає. Правда якщо спочатку (при створенні бази) файл був створений певного розміру, цей спосіб теж не допоможе.
Рецепт простий: //починаємо транзакцію BEGIN TRAN GO //робимо апдейт якогось поля якоїсь великої таблиці UPDATE . GO //скасовуємо транзакцію ROLLBACK GO
Після цього файл можна зрізати на розмір заповнених даних у файлі ldf. Рецепт використовуємо стільки разів, скільки потрібно.
При повній моделі відновлення бекапи робляться так часто як це можливо для того, щоб процес відновлення починати не з споконвіку існування бази, а з останнього повного створеного бекапу + далі накочують бекапи логів транзакцій. це дуже зручно хоча на практиці доводилося користуватися лише кілька разів у житті. У нас, наприклад, це раз на тиждень по четвергах (найоптимальніший час виходячи з усіх регламентів скуля + сервера 1С). При простій моделі ви зможете відновити базу тільки на момент останнього створеного бекапу.
Якщо у вас є нормальне програмне забезпечення з управління бекапами і логами ТЗ (хоча все це можна написати і на SQL) то робиться просто. Повна модель. Створюється бекап бази, зберігається, далі бекапяться логи раз у потрібний вам час у нас це 15 хвилин, логи теж зберігаються, але щоразу коли проходить повний бекап бази НОРМАЛЬНО всі старі бекапи бази та логів труться і починається новий відлік часу. Ну і так само налагоджено місячне зберігання бази піврічне та однорічнеякі зберігаються окремо і труться відповідно, коли створюються подібні бекапи.
За повної моделі та наших налаштувань ми можемо відновити базу максимум з тижневою давністю, далі все інше накотити логами. Так само ми можемо відновити базу на початок місяця, початок півріччя та початок року, але вже без логів транзакцій.
Щодо гальм не можу погодитися з попередніми ораторами про те, що проста модель працює швидше, вони працюють однаково якщо нормально настроєний сервер SQL, просто лог транзакцій повинен за замовчуванням лежати на іншому дзеркалі, окремо від того дзеркала де розташовується база. Багато разів перевіряли що так працює так однаково. Якщо ж логи транзакцій лежать на тому ж масиві, що й база - безумовно буде працювати повільніше.