Всем здравствуйте! Прошу помочь.
Дано: SQL 2008, база v8.1 (БП ред.1.6). На SQL настроены два Плана поддержки:
1. в 00:02 ночи делает full backup + backup журнала транзакций (иначе не получалось делать shrink для журнала);
2. второй в 02:30 делает несколько действий - перестройка индексов, обновление статистики, проверка целостности БД.
Первый план выполняется без проблем. А второй постоянно выдаёт ошибку. Прилагаю лог:

10/12/2010 02:30:00,test.Subplan_1,Error,1,TERMINAL,test.Subplan_1,Subplan_1,,Executed as user: KUNCEVO\administrator. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  2:30:00  Progress: 2010-10-12 02:30:01.20     Source: {AFE21A48-0CFE-4E66-9D54-E2B5890539A5}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2010-10-12 02:32:09.84     Source: Rebuild Index      Executing query "USE [base1]  ".: 0% complete  End Progress  Error: 2010-10-12 02:32:10.23     Code: 0xC002F210     Source: Rebuild Index Execute SQL Task     Description: Executing the query "ALTER INDEX [_Acc6_ByCode_SR] ON [dbo].[_Acc6] REB..." failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.". Possible failure reasons: Problems with the query<c/> "ResultSet" property not set correctly<c/> parameters not
set correctly<c/> or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:30:00  Finished: 2:32:10  Elapsed:  129.828 seconds.  The package execution failed.  The step failed.,00:02:12,0,0,,,,0

Подскажите, куда копать. Поиск по инету не принёс результатов нужных.

"Online index operations can only be performed in Enterprise edition of SQL Server"

Копай с переводчика на русский и сравнения возможностей разных версий скуля.

Дословно данное сообщение означает "операции с индексами онлайн работают только в Интерпрайз"

Видел это сообщение. Но смутило вот эта фраза (продолжение):
Possible failure reasons: Problems with the query<c/> "ResultSet" property not set correctly<c/> parameters not
set correctly<c/> or connection not established correctly

Выходит, что якобы ошибка в запросе? Но я пользовался Помощником создания плана.

Если следовать описанию причины, то получается, что в Standart версии (которая стоит у нас) нет возможности работы с индексами в режиме он-лайн. А как ещё можно? Отключив базу?

В любом случае попробую покапаться в сравнении версий SQL...

снять галку "keep index online while reindexing" в свойствах rebuild index task.
И я бы, на вашем месте, поставил модель восстановления simple, раз все равно шринкуете журнал транзакций

хотя, нет, simple не надо, если вы лог бэкапите без TRUNCATE_ONLY, как это здесь любят делать... А зачем, кстати, вы ему делаете shrink? любите ждать пока он снова подрастет? Во время "наращивания" ldf файла база становится недоступной для записи...

Спасибо за подсказку - попробую снять галку (не сообразил про неё).
Shrink делаю, чтобы не разрастался журнал. А вы как рекомендуете делать shrink? С какой периодичностью?

я рекомендую не делать шринк вообще :). Вместо этого, имхо, лучше делать резервные копии журнала транзакций. Это поможет дорасти вашему журналу транзакций до определенного размера и остановиться в росте.

в смысле - чаще делать бэкапы журнала транзакций

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

жуть.. а что их "остановит"? :-)

(5) шринг базы нужно делать после фуллбэкапа

(9) с целью?
почему все 1с-ники так любят шринки? *задумчиво*

(10) э-э-э.... т.е. шринк придумали 1Сникти.. Правда?

(11) интересная у вас логика.. шринк может быть очень полезен в определенных ситуациях - после удаления больших объемов информации из БД, например. В повседневной жизни от него пользы ноль, а проблем с производительностью он может запросто прибавить.. и вы так и не ответили зачем НУЖНО делать шринк

(12) "а проблем с производительностью он может запросто прибавить"

да ну?? Это каким боком?

"зачем НУЖНО делать шринк?" - Ну-у-у.. например для того, что бы например не разрастался лог файл.. т.к. транзакции всегда дописываются в конец, после последней записи..

(13) курите BOL для просветления.
Чтобы не разрастался лог-файл нужно регулярно делать бэкап журнала транзакций. Или поставить модель восстановления simple - лог вырастет до нужного размера и остановится в росте (как и с бэкапами лога).
Производительность - здесь несколько вариантов. Во-первых, это фрагментация данных (курите в бол про shrinkdatabase/shrinkfile), негативно сказывающаяся на скорости чтения данных, во-вторых - во время того как sql server увеличивает журнал транзакций, база становится как бы "read-only", что приводит, помимо прочего, к удерживанию всех блокировок теми транзакциями, которым надо "записаться" в журнал транзакций, что тоже может быть "чревато".
И большое спасибо, что рассказали куда и как дописываются транзакции.

(14) покурите BOL о пользе делать шринк при открытых конектах и при не закомитченных транзакциях - может меньше будете дальше нести чушь про "red-only"

" Или поставить модель восстановления simple - лог вырастет до нужного размера и остановится в росте "


Бу-га-га-га... Что бы рассказывать про вкус устриц, их нужно ЕСТЬ самому, а не разглядывать про них картинки...

Т.е. Никогда не видели как растёт лог-файл при симпл-модели восстановления после восстановлений баз из копий??? Ну-ну.. тогда продолжайте разглядывать картинки с BOL

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

" можно ссылочку на "пользу шринка при открытых коннектах и не закоммиченных транзакциях"? чет я вас не понимаю"

вы правду делаете шринк на базе на которой работают активно юзеры??? Хм-м-м... а bol плчитать для этого случая...

"видел и причина этому "открытые коннекты и незакомиченные транзакции", "

неужели так сложно воспринимать написанное? Я указал одну из причин роста лог-файла при симпл-моде восстановлении - восстановление базы из бэкапа (restore)

У вас действительно так бывает, что при восстановлении базы из бэкапа в этой базе есть "открытые конекты и незакамитченные транзакции"???


ё-ё-ё-ё.. и правда.. воинствующий дилетантизм - не лечится даже поверхностным листанием BOL...

(17) А, вы вон про что. Так при восстановлении из бэкапа - файл ldf становится того же размера, что и был на момент создания резервной копии. Это как бы факт, сорри, я вашу фразу не понял. Сам по себе он не увеличивается, сделаете шринк перед бэкапом - после восстановления бэкапа получите файл лдф с минимальным размером.
Имхо, виновато не мое восприятие, а ваш стиль изложения мыслей, мне непонятный.
Что же имелось в виду под "покурите BOL о пользе делать шринк при открытых конектах и при не закомитченных транзакциях - может меньше будете дальше нести чушь про "red-only"?
Вы хотели сказать, что когда люди работают не надо делать шринк?? Так это само собой. Но вы сделали шринк, а после этого люди начали работать - транзакции будут "писаться" в лог, места будет не хватать он будет расширяться. Во время расширения словите проблемы.

(18) "Сам по себе он не увеличивается, сделаете шринк перед бэкапом "

Именно что увеличивается.. так как база что ДО бэкапа находится в симпл-моде, что ПОСЛЕ бэкапа находится там же... Лог-фал имеет тенденцию разрастаться при больших заливках даже для симпл моды. И тут весьма рулит именно ШРИНК. Но так как база в симпл-моде, то никакого "разрастания при обычной работе юзеров НЕ ПРОИСХОДИТ поэтому Ваше утверждение насчёт производительнсти (повтояю в энный раз) чушь

Затем.. есть такой параметр - как величина увеличения базы. Если эта величина установлена ПРАВИЛЬНО, то для юзеров никаких особых издержек при увеличении размера базы НЕ ПРОИСХОДИТ.

ЗЫ.. ну и естественно автошринк - это зло :-)

>Затем.. есть такой параметр - как величина увеличения базы. Если эта величина установлена ПРАВИЛЬНО...

А как правильно определить параметр?

(20) наблюдением за ростом базы и пониманием логики процессов... Например очень важный параметр - Есть ли массовые импорты и если есть, то насколько они большие и частые. Если особых импортов нет, то опять же - вопрос религии... как вариант - что бы "разового увеличения" хватало на месяц работы пользователей без повторного запроса на увеличения работы в дат - файле. Размер лог-файла должен в принципе позволять работать без такого увеличения хотя бы в течении суток

ЗЫ.. всё вышесказанное - сильно усреднены и есть точка зрения на моей практике с моими БД для 1С. Безусловно истинной в последней инстанции не является :-)

(19)
>>Лог-фал имеет тенденцию разрастаться при больших заливках даже для симпл моды
А я с этим спорю? Конечно имеет, только восстановление из бэкапа не имеет отношения к этим операциям. Размер лог-файла восстановленной из бэкапа базы всегда имеет тот же самый размер, что и был на момент создания бэкапа. Извините, но говорить, что восстановление из бэкапа приводит к увеличению лог-файла - вот это чушь. При каких условиях я смогу это (разрастание при восстановлении из бэкапа) смоделировать? Какая должна быть база (какие размеры или от чего это с вашей точки зрения зависит)? Я запишу видео и выложу на ютуб.
Что касается проблем с производительностью - объясню еще раз. Какого бы размера лог файл не был - при его увеличении - получится кратковременный тормоз. То что на вашей нагрузке можно не бояться, что лог-файл  увеличится (разрастания при обычной работе юзеров НЕ ПРОИСХОДИТ) - прекрасно.

+(22) и, кстати, необходимость шринкования файла журнала транзакций про простой модели восстановления, как раз говорит о том, что он-таки разрастается. Пусть даже во время импорта данных - это как бэ не очень хорошо, хотя решать вам, конечно.

>наблюдением за ростом базы и пониманием логики процессов... Например очень важный параметр - Есть ли массовые импорты и если есть, то насколько они большие и частые.

А пример из жизни не могли бы привести: например, база с массовыми импортами определённого объёма и определённой периодичности роста и т.п. и при этом используются такие-то оптимальные параметры увеличения базы, ещё что-то?

(24) всё делается "по месту".. Универсальной таблетки счастья нет

набирайте статистику поведения базы... смотрите, где узкие места... Давать абстрактные советы - весьма опасная вещь

Ясно. Спасибо за подсказки. А вы в планы поддержки ставите shrink database?


Упс'у тоже благодарность - сняв галку "keep index online while reindexing" задание всё-таки запустилось. Уже пару часов крутится - всё никак не закончится.

(26) я - нет...

В итоге план поддержки №2 был запущен часов в 21:00 вчера руками и так и не завершился по текущую минуту. Прервал руками. Я так понимаю, это ненормально? Сама база занимает чуть более 9 Гб. Или план, включающий перестройку индексов, обновление статистики, проверку целостности БД может длится так долго?

(28) может... дурное дело не хитрое.. посмотрел, каких ресурсов нехватало?

насколько была высока утилизация CPU, memory, насколько длина было очередь обращения к процессорам и к дискам?

Посыпаю голову пеплом... Оборвал задание, не глядя. В пятницу вечером запустится задание и займусь этим (на выходных без пользователей), чтобы юзеров, если что, не беспокоить.

Итог вечернего пятничного плана2 (ничего не понимаю). Порядок действий по расписанию стоял такой:
1. rebuild indexes
2. update statistics
3. очистка процедурного кэша (DBCC FREEPROCCACHE)
4. check database integrity

Смотрю по истории выполнения задания. Порядок по времени совсем другой (выполняться задание начало в 23:30):
00:00:02 очистка кэша
00:05:45 update statistics
07:48:00 check database integrity
00:54:45 rebuild indexes

Почему так произошло?? Почему порядок следования не соблюдён расписанием? Расписание стояло одно на все задания (не раздельное). Последовательность именно, как указал выше я настроил.
Прошу помощи сведущих.

Размер файла базы данных увеличился с 9 с небольшим Гб до 14 с лишним, лог-файла - в разы.

(31) а нафига делать апдайт статистики после ребилда???

Если быза слабо растущая, то делаешь апдайт статистики ну раз в неделю, а ребилд индексов раз в год :-)

(32) посмотри, что именно "скушало" 5 гигов. Как вариант - дурацкие индексы.. Если так - то пересмотри насколько такие гиганские индексы полезны.

Внесу корректировки в планы. Похоже, индексы виной роста базы.
Такие вопросы:
1. сейчас чтобы уменьшить размер базы и лога, что лучше сделать? Забекапить базу и шринкнуть (имеет ли смысл)?
2. и почему всё-таки последовательность заданий оказалась нарушена?
3. если в свойствах базы стоит авто апдейт статистики, то нужно ли вообще обновлять статистику?

(33) "а нафига делать апдайт статистики после ребилда???"

Ты тоже удивишься, но статистики собираются не только по индексам

(36) удивлюсь.. а по чём ещё?

(37) По любым колонкам может.
Почитай про create statistics, узнаешь много поразительно новой информации

(35) если это виноваты индексы, то можно лишь "лишнее убить" - тут шринк однозначно не поможет

(38) продолжай... и что даёт (кто и как использует) это "обновление по столбцам"?

(40) Использует сам сервер, ты не поверишь...

(41) чему не поверю??

Насколько я понимаю, у тебя вызывает затруднение понимание то с чем работает "статистика" - а именно с "on one or more columns of a table or indexed view"

И на основании этого ты считаешь, что "появится что-то новенькое" если после ребилд индексов сделать апдаёт статистикс - правильно я понимаю твою мысль?

(35) почитай тут - http://www.sql.ru/articles/mssql/2005/081301StatisticsInSQLServer2005.shtml

SQL Server 2005 имеет много обслуживающих статистику механизмов. Самый важный из них - это возможность автоматически создавать и обновлять статистику. Этот механизм задействуется по умолчанию в SQL Server 2005 и SQL Server 2000. Приблизительно 98 % инсталляций SQL Server 2000 оставляют задействованным механизм автоматического обновления статистики, что принято считать хорошей практикой. В большинстве приложений баз данных, разработчики и администраторы могут положиться на автоматическое создание и обновление статистики, которое в достаточной мере обеспечивает всестороннюю и точную статистику данных, по которой оптимизатор запросов SQL Server 2005 выбирает хорошие планы исполнения, и в то же время снижаются затраты на разработку и администрирование. Если же Вам нужно в большей мере управлять созданием и обновлением статистики, чтобы получить боле соответствующие Вашим нуждам планы исполнения запросов, или более тонко управлять сбором статистики, Вы можете использовать ручное создание и обновление статистики.


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

(31) А можете сделать скриншот своего плана и выложить куда-нибудь на радикал? Такое чувство, что у вас все вообще одновременно начало выполняться.. Проверка целостности базы данных не должна идти больше чем перестройка всех индексов..
Файл данных у вас раздулся из-за перестройки индексов. Изначально, когда создаются таблицы и индексы, данные в файле лежат как бы "вполтную" друг к другу. Все вставки данных, соответственно, идут в конец файла - появляется фрагментация. Когда вы перестраиваете индекс он пересоздается таким образом, чтобы данные, принадлежащие одной таблице (или индексу) снова лежали друг к другу "вплотную". Таким образом, в файле данных у вас сейчас есть "дыры" - в этом нет ничего плохого - SQL Server со временем заполнит их данными. Если вы сейчас сделаете DBCC SHRINKDATABASE\SHRINKFILE - ВЕСЬ эффект от перестройки индексов потеряется - данные буду сжаты в кучу, снова появится фрагментация, поскольку SQL Server не будет разбираться в том к какой таблице/индексу принадлежат данные, а будет переносить их из конца файла данных в "дыры" в начале файла. (это весьма приблизительное описание)
Лог тоже не обязательно сжимать - если вы будете достаточно часто делать его резервные копии он больше расти не будет. Если вы его-таки сожмете, при следующей перестройке индексов он у вас снова вырастет до текущего размера.
(34) Перестройка индекса не может увеличить его размер, если не указывалось значение fill factor/pad index отличное от 100% (а сильно сомневаюсь, что ТС его менял).
(42) Есть статистика, создаваемая SQL Server'ом автоматически (у вас ведь тоже стоит галочка Auto Create statistics?) - эта статистика по неиндексированным столбцам, участвующим в отборах в запросах. Ее обновление в ходе перестройки индексов не производится.
Ну и то что вы пишете в (43) про отсутствие необходимости обновлять статистику - это, опять-таки, видимо, приемлимо для вашей нагрузки, но необязательно будет приемлимо для нагрузки ТС. SQL Server автоматически обновляет статистику по столбцу в том случае, если в нем изменилось/добавилось, ЕМНИП, около 20% строк - это довольно много. 1С сама рекомендует обновлять статистику ежедневно (а то и чаще).

"1С сама рекомендует обновлять статистику ежедневно (а то и чаще)."

Не будите столь любезны, дайте плз ссылочку на эту рекомендацию.

Заранее благодарен за Ваше внимание.

(45) + Кстати.. и кстати, я к сожалению так и не увидил, обоснование "полезности" делать обновление статистики непосредственно ДО или сразу ПОСЛЕ ребилда индексов - неужели о таком существенном моменте Ваш BOL молчит?

И ещё в тему (44)

"но необязательно будет приемлимо для нагрузки ТС. SQL Server автоматически обновляет статистику по столбцу в том случае, если в нем изменилось/добавилось, ЕМНИП, около 20% строк - это довольно много"

Обращаю внимание, что мы говорим про базы сиквела "обслуживающие" потребности 1С предприятия. Так? Тогда если не трудно, приведите плз пример таблицы для типичной базки в 5 гб (это средняя бухия среднего предприятия) которая в течении дня может так вырасти (более чем на 20%)?
Мои наблюдение показывают что основные таблицы обслуживающие критические важные с почки зрения производительности бизнес-процессы в день растут ну максиму на десятую долю процента.

У вас иначе? Приведите пример где и что и проссмотрел.

(45) Ссылочку - запросто: http://kb.1c.ru/articleView.jsp?id=13 (http://palsoft.oxnull.net/2010-09-05-07-16-48/29--ms-sql-server- полностью содранная оттуда статья, если у вас нет доступа к базе знаний)
Цитата оттуда: "Оптимальная частота обновления статистик зависит от величины и характера нагрузки на систему и определяется экспериментальным путем. Рекомендуется обновлять статистики не реже одного раза в день."

(46) "мой BOL" вообще молчит на тему порядка выполнения различных операций. И совершенно неудивительно что вы "не увидил обоснование "полезности" делать обновление статистики непосредственно ДО или сразу ПОСЛЕ ребилда индексов" - нет такого обоснования. Порядок этих операций не должен зависеть друг от друга - они решают разные задачи, но "побочным эффектом" от решения одной задачи (ребилд индексов) является частичное решение другой (обновление статистики).

"Тогда если не трудно, приведите плз пример таблицы для типичной базки в 5 гб (это средняя бухия среднего предприятия) которая в течении дня может так вырасти (более чем на 20%)?"
Я, если вы не поняли, говорю, что 20% это довольно большой трэшхолд для обновления статистики. На большой таблице, иногда, достаточно намного меньшего "процента" для того, чтобы хороший план выполнения запроса стал очень плохим. Для базы в 5 Гб сейчас может и не будет проблем, но через какое-то время проблемы могут начаться. Пример я вам привести не смогу, в нашей переписанной конфе есть справочник, в который раз в сутки добавляется около 1-2% записей и столько же удаляется (т.е. размер его практически не меняется). Если своевременно не обновить статистику - все запросы, в которых есть обращения к нему, скатываются в сканы.

Опять-таки, как я уже писал здесь (1c7 и SQL, я не делаю ребилд всех индексов, а только тех, уровень фрагментации которых больше 30 %. Возможно, при ребилде абсолютно всех индексов, все необходимые статистики будут обновлены и обновление автоматически созданной статистики можно оставить для автоматического обновления. Но это надо тщательно проверять. И чтобы не наткнуться на проблемы, лучше обновлять статистику каждую ночь, если это не мешает работе пользователей - вреда от этого не будет.

И, кстати, вы может быть все-таки расскажете как я могу промоделировать ситуацию с восстановлением бэкапа базы в простой модели восстановления и ужасным разрастанием ее лога от этого? У меня есть базы в 3 гб и в 90 Гб, я смогу на них увидеть этот чудо-эффект?

+(47) "Пример я вам привести не смогу" = "Типовой пример я вам привести не смогу"

(47)

"Пример я вам привести не смогу, в нашей переписанной конфе есть справочник, в который раз в сутки добавляется около 1-2% записей и столько же удаляется (т.е. размер его практически не меняется). Если своевременно не обновить статистику - все запросы, в которых есть обращения к нему, скатываются в сканы. "


1. хм-м-м.. я же говорю.. Мы всё  время говорим о разном. Я Вам говорю про поведению баз с дороботанными типовыми конфигурациями, Вы упорно приводите примеры "самописок".

2. Вы серьёзно утверждаете, что вставка/удаление на 1-2% содержимого таблицы уже настолько БЕЗНАДЁЖНО "портит" статистику, что черевато уже "скатываюним в сканы"??? Это что-то новенькое (если так, что можно что-нибудь повещественнее, чем такие "экспертные оценки"?).

3. Вы наверное почему-то не слышали про такую фичу сиквела:

В SQL Server 2005 имеются следующие возможности работы со статистикой:

implicitly create and update statistics - фоновое создание и обновление статистики с заданной по умолчанию частотой обновления (в командах SELECT, INSERT, DELETE и UPDATE, использование столбца в условии WHERE или в JOIN приводит к созданию или обновлению статистики, если это необходимо, и при условии, что включено автоматическое обновление).


" И чтобы не наткнуться на проблемы, лучше обновлять статистику каждую ночь, если это не мешает работе пользователей - вреда от этого не будет"

хм-м-м.. а может всё же наоборот? понаблюдать и убедиться что частоты обновления "раз в неделю" хватает с головой?


"У меня есть базы в 3 гб и в 90 Гб, я смогу на них увидеть этот чудо-эффект?"

Мы идём от противного.. есть реальное наблюдение над реальными базами которые живут только в СИМПЛ-режиме, но вот лог-файл их растёт и довольно существенно. Делать бэкап лога (по вашему совету) базы находящейся в СИМПЛ-режиме - это безусловно наверно занимательно, но на мой скромный взгляд - явный изврат.

ЗЫ.. или вы никогда не видели рост лог-файла у баз находящихся в СИПМЛ-режиме восстановления?

>А можете сделать скриншот своего плана и выложить куда-нибудь на радикал?
Сделал (скрин самого задания + скрины каждого пункта задания), ссылка: www.7721010.ru/downloads/prtsc_plan.doc
Обращу внимание, что сейчас там стоит расписание On demand - я отключил расписание. Было общее время начало для всего плана 02:30 ночи.

Кстати, попробовал формирование а базе обороток (ОСВ за месяц, квартал, полугодие, год) - всё влёт делается. До этого надо было ожидать по несколько минут.

(51) Поздравляю.. значит пляски с бубном не прошли зря :-))

(52) Меня только смущает увеличившийся размер базы и лога. Но, если я правильно понимаю, то расти они в ближайшее время не должны (с учётом активности в текущей БД).

(53) а дифиринциальные бэкапы делаете?

Какой план бэкапов?

Какой размер дат и лог-файла сейчас? Как они выросли за последнее время (какая динамика)?

(54) Как писал выше: делаю full backup затем log backup. Происходит автоматом в 00:02 ночи.
Размеры сейчас: дата около 14,1 Гб, лог 7,9 Гб. Выросли они в субботу после выполнения плана, скрин на который положил в сообщении (50).
Вообще сейчас переделал задание по примеру отсюда http://kb.1c.ru/articleView.jsp?id=13 (http://palsoft.oxnull.net/2010-09-05-07-16-48/29--ms-sql-server (только всё настроил пока на "раз в неделю" - посмотрю на поведение базы).

(55) а режим восстановления базы какой? Фулл, балк-логед или симпл?

(56) Режим восстановления full

(57) дифф бэкапы делаете в течении дня?

Какую задачу должны выполнять бэкапы лог-файла, когда уже есть полный бэкап базы?

(58) Только сейчас сообразил, что бэкап лога у меня - это атавизм.
Первоначально, при ковырянии с базой, я пытался уменьшить размер лога (как-то разросся он после какой-то операции). Уменьшить пытался шринком лога. Без бекапа лога шринк не получался. После бэкапа получилось шринкнуть лог как-то. В итоге сделал задание: бэкап файла данных, затем лога, затем шринк.
Затем, почитав тут разные ветки, и увидев негатив в отношении шринка, сам шринк отключил, а два бэкапа в результате остались.

Так понимаю, что сейчас достаточно оставить full backup + (раз в неделю обновление статистик + дефрагментация + реиндексация)?

(58) Да, забыл: дифф бэкапы в течение дня не делаю.

(59) вопрос конечно "религии" :-)

но я свою точку зрения уже излагал.. понаблюдав за СВОИМИ базами (с их динамикой роста и по данным наблюдений), я пришёл в выводу

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

ОК, я понял, спасибо за помощь.
Упс'у также выражаю благодарность - особенно за подробную роспись в сообщении (44).

(50) Странно.. Вообще должно было в заданном порядке выполняться, может быть какой нибудь из СU решит проблему.. Кроме того такой вопрос - вы осознанно поставили в ребилде индексов fill factor 50%? За счет этого вы в два раза раздули все свои индексы. Настолько ли активная вставка у вас идет?
(59) Если вы оставляете модель восстановления FULL - полных и дифф бэкапов недостаточно! Если вы не будете делать регулярно бэкапы лога он будет расти безостановочно! Или переводите базу в SIMPLE, или включайте в расписание бэкапы лога.

(49)
>>2. Вы серьёзно утверждаете, что вставка/удаление на 1-2% содержимого таблицы уже настолько БЕЗНАДЁЖНО "портит" статистику, что черевато уже "скатываюним в сканы"??? Это что-то новенькое (если так, что можно что-нибудь повещественнее, чем такие "экспертные оценки"?).

Во-первых, статистику ничего не портит - она просто устаревает и перестает отражать текущее положение дел. Index Seek выбирается оптимизатором в том случае если запрос получает не больше 10-15 процентов строк из таблицы. Надо ли вам пояснять, что не зная о том что в таблице изменилось 3-4 процента строк (а об этом заранее неизвестно, если не обновлять статистику), оптимизатор может решиить, что запрос будет возвращать не 10-13 процентов строк таблицы, а 13-17?

>> Вы наверное почему-то не слышали про такую фичу сиквела:

>>implicitly create and update statistics

Ага, а это не я вам пару постов назад говорил, что "граничным условием" для автоматического обновления статистики является значение около 20 процентов? Или это не я вам говорил, что АВТОМАТИЧЕСКИ СОЗДАННАЯ статистика не обновляется во время ребилда индексов? Именно автоматически созданную статистику нужно обновлять даже после ребилда индексов - она не обновится при ребилде..

>>Мы идём от противного.. есть реальное наблюдение над реальными базами которые живут только в СИМПЛ-режиме, но вот лог-файл их растёт и довольно существенно. Делать бэкап лога (по вашему совету) базы находящейся в СИМПЛ-режиме - это безусловно наверно занимательно, но на мой скромный взгляд - явный изврат.

Нет, мы не идем от противного. Мы идем от этой вашей фразы:
"Я указал одну из причин роста лог-файла при симпл-моде восстановлении - восстановление базы из бэкапа (restore)" в (17)
Я утверждаю что это бред. Восстанавливая базу из бэкапа мы получаем лог файл того же размера, что и был на момент создания бэкапа. Вы сейчас пытаетесь сказать, что ПОСЛЕ восстановления лог может расти. С этим спорить глупо и изначально речь была не об этом. Если вы мне расскажаете как я могу увидеть, что причиной роста лога является ВОССТАНОВЛЕНИЕ базы из бэкапа (как вы это говорите) - я принесу вам извинения и заткнусь надолго.
Кроме того я нигде не советовал делать бэкап лога при модели восстановления симпл. SQL Server просто не даст сделать такую глупость. Поскольку автор в самом начале сказал, что делает бэкапы лога, уже было понятно, что модель восстановления его базы FULL (да и вообще эта настройка выбирается по умолчанию).

>>хм-м-м.. а может всё же наоборот? понаблюдать и убедиться что частоты обновления "раз в неделю" хватает с головой?
Это вопрос религии. А если окажется что не хватает? Запускать в середине рабочего дня обновление статистики? Генерировать кучу i/o операций и жрать процессор почем зря? Хотя, конечно, может и хватить. Это как прикинуть, что бака бензина вам хватит на неделю и заправляться в воскресенье в 18.00. Так тоже можно, конечно, но я предпочту заехать на заправку еще и в среду, долить.

(63) >Кроме того такой вопрос - вы осознанно поставили в ребилде индексов fill >factor 50%? За счет этого вы в два раза раздули все свои индексы. Настолько ли >активная >вставка у вас идет?

Год назад ходил на курс "MS SQL 2008 для поддержки системы "1С:Предприятие 8": администрирование, оптимизация, обеспечение безопасности" в Учебном центре №3. Так вот там один из примеров по созданию планов обслуживания (для самостоятельной работы) описывал эти действия. Надо было создать план (восстановление индексов + обновление статистики + выполнение инструкции DBCC FREEPROCCACHE + проверка целостности БД). При настройке перестроения индексов предлагалось установить параметр "Изменить долю свободного места на странице" в значение 50%.

Вставка у меня не такая активная. Поэтому я и напрягся после получения такого результата.
Сейчас можно уменьшить размер (путём шринка, например, а потом провести ещё раз перестройку индексов, но не указывая параметр fill factor)? При этом сохранится ли скорость формирования отчётов, которая после всех моих кривых действий получилась?

(64) Если вы хотите иметь базу минимального размера вам нужно сделать следующее:
1. Сделать бэкап лога.
2. Перестроить индексы с fill factor'ом = 100%
3. Сделать бэкап лога, чтобы он у не вырос во время следующей перестройки индексов
4. Сделать DBCC SHRINKDATABASE (или DBCC SHRINKFILE с указанием NOTRUNCATE - это может быть даже лучше, поскольку не уменьшит лог => ему не придется расти при следующей перестройке)
5. Снова перестроить индексы - уже с таким fill factor'ом, который вам нужен
6. Сделать бэкап лога.
(чтобы не было заметно какого-либо падения производительности, все это желательно сделать за один раз, по крайней мере 4-6)
Если вы сменили модель восстановления на simple, бэкапы лога не нужны (можете перед п.1 перевести базу в SIMPLE, все сделать, а потом вернуть FULL и обязательно сразу же сделать полный бэкап).
А можете просто перестроить индексы с нужным филфактором, тогда файл данных у вас не уменьшится, но и расти какое-то время не будет.
Скорость выполнения отчетов, которая у вас получилась, должна сохраняться в том случае, если вы будете регулярно обновлять статистику и следить за фрагментацией индексов (причем фрагментация в меньшей степени влияет на скорость). Как часто вам надо будет это делать вы выясните опытным путем :).

ВАУ!!!! Что я вижу!!!

"4. Сделать DBCC SHRINKDATABASE"

Наверное "щось в лiсi здохло" (с) :-)))

ЗЫ.. ничего личного.. ничего личного.. любые совпадения случайны :-)))

(66) ответ на это ваше сообщение был дан еще в (12)
за сим, адьё. Смысла что-то Вам доказывать больше не вижу

(67) а ни нужно что либо доказывать . Нужно быть только последовательным в СОБСТЕННЫХ рассуждениях.

Это Ваша фраза в (12) - "шринк может быть очень полезен в определенных ситуациях - после удаления больших объемов информации из БД, например. В повседневной жизни от него пользы ноль, а проблем с производительностью он может запросто прибавить". Так?

В (65) нет ни слова, что было "большое удаление информации". Так? Да и выше ситуация, на которую Вы давали ответ операцию "большое удаление информации" явно исключало. Так?

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


ЗЫ по поводу иллюзий, что в "обычной жизни" шринк не нужен - Вам уже писалось много и приводился КОНКРЕТНЫЙ пример - сиквельная база находится в симпл-моде, но у этой базы дико растёт лог-файл при регулярном восстановлении из (тут сори за терминалогическую неоднозначность) dt-файла (т.е. безусловно корректнее говорить из файла выгрузки, а не бэкапа, как употреблял ранее я - приношу глубочайшие извинения).  Ну а далее, как Вы и сами прекрасно знаете - делать бэкап лог-файла в симпл-моде это нонсенс и тут только шринк рулит.

ЗЫЫ.. Собственно приведённое выше НЕ ЯВЛЯЕТСЯ докозательством. Это лишь мой личный опыт ни сколь не притендующий на абсолют, но данный форум это на мой взгляд и есть - "обмен опытом", а не "долбёжка разными доказательствами".

(68)у человека после п.4. идет очень уместный п.5

(68)
>>или признайте неправоту в (12), либо продалжайте фанатично отвергить шринк везде и вся "за исключением операций большого удаления информации".

и какое из слов "может быть очень полезен в определенных ситуациях" и "например" вам непонятно?
ТС сейчас "раздул" свои индексы (вы, кстати, практически угадали в (34) - респект, у меня скилл телепатии не на столько прокачан) в два раза => после перестроения их с fill factor'ом 100% у него освободится много места - не похожа ли эта ситуация на удаление большого количества данных? Как вы считаете? Где вы видите противоречие? Вы считаете, что уменьшение всех индексов в два раза можно отнести к "повседневной жизни"?
Я, кстати, в том же посте, предложил ТС и вариант без шринка (а в варианте со шринком написал что после шринка сразу должны быть перестроены индексы).

И где вы, собственно, увидели фанатизм? Я сформулирую свою позицю - я категорически против шринка на рабочей базе без последующего перестроения всех индексов. Шринк с последующим перестроением индекса допустим в нерабочее время, но приведет к тому, что перестроение индексов будет идти дольше (за счет того времени, которое SQL Server затратит на увеличение файла данных и лога), а выигрыша в размере файла данных и лога не будет, за исключением ограниченного количества случаев.
Вы видите здесь какие-то расхождения с моими словами в ходе всего топика?

>> при регулярном восстановлении dt-файла
Вот так согласен - лог конечно может вырасти. Правда не совсем понятно почему у вас все равно непрерывно растет лог. Если dt-шники одинаковые, лог не должен расти больше чем в первый раз (ну то есть загрузили dt, посмотрели размер лога, после следующей загрузки того же dt лог должен быть того же размера - если база в simple mode и не было шринка). И я по прежнему утверждаю, что если у вас ПОСТОЯННО растет лог в "симпл моде" - он дорастет до определенного размера и остановится. Шринком вы лучше не делаете.

>>делать бэкап лог-файла в симпл-моде это нонсенс и тут только шринк рулит
Вы путаете теплое с мягким. Бэкап лога в симпл-моде - это не носенс.. Это невозможная ситуация. Бэкап лога базы с моделью восстановления full делает то, что у базы в simple происходит автоматически при чекпойнте - усекает журнал транзакций (http://msdn.microsoft.com/ru-ru/library/ms189085.aspx). Шринк - отдает место, занимаемое файлом, операционной системе.

"И я по прежнему утверждаю, что если у вас ПОСТОЯННО растет лог в "симпл моде" - он дорастет до определенного размера и остановится. Шринком вы лучше не делаете"

э-э-э.. ну тут как вариант, что остановится через изчерпание места на диске... Я как то хотел померять "пределы такого роста. После того, как при 5 гиговой баз такой "апендикс" лог-файла (а база повторюсь в симпл-моде) лолетел до 30 гигов. Я перестал извращаться "наблюдениями" и просто шринку такие апендиксы.
И это "обычная жизнь" и это "без массированных удалений"

ЗЫ.. это мой опыт. Если у вас он другой - я рад за вас :-)

(65) Проделывал действия рекомендуемые. Дошёл до пункта:
4. сделал SHRINKDATABASE (правой кнопкой по базе - tasks - SHRINKDATABASE) - размеры ни лога, ни даты не изменились (по отельности попробовал - то же самое).
Потом всё доделал - и размер не уменьшился, а то и подрос.

Чего я не так накрутил?

А не одолжит ли кто скриптов для перестройки индексов и шринка (эти dbcc ...)?

(72) с каким филфактором вы перестраивали индексы в первый раз (перед шринком) и с каким во второй? Если выбирали пункт "использовать процент заполнения страниц по умолчанию", то индексы могли перестроиться с тем же филфактором (50).
Когда делали shrinkdatabase ставили галку "reorganize files..."? Если не ставили, то ничего уменьшиться и не могло.

Скрипты.. А чем вас планы обслуживания не устраивают?
Вообще, так, навскидку, что-то типа этого (не проверял, где-то могут быть ошибки.. в частности, не совсем уверен в sp_msForEachTable, может придется что-то изменить):

USE [your_base]

EXEC sp_msForEachTable "ALTER INDEX ALL ON '?' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 0)"

BACKUP LOG [your_base] TO DISK = 'путь'

DBCC SHRINKFILE ('логическое имя файла данных', 0, NOTRUNCATE)

DBCC SHRINKFILE ('логическое имя файла данных', 0, TRUNCATEONLY)

EXEC sp_msForEachTable "ALTER INDEX ALL ON '?' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 0)"

DBCC SHRINKFILE ('логическое имя файла ЖУРНАЛА', 0, TRUNCATEONLY)

BACKUP LOG [your_base] TO DISK = 'путь'

Последний шринк (для журнала транзакций) обрежет свободное место в конце файла журнала транзакций (если оно осталось). Оставшегося размера должно, в принципе, хватать для перестройки всех индексов, т.е. расти не будет, при своевременных бэкапах журнала транзакций. Со временем он снова подрастет, когда увеличится размер индексов. Ну и еще раз скажу, что на время этих операций можно перевести базу в симпл, а потом сразу вернуть в full и сделать полный бэкап (но в этом случае, ИМХО, шринк журнала транзакций лучше не делать - он у вас станет минимального размера).

(73) блин.. везде где написано PAD_INDEX=ON должно быть PAD_INDEX = OFF (с таким филфактором не существенно, но лучше ставить OFF)

(73) На счёт перестройки индексов с fill factor: возможно, я что-то не так делал. Воспользовался планом обслуживания, выбрав задание rebuild. Там попытался указать параметр Change free space per page percentage to - если ставишь ноль, кнопка ОК становится недоступна (серой), а трёхзначное значение поставить не даёт (только двузначные). Т.е. 100 я поставить не смог. Вот и сделал по дефолту, как вы правильно предположили.
Галку "reorganize files..." при шринке пробовал ставить.

В общем, проблема похоже в том, что всё-таки не так сделал перестройку индексов. Но почему так получилось - описал выше.
Остаётся то тогда, делать перестройку через запрос? Например, как предложили вы:

USE [your_base]

EXEC sp_msForEachTable "ALTER INDEX ALL ON '?' REBUILD WITH (PAD_INDEX = OFF, FILLFACTOR = 0)"

Вообще я подумал о варианте проще: в задании плана обслуживания можно всегда посмтреть текст запроса по кнопке T-SQL. Скопировать скрипт, созданный сиквелом, а дальше выполнить запрос, только указав нужный мне fill factor.
Взлетит?

(76) Да, должно взлететь.

(65) Итак, наконец проделал всё. Не без неожиданностей, как водится.
На пункте 4 (shrink database) через небольшое время вылетела ошибка: Cannot shrink log file 2 because the logical log file located at the end of the file is in use (Error 1222). Запустил повторно - операция завершилась успешно. Размер базы данных уменьшился почти в два раза (с 14 Гб до 7 с лишним). А вот лог (подросший за последние пару дней) вырос с 8 с лишним до 10 Гб. Попробовал сделать shrink file - log. В диалоге сиквел написал, что свободного места 99%. Я указал shrink to 5000 mb. И лог уменьшился почему-то только до 7 с лишним. Не понял я почему так (??).
Затем сделал update statistics (на всякий случай). Скорость отчётов осталась шустрой.
Хоть и есть некоторые непонятки, но главное результат.
Упсу моя благодарность за школу и всем откликнувшимся.

P.S.: однако не просто с сиквелом...

(78) >>В диалоге сиквел написал, что свободного места 99%. Я указал shrink to 5000 mb. И лог уменьшился почему-то только до 7 с лишним. Не понял я почему так (??).

Физический файл журнала транзакций состоит из более мелких виртуальных файлов журналов (VLF). Shrink, с конца, удаляет все неактивные VLF до тех пор пока не наткнется на активный VLF (тот, в который в данный момент ведется запись транзакций). В вашем случае, shrink удалил все некативные VLF, нашел активый и хотя перед этим активным VLF остались только неактивные, он остановился. Чтобы узнать последний активный VLF (т.е. размер, до которого вы можете в данный момент сократить лог) используйте DBCC LOGINFO в контексте нужной базы данных. У активных VLF статус = 2.

(78) И нет никакой возможности сжать лог дальше активного VLF?

(80) Есть. Журнал транзакций заполняется "по кругу", т.е. после того как в самом последнем активном VLF (в самом конце файла журнала транзакций) закончится место, SQL Server посмотрит в начало файла и если увидит, что первый VLF уже не активен, он будет писать в него.
Чтобы VLF'ы "перестали быть" активными нужно сделать бэкап журнала транзакций. Если этого не делать - SQL Server не сможет повторно использовать место в файле ЖТ и, соответственно, файл будет расти безостановочно. Это касается моделей восстановления FULL и BULK-LOGGED, в модели восстановления SIMPLE VLF становится неактивным (и, соответственно, пригодным к повторному использованию) сразу же после того как все транзакции, записанные в нем, будут закоммичены (ну или откачены).
В вашем случае нужно просто немного подождать, посмотреть через DBCC LOGINFO что активные VLF появились в начале файла, сделать бэкап журнала транзакций и обрезать лог до требуемого размера (но учтите, что при следующем перестроении индексов он, скорее всего, снова вырастет). Если вы не делаете регулярные бэкапы журнала (а как и говорили ранне только раз в сутки), скорее всего у вас будет много активных VLF - можете посмотреть в каком порядке они заполняются по FSeqNo - в VLF с максимальным FSeqNo идет запись в данный момент.

Т.е. ещё в целом имеет смысл делать бэкап лога чаще, чем раз в сутки?

(82) Зависит от системы. Частота бэкапов, в первую очередь, должна определяться тем, за какой промежуток времени вы готовы потерять данные при форс-мажоре. Если потеря информации за сутки для вас не критична и увидите, что при бэкапе лога раз в сутки лог-файл у вас не растет - оставляйте как есть

Многоуважаемый Упс, столкнулся с такой проблемой: сильно вырос лог-файл (сейчас 20 с лишним Гб). Бэкап делается, как писал раньше. А почему так разрослось?

(84) Логи резко вырастают при интенсивной реорганизации данных. Например, при реиндексации.

(84) Что покажет:
select name, log_reuse_wait_desc from sys.databases
DBCC SQLPERF('LOGSPACE')
?

Выдало две таблицы:
1. в первой по моей базе в колонке "log_reuse_wait_sesc" написал значение "LOG_BACKUP";
2. во второй (нижней таблице) напротив базы написал
 Log size (Mb) 20248,55
 Log space used (%) 0,8610568

(87) Я сейчас еще раз ссылку посмотрел по которой вы план обслуживания настраивали - у вас в одном плане есть и Reorganize Index Task, и Rebuild Index Task? Если есть - то все нормально, просто вам нужно будет делать бэкап журнала транзакций после завершения одной из этих процедур (и, соответственно, перед началом другой). Хотя это неправильно, Reorganize Index Task (если я прав о его наличии) можно вообще убрать, оставив только Rebuild Index.

Я немного переделал с того плана рекомендации 1С (http://kb.1c.ru/articleView.jsp?id=13 (http://palsoft.oxnull.net/2010-09-05-07-16-48/29--ms-sql-server)), но, в целом, всё те же задания остались. На данный момент реализовано 2 плана (в каждом для каждого задания своё расписание):
1. выполняется каждый день - full backup в 00:02:00, затем в 00:30:00 log backup;
2. выполняется раз в неделю, в воскресение - update statistics (all existing statistics, full scan) в час ночи ->  reorganize index в 3 часа ночи -> rebuild index (default amount of free space) в пять утра.
Второй план настроил как раз по рекомендациииз рекомендаций 1С по ссылке выше.

Т.е. вы рекомендуете либо делать бэкап после одной (любой) из этих процедур, либо вообще убрать Reorganize Index?

(89) Не после любой, а после той которая идет первой (т.е., в вашем случае после reorganize index). Там, по этой ссылке, они рекомендуют делать дефрагментацию индексов каждый день, а перестройку раз в неделю, но эти операции не должны бы пересекаться - т.е. в один и тот же день не нужно выполнять и reorganize и rebuild. Rebuild удаляет индексы и заново их создает, т.е. от reorganize'a не будет никакого толка когда бы он не выполнялся (после rebuild'a индексы и так уже будут максимально "оптимизированы").
И зачем вам столько subplan'ов? Если у вас "окно" для обслуживания позволяет запускать все эти операции последовательно - почему бы их не запускать друг за другом (http://s014.radikal.ru/i329/1010/bd/52bc87f20bd0.png)? Так вы будете уверены, что операции не пересекутся по времени..

(90) Ссылка битая.
Вообще, можно и последовательно, но я выше писал, что у меня по каким-то причинам в прошлые разы, несмотря на установленную последовательность, задания "пошли" в разнобой. Вот и страхуюсь.
Попробую убрать reorganize. А с логом тогда проделаю шринк.

Подскажите, пожалуйста, люди сведущие: возможно ли при настройке Плана (бэкап делаю) указать сетевой путь, а не локальный? Когда указываю место расположения файла архива, то возможно выбрать только из локальных дисков, а нужно на другой сервер положить.

(92) Зачем? Тебе не жалко бекапа?

(93) Ветка-диагноз?

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

(95) скопируй

(96) Так и пришлось делать. В общем автоматизировали задачу путём того, что программка для общего бэкапа заодно забирает и папку с бэкапом лога и базы данных.

можно было б в джоб бекапа добавить шаг cmdexec