как уменьшить размер резервной копии журнала транзакций после полного резервного копирования?

<p>у меня есть три плана обслуживания, настроенных для запуска на экземпляре sql server 2005:</p><ul> <li>еженедельная оптимизация базы данных с последующим полным резервным копированием</li> <li>ежедневное дифференциальное резервное копирование</li> <li>почасовые резервные копии журнала транзакций</li> </ul><p>почасовые резервные копии журналов обычно составляют несколько сотен kb и 10 мб в зависимости от уровня активности, ежедневные различия обычно увеличиваются до 250 мб к концу недели, а еженедельное резервное копирование составляет около 3,5 gb.</p><p>у меня проблема в том, что оптимизация перед полной резервной копией, по-видимому, приводит к увеличению размера следующей резервной копии журнала транзакций более чем в 2 раза по сравнению с полной резервной копией, в данном случае 8 гб, до возвращения в нормальное состояние.</p><p>кроме как <code>backup log <databasename> with truncate\_only</code>, есть ли способ уменьшить размер этой резервной копии журнала или вообще предотвратить запись оптимизаций в журнал транзакций, поскольку они обязательно будут учтены в полной резервной копии, которой они предшествуют?</p>

<p>здесь есть несколько интересных предложений, которые, похоже, показывают непонимание того, как работают резервные копии журналов. резервная копия журнала содержит все журналы транзакций, созданные с момента создания предыдущей резервной копии журнала, независимо от того, какие временные или полные резервные копии были сделаны в промежуточный период. остановка резервного копирования журнала или переход к ежедневному полному резервному копированию не повлияет на размеры резервного копирования журнала. единственное, что влияет на журнал транзакций, - это резервное копирование журнала после запуска цепочки резервного копирования журнала.</p><p>единственное исключение из этого правила - разрыв цепочки резервных копий журналов (например, переход к модели восстановления simple, возврат из снимка базы данных, усечение журнала с использованием backup log with no\_log/truncate\_only), и в этом случае первое резервное копирование журнала будет содержать весь журнал транзакций с момента последнего полного резервного копирования - который перезапускает цепочку резервного копирования журнала; или если цепочка резервного копирования журнала не была запущена - когда вы впервые переключаетесь в режим full, вы работаете в своего рода псевдо-простой модели восстановления, пока не будет выполнено первое полное резервное копирование.</p><p>чтобы ответить на ваш первоначальный вопрос, не вдаваясь в простую модель восстановления, вам придется взять на себя резервное копирование всего журнала транзакций. в зависимости от действий, которые вы предпринимаете, вы можете делать более частые резервные копии журналов, чтобы уменьшить их размер, или делать более целевую базу данных.</p><p>если вы можете опубликовать некоторую информацию о тех операциях по обслуживанию, которые вы выполняете, я могу помочь вам оптимизировать их. вы, случайно, не выполняете перестройки индекса с последующей уменьшенной базой данных, чтобы освободить пространство, используемое перестройками индекса?</p><p>если у вас нет других действий в базе данных во время обслуживания, вы можете сделать следующее:</p><ul> <li>убедитесь, что активность пользователя остановлена</li> <li>сделать окончательное резервное копирование журнала (это позволяет восстановить до момента начала обслуживания) <ul> <li>переключиться на простую модель восстановления</li> <li>выполнить обслуживание - журнал будет обрезаться на каждой контрольной точке</li> <li>переключиться на полную модель восстановления и сделать полную резервную копию</li> <li>продолжить как обычно</li> </ul> </li> </ul><p>надеюсь, это поможет - с нетерпением жду дополнительной информации.</p><p>спасибо</p><p>[правка: после всех дискуссий о том, может ли полная резервная копия изменить размер последующей резервной копии журнала (не может), я собрал всеобъемлющий пост в блоге с исходным материалом и сценарием, который это доказывает. проверьте это на <a href="https://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/]">https://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/]</a> </p>

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

<p>ваш последний вопрос был: <em>"кроме backup log with truncate\_only, есть ли способ уменьшить размер этой резервной копии журнала или вообще предотвратить запись оптимизаций в журнал транзакций, так как они обязательно будут учтены?" ибо в полной резервной копии они предшествуют? "</em></p><p>нет, но вот обходной путь. если вы знаете, что единственными действиями в этой базе данных в то время будут задания обслуживания индекса, то вы можете остановить резервное копирование журнала транзакций до начала обслуживания индекса. например, на некоторых моих серверах в субботу вечером графики работы выглядят так:</p><ul> <li>9:30 pm - выполняется резервное копирование журнала транзакций.</li> <li>9:45 pm - резервное копирование журнала транзакций выполняется в последний раз. расписание останавливается в 9:59.</li> <li>10:00 pm - задание обслуживания индекса запускается и имеет встроенные остановки до 11:30.</li> <li>11:30 pm - полное задание резервного копирования начинается и заканчивается менее чем за 30 минут.</li> <li>12:00 - резервное копирование журнала транзакций начинается каждые 15 минут.</li> </ul><p>это означает, что у меня нет возможности восстановления на определенный момент времени с 9:45 до 23:30, но выигрыш - более высокая производительность.</p>

<p>простой ответ: измените свою еженедельную работу по оптимизации на более сбалансированную работу по ночам. то есть переиндексируйте таблицы a-e вечером в воскресенье, f - l вечером в понедельник и т. д. ... найдите хороший баланс, ваш журнал будет в среднем примерно на 1/6 размера. конечно, это работает лучше всего, если вы не используете встроенную задачу поддержки индекса ssis.</p><p>недостатком этого, и это существенно в зависимости от нагрузки, которую испытывает ваша база данных, является то, что она наносит ущерб оптимизатору и повторному использованию планов запросов.</p><p>но если все, что вас волнует, это размер вашего t-журнала еженедельно, разделите его на день или час и час и запустите промежуточные резервные копии t-log.</p>

<p>вы также можете воспользоваться сторонним инструментом (litespeed от quest, sql backup от red gate, hyperbac), чтобы уменьшить размеры резервных копий и журналов. они могут быстро окупить себя за счет экономии на ленте.</p>

<p>можете ли вы специально сделать резервную копию вашего журнала транзакций в различных точках во время оптимизации вашей базы данных? общий размер t-журналов будет таким же, но каждый будет меньше, возможно, поможет вам в некотором роде.</p><p>можете ли вы сделать более целенаправленную оптимизацию базы данных, чтобы было создано меньше транзакций (кто-то упомянул об этом, но я не уверен, что последствия были прописаны). например, терпеть определенную фрагментацию или потратить впустую некоторое время. если 40% ваших таблиц фрагментированы только на 5%, не трогайте их, чтобы сэкономить немного активности.</p>

<p>вероятно, можно предположить, что ваши "оптимизации" включают перестройку индекса. только выполнение этих задач еженедельно может быть приемлемо для базы данных, которая не встречает большого количества обновлений и вставок, однако, если ваши данные очень изменчивы, вы можете сделать пару вещей:</p><ol> <li> <p>пересматривайте или реорганизуйте свои индексы по ночам, если позволяет ваше расписание и если влияние приемлемо. при выполнении этих задач ночного обслуживания индексов нацеливайте только те индексы, которые фрагментированы, скажем, 30% для перестроений и в диапазоне 15-30% для перегруппировок.</p> </li> <li> <p>эти задачи являются зарегистрированными транзакциями, поэтому, если вы обеспокоены ростом журнала, я бы рекомендовал то, что рекомендовал пол. окончательное резервное копирование журнала транзакций перед обслуживанием индекса, переключение на простое восстановление, затем процесс обслуживания, а затем переключение обратно на полное восстановление с последующим полным резервным копированием данных должно помочь.</p> </li> </ol><p>я использую дзен-подобный подход к своим файлам журналов: они того размера, который им нужен. до тех пор, пока они не пережили бурного роста из-за плохой практики резервного копирования по сравнению с активностью базы данных, которая является мантрой, которой я живу.</p><p>что касается сценариев, которые выполняют обслуживание индекса по усмотрению, посмотрите онлайн: их там масса. эндрю келли опубликовал приличный журнал в журнале sql около года назад. в sqlserverpedia есть несколько сценариев от мишель аффорд, а в последнем выпуске журнала sql (я полагаю, в июле 2009 года) также есть полная статья на эту тему. смысл в том, чтобы найти тот, который вам подходит, и сделать его своим с минимальными настройками.</p>