У меня есть три плана обслуживания, настроенные для работы на экземпляре Sql Server 2005:
Ежечасные резервные копии журналов обычно составляют от нескольких сотен КБ до 10 МБ в зависимости от уровня активности, ежедневные разницы обычно вырастают примерно до 250 МБ к концу недели, а еженедельное резервное копирование составляет около 3,5 ГБ.
Проблема, с которой я сталкиваюсь, заключается в том, что оптимизация перед полным резервным копированием, похоже, приводит к увеличению размера следующей резервной копии журнала транзакций более чем в 2 раза по сравнению с размером полной резервной копии, в данном случае 8 ГБ, прежде чем вернуться в нормальное состояние.
Кроме как BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY, есть ли способ уменьшить размер этой резервной копии журнала или вообще предотвратить запись оптимизаций в журнал транзакций, поскольку они обязательно будут учтены в полной резервной копии, которой они предшествуют?
Здесь есть несколько интересных предложений, которые, кажется, показывают неправильное понимание того, как работают резервные копии журналов. Резервная копия журнала содержит ВСЕ журналы транзакций, созданные с момента предыдущей резервной копии журнала, независимо от того, какие полные или дифференциальные резервные копии были сделаны за это время. Остановка резервного копирования журналов или переход к ежедневному полному резервному копированию не повлияет на размер резервных копий журналов. Единственное, что влияет на журнал транзакций, - это резервное копирование журнала после запуска цепочки резервного копирования журнала.
Единственное исключение из этого правила - если цепочка резервных копий журнала была нарушена (например, при переходе к модели восстановления SIMPLE, возврате из моментального снимка базы данных, усечении журнала с помощью BACKUP LOG WITH NO_LOG / TRUNCATE_ONLY), и в этом случае первая резервная копия журнала будет содержать весь журнал транзакций с момента последнего полного резервного копирования, что перезапускает цепочку резервного копирования журнала; или если цепочка резервного копирования журналов не была запущена - когда вы впервые переключаетесь на ПОЛНЫЙ, вы работаете в своего рода псевдо-ПРОСТОЙ модели восстановления, пока не будет создана первая полная резервная копия.
Чтобы ответить на ваш исходный вопрос, не вдаваясь в ПРОСТОЮ модель восстановления, вам придется выполнить резервное копирование всего журнала транзакций. В зависимости от действий, которые вы предпринимаете, вы можете чаще делать резервные копии журналов, чтобы уменьшить их размер, или делать более целевую базу данных.
Если вы можете опубликовать информацию о выполняемых вами операциях по обслуживанию, я могу помочь вам их оптимизировать. Вы случайно не выполняете перестроение индекса, а затем сжимаете базу данных, чтобы освободить место, используемое при перестроении индекса?
Если во время обслуживания у вас нет другой активности в базе данных, вы можете сделать следующее:
Надеюсь, это поможет - с нетерпением жду дополнительной информации.
Спасибо
[Изменить: после всех обсуждений о том, может ли полная резервная копия изменить размер последующей резервной копии журнала (а не может), я собрал исчерпывающий пост в блоге с справочными материалами и сценарием, который это доказывает. Проверьте это на https://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/]
Вы можете сжать их, но они просто снова вырастут, что в конечном итоге приведет к фрагментации диска. При перестроении и дефрагментации индекса создаются очень большие журналы транзакций. Если вам не нужна возможность восстановления на определенный момент времени, вы можете перейти в простой режим восстановления и полностью отказаться от резервного копирования журнала транзакций.
Я предполагаю, что вы используете план обслуживания для оптимизации, вы можете изменить его, чтобы использовать скрипт, который выполняет дефрагментацию индекса только при достижении определенного уровня фрагментации, и вы вряд ли пострадаете от снижения производительности. Это приведет к созданию журналов гораздо меньшего размера.
Кстати, я бы пропустил ежедневные различия в пользу ежедневных полных резервных копий.
Ваш последний вопрос был: «Помимо BACKUP LOG WITH TRUNCATE_ONLY, есть ли способ уменьшить размер этой резервной копии журнала или вообще предотвратить запись оптимизаций в журнал транзакций, поскольку они обязательно будут учтены в полной резервной копии, которой они предшествовали?»
Нет, но вот обходной путь. Если вы знаете, что единственными действиями в этой базе данных в это время будут задания по обслуживанию индекса, вы можете остановить резервное копирование журнала транзакций до начала обслуживания индекса. Например, на некоторых моих серверах по субботним вечерам графики работы выглядят так:
Это означает, что у меня нет возможности восстановления на определенный момент времени между 21:45 и 23:30, но выигрыш - более высокая производительность.
Простой ответ: измените еженедельное задание по оптимизации, чтобы оно выполнялось более сбалансированным образом каждую ночь. т.е. переиндексируйте таблицы a-e в воскресенье вечером, f-l в понедельник вечером и т. д. найдите хороший баланс, ваш журнал будет в среднем примерно 1/6 размера. Конечно, это работает лучше всего, если вы не используете встроенное задание обслуживания индекса ssis.
Обратной стороной этого является то, что он существенно зависит от нагрузки, которую испытывает ваша база данных, в том, что это наносит ущерб оптимизатору и повторному использованию планов запросов.
Но если все, что вас волнует, - это размер вашего t-журнала на еженедельной основе, разделите его по дням или часам и запускайте резервные копии t-log между ними.
Вы также можете изучить сторонний инструмент (Litespeed от Quest, SQL Backup от Red Gate, Hyperbac), чтобы уменьшить размер резервных копий и журналов. Они могут быстро окупиться за счет экономии на магнитной ленте.
Вероятно, можно предположить, что ваши «оптимизации» включают перестроение индекса. Выполнение этих задач только на еженедельной основе может быть приемлемым для базы данных, которая не встречает большого количества обновлений и вставок, однако, если ваши данные очень подвижны, вы можете сделать несколько вещей:
Перестраивайте или реорганизуйте индексы каждую ночь, если позволяет расписание и если влияние приемлемо. При выполнении этих задач еженощного обслуживания индексов нацелены только на те индексы, которые фрагментированы более чем на 30% для повторных построений и в диапазоне 15–30% для реорганизации.
Эти задачи представляют собой регистрируемые транзакции, поэтому, если вас беспокоит рост журнала, я бы поддержал то, что рекомендовал Пол. Окончательное резервное копирование журнала транзакций перед обслуживанием индекса, переключитесь на простое восстановление, затем выполните процесс обслуживания, а затем вернитесь к полному восстановлению с последующим полным резервным копированием данных.
Я использую дзен-подход к своим файлам журналов: они того размера, которого они хотят. До тех пор, пока они не пережили бурного роста из-за плохой практики резервного копирования по сравнению с активностью базы данных, это моя мантра.
Что касается скриптов, которые выполняют дискреционное обслуживание индекса, посмотрите онлайн: их очень много. Примерно год назад Эндрю Келли опубликовал неплохую статью в журнале SQL. В SQLServerPedia есть несколько сценариев от Мишель Аффорд, а в последнем выпуске журнала SQL Magazine (кажется, июль 2009 г.) также есть полная статья по этой теме. Дело в том, чтобы найти тот, который вам подходит, и сделать его своим с минимальными настройками.
Можете ли вы сделать резервную копию журнала транзакций на различных этапах оптимизации базы данных? Общий размер t-журналов будет таким же, но каждый будет меньше, что, возможно, поможет вам в какой-то мере.
Можете ли вы сделать более целенаправленную оптимизацию базы данных, чтобы создавалось меньше транзакций (кто-то упомянул об этом, но я не уверен, что последствия были изложены). Например, терпение некоторой фрагментации или ненадлежащего использования пространства на некоторое время. Если 40% ваших таблиц фрагментированы только на 5%, то, если вы не прикасаетесь к ним, можно значительно сэкономить.