SQL server 2005 полный бекап и журнал транзакций. #601128

Здравствуйте. Прошу совета и подсказки в следующем: Создал следующую схему резервного копирования базы: 1. Каждое воскресенье в 4 часа делаю полное резервное копирование базы. 2. Каждый день, кроме воскресенья, в 5 часов делаю разностное резервное копирование базы. 3. Каждый день каждый час с 9.00 до 21.00 делаю копирование журнала транзакций. 4. Каждый день в 00:00:00 делаю обновление статистики, 01:00:00 -  реорганизация индекса, 02:00:00 - перестроение индекса. Прошу посоветовать, правильно ли я организовал бекап. Не ли чего лишнего или недостающего. И самый главный вопрос: Каждый бекап журнала транзакций в среднем у меня занимает 150-200МБ. Во время простоя, когда пользователей нет, занимает 404КБ. Но каждое утро в 9 часов создается первый бекап журнала транзакций размером в 5ГБ. Я так понимаю размер велик, потому что в себе он держит лог по реиндексациям и т.д. Т.е. все что происходило с последнего бекапа журнала транзакций 21:00:00 вечера. Но это же не совсем верно. Ведь в 5 часов создался полный (в воскресенье) или разностный(каждый день) бекап. Почему журнал транзакций автоматически не урезается.

"Каждый день в 00:00:00 делаю обновление статистики, 01:00:00 -  реорганизация индекса, 02:00:00 - перестроение индекса". "реорганизация индекса" это что, дефрагментация? Перестроение индексов уже включает в себя и обновление статистики, и дефрагментацию. Поэтому два первых пункта лишние.

Еще вопрос - в 2 часа у Вас начинается ребилд индексов. А сколько он длится?

Размер базы какой?

select name, log_reuse_wait_desc from sys.databases where name = 'MyDataBase'

Вобщем делаешь бэкап самой базы, потом бэкап лога, а потом уже обрезаешь файл лога. Например так: GO

Блин, время не посмотрел. Автор давно уже сам разобрался видать )

А ты говорил ему АвтоШринк?

Автошринк - зло.

Главный вопрос: Почему после полного бекапа в 4 часа или разностного в 5 часов бекап журнала транзакций в 9 часов включает все транзакции, начиная с 21.00 предыдущего дня, а не с 5 часов утра? Что, при полном или разностном бекапе журнал автоматически не урезается?

Ответьте плиз на главный вопрос: почему полный или разностный бекап не сдвигает LSN для следующего бекапа журнала транзакций?

нет и не должен

делаешь бекап базы, далее бекап лога в отдельном задании далее обрезку лога.. ну и обработку по очистке перед бекапом лога и архивом базы - чтоб лог транзакций к примеру, 60 дней хранить

А зачем после бекапа лога (т.е. журнала транзакций) делать обрезку лога. Ведь этот бкап и так уже сдвинет lsn

с чего это ?

тогда вопрос. BACKUP LOG db_name WITH TRUNCATE_ONLY создает бекап, или просто производит урезку цепочки. Может просто в последнем бекапе журнала транзакций в 21.00 сделать дописку WITH TRUNCATE_ONLY и все? Т.е. мне не нужен лог действий с 21.00 до 09.00 В это время пользователи не работают С 0.00 по 02.00 идут регламентные задания по переиндексации и т.д, в результате которые и создается бекап журнала транзакций в 9.00 такого большого размера

просто создает бекап лога

чем зло если не секрет?

Файл меняет размер. Соответственно его физическое положение на диске фрагментируется.

тогда как мне сделать так, чтобы в утреннем 9.00 бекапе журнала транзакций не были данные относительно ночных регламентных заданий по переиндексации и т.д. Т.е. я хочу следующее: Каждое воскресенье ночью создавать полный бекап. Каждый день ночью  создавать разностный бекап. Каждый час с 9.00 по 21.00 создавать бекап журнала транзакций. Каждый день ночью производить регламентные задания по Гилеву - индексы, статистика и т.д. Но чтобы при этом не было ненужных никому лишних логов, как сейчас происходит у меня, т.е. в утреннем бекапе журнала транзакций сидят ненужные мне данные по ночным переиндексациям, что увеличивает размер бекапа.

> Каждый день ночью производить регламентные задания по Гилеву - индексы, статистика и т.д. Вот это сильно увеличивает лог. 1. Бэкап лога 1. 2. Полный/разностный бэкап. 3. Бэкап лога 2. 4. Удалить бэкап лога 1. 5. Подрезать файл лога.

без прерывания цепочки никак не сделать

задание 1: обработка обслуживания (очистка старых бекапов с диска) - полный бекап каждое воскресенье задание 2: обработка обслуживания (очистка старых бекапов с диска) - разностный бекап каждый день, окромя  воскресенья задание 3: обработка обслуживания (очистка старых бекапов лога с диска) - бекап лога каждый каждый день с 9 до 21, окромя  воскресенья - скрипт t-sql со шринком лога задание 4-6 регламентные операции согласно расписанию

Если фул бэкап у тебя делается только 1 раз в вскр, то ничего сделать нельзя. Если фул бэкап делать раз в сутки, тогда можно прерывать цепочку усекая журнал с параметром TRUNCATE_ONLY и ему подобных (No_LOG и тд)

что именно нельзя сделать?

+22 если тебе не нужен последний лог - тот который от обработки твоих заданий - сделай отдельным рег заданием его бекап + шринк + удаление с диска.

"Но чтобы при этом не было ненужных никому лишних логов"

всё можно

что именно все можно?

"Но чтобы при этом не было ненужных никому лишних логов" 27

что есть никому не нужный лог?

какая последовательность действий?

см

он хочет лог транзакций от рег. заданий удалить..

как он будет восстанавливаться в случае чего?

дык это его проблемы :) ТС же грит в морг - значит в морг Хотя, достаточно разностный архив делать не вечером, а после этих рег заданий и усё..

хотя и так, если делать шринк лога - он будет всегда мелким, а реиндекс/статистика и прочие перестроения не увеличат лог на много

Увеличат на много.

ну.. если делать бекап лога, скажем каждые полчаса.. то весить он будет ~5-10Кб если выполнять рег задания.. ну увеличится он до ~10 метров.. и хрен с ним. :)

вообщем если п.2 это в 5 часов утра (05.00) то можно прервать цепочку выполнив усечение журнала с TRUNCATE_ONLY, соответственно перед этим самым разностным бэкапом. Если же 5 часов это 17.00 то никак.

У меня после реиндексации и обновления статистики лог растет до размера самой базы.

ну не знаю..смотрю бекап лога - максимум 200 метров есть, всё остальное - килобайты..

>Хотя, достаточно разностный архив делать не вечером, а после этих рег заданий и усё. У меня и так рег задания делаются ночью с 00.00 по 02.00, а разностный делается в 04.00. И после этого никаких действий в базе не происходит. Но бекап утром в 9.00 огромный, в полтора раза больше базы. Смотрю в логах, и там указано, что бекап журнала взял данные lsn с 21.00 часового последнего журнала транзакций.

ёпт, после рег заданий делаешь бекап лога и его шринк + удаление с диска обработкой обслуживания, далее разностный бекап и привет - наслаждаешься.

ок. Попробую. Только че то я сомневаюсь, что после разностного бекапа создастся нормальный бекап журнала транзакций, способный к восстановлению, потому что он будет своим началом lsn ссылаться на тот бекап, который мы удалим.

а зачем тебе нужен будет потом этот лог ? восстановление будет уже от разностного бекапа

Уверен? утренний бекап журнала логов ведь хранит в себе начальный lsn от последнего бекапа журнала транзакций,который мы удалим, а не от последнего разностного бекапа.

см разностный бэкап начинает новую цепочку журналов.

соответственно при восстановлении нужен будет последний фул+последний разностный+журналы идущие уже от этого разностного бэкапа

уверен :) Блин.. всё это проверяется за пару минут..

В >делаешь бекап лога. Я должен делать этот бекап с параметром TRUNCATE_ONLY? Т.е. получается, если я использую этот параметр, тогда цепочка начнется от разностного бекапа, а если без этого параметра, тогда даже если после бекапа журнала сделать разностный бекап, то цепочка идет от последнего журнала лога, а не от разностного. Правильно? По крайней мере щас у меня так, утренний бекап журнала связан цепочкой в вечерним бекапом журнала (21.00), а не с ночного разностного (05.00)

трункейт онли не обрежет тебе лог, если че нужно шринк делать еще

угу, бэкап с трункейтом тебе усечет лог, + нужно как в еще сделать шринк файла. Но там могут быть нюансы, если есть активные логические журналы, то шринк отрежет до них.

учитывая что я новичок, прошу не судить за возможно элементарные вопросы. И так 2 возможно очень элементарных вопроса(но не для меня :) ): 1. Как работает шринк, т.е. если я указываю размер к примеру 1024 Кб, за счет чего он уменьшает до этого размера. Что стоит к примеру поставить 10 Кб. 2. < удаление с диска обработкой обслуживания. Что такое обработка обслуживания. где ее найти?

1. 2.Управление-ПланыОбслуживания-Твой план -Задача очистка после обслуживания

По первому вопросу: уменьшает за счет неиспользуемых страниц в файле.

ну не намного :)

он не уменьшает до этого размера. он освобождает не используемые участки пока максимально не приблизится к этому значению. если успешно уменьшишь до 10, получишь тормоза, когда лог начнет расти. если места в логе нет скуль вешает "пива нет" и ждет пока появится (оно). смысла в шринке нет, если ты только не переносишь файл лога физически куда то, или места мало у тебя похоже 2 цепочки бэкапов, сравнивай параметры. бэкап лога содержит транзакции с последнего бэкапа своей цепочки. возможно у тебя бэкап сет нейм динамически создается?

две цепочки журналов - это пять. в все верно, но и верно. Дифференциальный бэкап, как и полный, не разрывает цепочку журналов, но позволяет начинать ее заново. Пример: в 21.00 полный бэкап, в 22.00 бэкап лога, в 23.00 бэкап лога, в 00.00 диф бэкап, в 01.00 бэкап лога, 02.00 бэкап лога. Можно восстановиться так: полный, бэкапы лога с 22.00 и 23.00, бэкапы лога с 01.00 и 02.00 - все будет нормально. А можно восстановиться так: полный, диф бэкап с 00.00, бэкапы лога с 01.00 и 02.00 - тоже все будет нормально. Такой вариант и предлагается в . Единственный косяк - если вдруг что-то поломается после бэкапа с TRUNCATE_ONLY и перед нормальным бэкапом лога, то восстановиться на момент времени перед сбоем будет нельзя.

предложи свой, отличный, вариант объяснения

читай до просветления. Вкратце: дифференциальный (который тут почему-то называют разностным) и полный бэкапы НЕ НАРУШАЮТ цепочку журналов, но МОГУТ ее НАЧАТЬ.

ТС уже предлагали попробовать повосстанавливать базы из того бэкапа, который он настроил и делает )))))

у меня нет к тебе вопросов, объясни или прочитай хотя бы и потом ответь на

хотя не надо, в этой фразе всё твоё понимание "Единственный косяк - если вдруг что-то поломается после бэкапа с TRUNCATE_ONLY и перед нормальным бэкапом лога, то восстановиться на момент времени перед сбоем будет нельзя."

да потому что журнал после полного или диф бэкапа действительно НЕ УРЕЗАЕТСЯ, блин.

ладно. Типа разобрался. Спасибо большое. Только вот теперь никак не пойму. Делаю use my_database go dbcc shrinkfile(N'my_database_log') запускаю эти команды в запросе в mssql server management studio - все работает, ldf уменьшился с 7 гигов до 700 килобайтов. Но если это же точно также написать в "планы обслуживания"->выполнение инструкции T-SQL, то он не выполняется, ругается что по базе "master" не найден файл my_database_log. Т.е. типа use не срабатывает чтоли, и не переключается на нужную мне базу, или в ситнаксисе что то не так? Никак не пойму

не увлекайся мелкими логами. Сервер будет дергать диск приращивая лог файл, когда ему потребуется. Размер лог по умолчанию нужно поставить в model. И размер его нужно резать до 10-30% от размера базы(размер процента зависит от возможностей).

не совсем понял. Что значит поставить в model. Где это. И что получается, по мере роста базы мне каждый раз менять регламентное задание, изменяя размер сжатия? А что если я вообще не буду никогда выполнять эту команду dbcc shrinkfile? И еще, в моим главным вопросом было следующее: почему в query эта команда срабатывает, а в  "планы обслуживания"->выполнение инструкции T-SQL - нет.

какая у вас версия sql server (select @@version) и management studio (help->about)? Возможно имеет смысл их обновить, с maintenance plans, емнип, были проблемы в старых версиях - rtm, sp 1, sp 2.

а размер базы можно увидеть? а то может там база 5 гигов и стока заморочек с бэкапами ни к чему

у меня так и всё работает USE [databasename] GO DBCC SHRINKFILE (N'databasename_log' , 0, TRUNCATEONLY) GO

ты вроде и сам в курсе, какие транзакции попадают в бекап те, что ДО бекапа, диф или полного, _не попадают!!11..

я уже запутался. В какой бэкап? Если в бэкап лога, при том что цепочка журналов не повреждена - то попадают. В бэкапе лога будут ВСЕ транзакции, "прошедшие" после предыдущего бэкапа лога! Если раньше бэкапа лога не было, или цепочка журналов была нарушена (BACKUP LOG WITH TRUNCATE_ONLY, переход в SIMPLE и обратно) - то в бэкап лога попадут все транзакции, "прошедшие" после полного или диф бэкапа.

как ты думаешь, почему растет лог, если делать только полные/диф бэкапы, но не делать бэкап лога?

:) база пока что всего 4 гига. Но я хочу щас все подготовить на будущее

у меня база 30 гб full backup занимал 50 секунд, когда в базе никого

"В бэкапе лога будут ВСЕ транзакции, "прошедшие" после предыдущего бэкапа лога!" нет, ты не прав =)

заеbись аргументация. на, осмысливай: как ты считаешь, как у меня получилось восстановить базу данных на момент времени между первым бэкапом лога и вторым полным бэкапом, если во втором бэкапе лога нет этих транзакций? Сейчас, правда, подумал, что надо было восстановить не перед, а после удаления, но с этим ты уже и сам сможешь поиграться и все попробовать.

думаешь правда интересно твои мультики смотреть? времена в бэкапе лога > времени бэкапа дб слово цепочка ты уже выучил

Люди, а если я перешел на 2008 Р2, как мне обойти backup log with truncate_only?