Для чего нужно делать бекап журнала транзакций SQL и нужно ли его вообще делать?

Чтоб он сбросил накопленный груз в основной файл

(1) Он "сбрасывает груз" по завершению транзакции. Точнее даже не сбрасывает, т.к. запись идет параллельно, но то уже нюансы. Тут несколько другое.

(0) Модель резервирования какая?

(3)
полная

(0) https://docs.microsoft.com/ru-ru/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-2017

(0) чтобы рестор сделать на любой момент времени (условно)

(0) Если база огромная, то частый бэкап - штука затратная (и по времени и по месту на диске)
А так - раз в неделю - полный бэкап
Каждый день - разностный
Каждые полчаса - журнал

И вуаля - ты можешь восстановить копию с точностью до получаса.

(7) Не до получаса, а вообще на любой момент кроме как посреди транзакции

(2) Имелось в виду чтоб не переполнился

(7) если есть бекап журнала, то можно восстановится с точностью до транзакции

чтобы лог лдф начал перезаписывать записи, попавшие в журнал, переиспользуя место занятое на диске под лог. Предполагается, что на сервере место ограничено и дороже чем где то в другом месте

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

(1) сброс грязных страниц из журнала в основной файл делается в момент чекпоинта
просто при бекапе журнала вызывается чекпоинт явно

(13) +
кроме этого, после бекапа журнала, страницы в журнале помечаются как доступные для удаления при шринке или для перезаписи

(13) Так с небольшими нюансами, которые совершенно не принципиальные. Но автор уже пояснил в (9), что он имел в виду несколько другое, но неточно сформулировал.

(13) (15) Да, пардон, спс за поправку

(12) "Не будете бекапить - он будет содержать в себе все транзакции"

Не совсем так.

Бэкап журнала транзакций это сохранения журнала транзакций.

После завершения транзакции информация о транзакциях хранится в журнале до того как вы не очистите информацию о транзакциях.

При бэкапе журнала транзакций он автоматически не очищается. Для очистки журнала транзакций есть специальная команда.

Бэкап журнала транзакций делается для того чтобы сохранить информацию о транзакциях. Поскольку журнал транзакций можно очистить и таким образом информация о о транзакциях будет потеряна.

(17) Это и есть нюансы :)

(0) Особого смысла в этом нет.

Поскольку за время пока вы обнаружите что с базой что то произошло пройдет время. И откат базы на состояние до изменения не будет иметь смысла.

Пример.

В 9 утра начали работу.  Есть ночной бэкап базы на 2 часа ночи.
В 10 часов программист по ошибке пометил на удаление 100 документов по контрагенту.

В 11 часов это обнаружили. за это время были выписаны 10 расходных, 20 приходных, и 5 ПКО.

Если откатывать по журналу транзакций базу на состояние на 10 часов то все изменения в базе сделанные с 10 до 11 часов затрутся.
А документы  же уже выписаны - номера, распечатывание и т.п.

Поэтому проще взять ночную копию, развернуть ее в резервную базу и из нее перенести в рабочую те докуменыт которые были ошибочно помечены на удаление.

С учетом  сериализации в восьмерке и возможности выгрузить документы и движения в XML это не сложно.

(0) Журнал транзакций нужен и полезен если в базе идет большой документооборот и часто возникает задача определить кто и когда сделал изменения и какие это были изменения. При наличии журнала транзакций  в таком случае можно смоделировать ситуацию которая была напрмер в базе на 10.25 перед выпиской конкретного документа. И ответить таким образом на вопрос почему - каике были остатки товаров на момент выписки, какая задолженность контрагента и т.п.

есть смысл вообще полную систему бекапирования использовать или лучше простую?

(22) Это зависит от потребностей вашей базы.

Мое мнение - нет. Описал почему в (20).

(22) Возможность восстановления на любой момент времени (иметь срез данных). Она не только для восстановления рабочей инфобазы во время чьего-то косяка нужна бывает.

(24) А для чего это еще нужно ? В чем польза этого ?

(25) Расследовать ситуации изменения данных, например, спустя более ощутимое время, чем сутки или даже неделя. Из-за недостаточной детализации сторонних средств журналирования, например.

(26) Для этого версионирования вполне достаточно. На мой взгляд.

Ну или все-таки сам клиент когда готов пожертвовать уже введенными данными и прямым текстом просит накатить ему в прод копию на такой-то час такую-то минуту

(27) Так это если оно ведется и с достаточной степенью детализации

(27) версионирование хорошо, когда знаешь в каком месте что поменяли. А когда известно лишь, обороты по таким-то счетам изменились, куда проще поднять бекапчик

(29) Так затраты на создание или включение этого версионирования будут значительно меньше чем на хранение журналов транзакций.

Причем при версионировнаи у вас срез состояния документа будет храниться пока вы ее не удалите.
А копия журнала транзакций устареет и вы ее удалите.

(30) "А когда известно лишь, обороты по таким-то счетам изменились, куда проще поднять бекапчик"

Так для этого ночной бэкап есть. Чем тут копия журнала транзакций поможет ?

(32) а, сорри. не вник в суть спора

(31) Никогда не знаешь, что нужно версионировать, а что нет

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

1. Проверь нет ли опции NO_TRUNCATE при BACKUP LOG
2. Усечение (truncate) при бекапе физически размер файла не уменьшает, а только помечает страницы в нем доступными для перезаписи/удаления.
Т.е. truncate - делает логическое усечение страниц в файле.
Физическое обрезание файла делается при shrink, но только в границах логического усечения

"журнал транзакций нарастает и на диске занимает больше места чем основная база, это нормально?" // Так и задумано

"Что с этим делать?" // Липосакцию

(35) Размер журнала транзаций и будет нарастать до тех пор пока вы не сделаете сначала операцию truncate а потом shrink

Операция truncate удаляет из журнала транзакций информацию о завершенные транзакции.

Операция shrink уменьшает физически размер файла транзакий.

Например у вас лог файл 2 Гб.

Все транзакции завершены. Например сейчас с базой никто не работает.

После операции truncate размер файла транзакий останется 2 Гб но вся информацию о завершенных транзакциях будет удалена.

После операции shrink размер файла транзакций станет 1 Мб (или другой размер который у вас указан в свойствах базы).

(0) Пояснение того что написано в (36)

>1. Проверь нет ли опции NO_TRUNCATE при BACKUP LOG

Если есть конструкция NO_TRUNCATE то при бэкапе лога информация о успешных транзакциях не удалится из лога.

>Физическое обрезание файла делается при shrink, но только в границах логического усечения

Если работа с базой не ведется (новые транзакции не добавляются) то truncate пометит все завершенные транзакции.

Соответственно после этого можно весь журнал зашринковать.

(35) Как говорил Василий Иванович: "Но есть нюанс!"
ваш сервер может решить, что вы совершенно напрасно не реплицируете свою базу на другой сервер и решит вам немного помочь.
Помогите усечь и сжать log MSSQL, перевод в Simple и шринк не дает результата

Кто подскажет вот какой момент.

В свойствах базы есть свойство "Авто сжатие".

Ее удобство в том что если используется простая модель восстановления то файл транзакций периодически уменьшается.

Вопрос в том - когда это происходит ? Насколько я знаю SQL сервер делает это самостоятельно.

Сколько читал по этой теме ничего не попадалось.

Единственно что попадалось это информация о том что

"Также возможно настроить базу данных на автоматическое сжатие путем выставления параметра БД AUTO_SHRINK в значение ON."

(42) https://books.google.ru/books?id=7DiOrj68d3cC&pg=PA569&lpg=PA569&ots=rwONjNDUKF&focus=viewport&dq=AUTO_SHRINK+в+значение+ON&hl=ru
только зачем

(43) То что файлы базы данных и журнала сжимаются автоматически при установленной галочке "Auto shrink" я знаю.

Вопрос был в том что  

1. Нужно ли для этого еще что то (настраивать планы обслуживания или т.п.)
2. Как часто происходит это сжатие (раз в день, раз в неделю и т.п.)

В книгах пишут что это происходит автоматически но не дают ответа на эти два вопроса.

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

(0) этот вопрос задают те, у кого еще впереди задача "надо бы восстановиться на момент до РегистрыСведений.КонтактнаяИнформация.СоздатьНаборЗаписей().Записать(), а то я тут... в общем... так вышло... но зато могу повторить"

(46) Ты что-то напутал: в сабже речь не о ведении сабжа, а о его бекапе

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

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

(45) Спасибо за пояснение

(42) (44) (49) ты включил автошринк у себя на продакшн базах?

Аналогией автошринка в проде - в мире 1С - можно считать настройку перезапуска рабочих процессов в кластере 1С (в свойствах кластера) - типа ставишь там 86400 и каждые сутки у тебя свежие, а не распухшие, рабочие процессы. Только задница в том, что временем такого перезапуска управлять напрямую и легко нельзя - отсчет начинается со времени предыдущего запуска менеджера кластера, и можно словить такой глобальный рестарт посреди рабочего дня или какой-нибудь чувствительной длительной операции.
Так же и с автошринком - никогда не знаешь, когда он начнет делаться, и можешь словить тормоза.

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

Ну и бред вы тут несёте. https://www.sqlskills.com/blogs/paul/auto-shrink-turn-it-off/