gifts2017

Шринк лога транзакций MS SQL 2008/2012 в экстренном случае или боремся с ошибкой HRESULT=80040E14

Опубликовал Сергей Чигирь (Kserken) в раздел Администрирование - Чистка базы

Пошаговая инструкция по уменьшению лога транзакций (*.ldf) MS SQL 2008/2012.

Когда при подключении к базе MS SQL появляются ошибки:

Ошибка СУБД:
Microsoft OLE DB Provider for SQL Server: Журнал транзакций для базы данных "ReportServer" заполнен. Чтобы обнаружить причину, по которой место в журнале не может быть повторно использовано, обратитесь к столбцу log_reuse_wait_desc таблицы
sys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1

или 

Ошибка СУБД:
Microsoft OLE Provider for SQL Server: The transaction log for database “ReportServer” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column is sys.database
HRESULT=80040E14, SQLSTATE=4 2000, native=9002

это значит, что на диске, где расположен лог транзакций закончилось место и теперь СУБД некуда записывать данные о новых транзакциях. Чаще всего такое происходит, когда не установлено никаких ограничений на размер лога и в MS SQL не создано соответствующих планов обслуживания.

В таком случае нужно уменьшить размер самого файла транзакций (*.ldf), другими словами сделать шринк (сжатие) лога. Для этого можно использовать как запрос, так и сжатие лога вручную. 

Рассмотрим сжатие лога транзакций вручную:

Шаг 1. Установить модель восстановления Простая (Simple). Правой кнопкой на базе - Свойства(Properties) - Параметры(Options) - 4-й сверху пункт Модель восстановления(Recovery model) - Простая(Simple) - OK.

Шринк лога MS SQL 2008/2012

Шринк лога транзакций MS SQL 2008/2012

Шаг 2. Выполнить шринк (сжатие) лога транзакций. Правой кнопкой на базе - Задачи(Tasks) - Сжать(Shrink) - Файлы(Files) - установить Тип файла(File type) - Журнал(Log) - в Операция сжатия(Shrink action) - выбрать Реорганизовать страницы, перед тем осводить неиспользуемое место(Reorganize pages before releseasing unused space) - Сжать файл (Shrink file to) - 
указать приемлемый размер лога.

Шринк лога транзакций MS SQL 2008/2012

 Шринк лога транзакций MS SQL 2008/2012

Шаг 3. Установить модель восстановления Полная(Full). Правой кнопкой на базе - Свойства(Properties) - Параметры(Options) - 4-й сверху пункт Модель восстановления(Recovery model) - Полная(Full) - OK. 

 Шринк лога транзакций MS SQL 2008/2012

P.S.: В данной статье даны рекомендации для решения конкретной проблемы. Настройка самого MS SQL здесь не рассматривается! 

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Владимир Гусев (adhocprog) 15.01.13 20:14
(0) а зачем возвращать в состояние "Полная"?
Можно оставаться на Простой и рассчитывать только на бэкапы.
2. aspirator 23 (aspirator23) 15.01.13 21:37
(1) Полная более гибкая. А для больших баз еще и быстрая: с одной стороны можно обеспечить маленькие интервалы восстановления, с другой быстрый бэкап в рабочее время.
logos; caponid; +2 Ответить 2
3. Василий Берестнев (batan) 15.01.13 22:27
Еще бы автор написал, для чего вообще нужна данная процедура )
4. Сергей Чигирь (Kserken) 15.01.13 22:51
(3) batan, данная процедура нужна в том случае, если логи сильно разрастаются. Было у меня на практике, когда нерадивые сисадмины не следили за логами и они разрастались до размеров нескольких сотен ГБ (240 Гб если быть точным, был и в 70 Гб). Поэтому и приходилось выполнять такие манипуляции.
5. Caponid V (caponid) 16.01.13 01:21
Читайте плз документацию к БД она не просто так писана - если бы мог, поставил бы минус публикации - есть стандартные процедуры с бекапом и обрезкой лога.
а то что написано можно делать только на базе без подключенных пользователей - хотя зачем это нужно?? если можно обойтись стандартной процедурой в одну строчку мссиквела.
кому интересно - тот хотя-бы на sql.ru поищет.
randa; theshadowco; +2 Ответить 2
6. Наталия Мастербатова (zzz_natali) 16.01.13 04:48
(2) aspirator23,
Полный бред! Не видела ни разу ни одной фирмы, где в течение дня начинали восстанавливать бакап, а потом еще пол-дня чесали репу, какие доки/транзашки были сделаны, а какие нет(учитывая, что 30-50тичисленное стадо манагеров постоянно разбредается и никого не собрать по тубзикам-курилкам, чтобы выяснить где был реал-тайм).
Выводы: простой режим восстановления, ночной бакап + в обед, если уж так плющит(видела в одной конторке, где сотрудников принудительно выгоняют на хавчик из офиса и базы) и получасовые снапшоты, если уж совсем фобия.
7. aspirator 23 (aspirator23) 16.01.13 06:56
(5) То что написано делается без "выгоняния" пользователей.
(6) Попробуйте поработать с большими базами данных. Тогда и опыт появился бы и понимание как это работает.
То что вы не видели, не означает, что у всех также.
isn; Puk2; artichoke; +3 Ответить
8. Алексей Фурманов (Aleksey-29) 16.01.13 08:28
делаем то же самое. работает на 100%. Выгонять пользователей не нужно. Место на диске за 3 минуты освобождается.
9. dvv01 (dvv01) 16.01.13 09:15
На одной из картинок в параметрах есть такое свойство как "автоматическое сжатие = FALSE".
А почему сразу его не поставить в TRUE? И забыть про все вышеописанное?
10. Anton Loginchev (logos) 16.01.13 09:21
Вредительская статья. Уходить на простую модель без предварительного полного бэкапа - мягко говоря опрометчиво. Ну в общем то и формулировка задачи странная. Проще 1 раз настроить задания по созданию бэкапов и обрезанию логов (о боже с запросами, да да) и забыть об этом.
msvd; DmitrySinichnikov; alest; Дмитрий74Чел; tehas; DissideNtAGiTatoR; randa; Den_D; +8 3 Ответить
11. Babuin 16.01.13 09:47
Автор забыл добавить что после обратного перехода на полную модель нужно собственно сделать полный бэкап, так как он предыдущими действиями прервал цепочку восстановления, соответственно если есть задания по бэкапу оно не будет выполнятся, пока не пройдет новый фул бэкап.
Метод описанный в статье имеет право на жизнь, но лучше все изначально грамотно спроектировать, что бы проблема с "внезапно" выросшим журналом не было в принципе.
Valet; Den_D; +2 Ответить 2
12. Никита Коротаев (bforce) 16.01.13 10:28
Идея неплоха, но в заголовке статьи не хватает надписи "в экстренном случае" или "когда штатные средства не позволяют"
Действительно, бывают запущенные ситуации, когда с логом уже ничего сделать нельзя (ни бэкап, ни шринк).

Я правильно понимаю, что эта операция поможет в случае, когда на диске уже почти не осталось свободного места?
13. mxm2 mxm2 (mxm2) 16.01.13 10:42
(2) aspirator23, Полная модель восстановления - обеспечивает восстановление "с точностью" "до минуты" (имею ввиду из бакапа), но это приводит к тому что разрастается лог (часто он занимает весь диск, и база "останавливается"), Простая модель - позволит восстановить информацию только на момент создания бакапа, никакого (почти) влияния на скорость работы эти модели не имеют (полная модель теоретически медленнее - т.к. при её использовании делается множество "лишних записей" в лог, в отличие от простой модели). подробнее тут http://www.gilev.ru/1c/mssql/backup.htm. Поэтому предпочтительно использовать именно простую модель, с каждодневным бакапом (в наиболее ненагруженное пользователями и фоновыми заданиями время), и с регулярным автоматическим шринкованием, через планы обслуживания СКЛ (кстати использование планов обслуживания 2008/2012 также позволяет "без написаания запросов", производить гибкие настройки многих вещей, в частности бакапов и шринков, да и много чего еще). вот тут еще инфа есть про бакап http://aquablog.3dn.ru/publ/15-1-0-52
14. aspirator 23 (aspirator23) 16.01.13 11:26
(13) Важное замечание сделал(11).
1.Насчет того что "разрастается лог, который часто занимает весь диск" - это не обсуждаем.
SQL сервер тоже нужно настраивать, а не просто поставить по умолчанию.
2."предпочтительно использовать именно простую модель, с каждодневным бакапом" - я уже писал, что простая модель хороша для небольших баз. А также в случае если требования по восстановлению
никто не заявляет. А вот если заявляет и база большая, то простой моделью не выкрутишься.
Либо не обеспечишь нормальную периодичность восстановления, либо если обеспечишь, то тогда база будет ложиться
в момент выполнения полного бэкапа при простой модели.
15. Leon Nid (ixileon) 16.01.13 11:39
Я обычно делаю скриптом. Быстрее получается.
Также можно настроить выполнение по расписанию.

USE ИмяБазыДанных
ALTER DATABASE ИмяБазыДанных
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (ИмяФайлаЛога,100) -- 100 кол-во мб
GO

ALTER DATABASE ИмяБазыДанных
SET RECOVERY FULL;
GO
...Показать Скрыть
plvn; isn; d.zhukov; +3 Ответить 1
16. mxm2 mxm2 (mxm2) 16.01.13 11:52
(14) aspirator23,
- я уже писал, что простая модель хороша для небольших баз. А также в случае если требования по восстановлению
никто не заявляет.

Простая модель хороша для любых баз (и больших и не больших), если нет требований по восстановлению на любой момент времени.
17. Сергей Чигирь (Kserken) 16.01.13 14:07
(9) dvv01, можно даже настроить, чтобы с логом вообще проблем не было, но в данном примере рассмотрено как это можно сделать просто и быстро
18. Сергей Чигирь (Kserken) 16.01.13 14:18
(12) bforce, дельное замечание по-поводу названия. Да, когда места нет, а его нужно срочно освободить.
19. Сергей Чигирь (Kserken) 16.01.13 14:22
(11) Babuin, по-поводу полного бэкапа добавлю. А вот насчет настройки - это отдельная тема. Я лишь показал, как по-быстрому место освободить. У самого базы висят и все настроено. Так что настройку тут не рассматриваю.
20. Сергей Чигирь (Kserken) 16.01.13 14:26
(15) ixileon, можно скриптом. А можно как и у меня. Тут кому как удобнее.
21. Михаил Максимов (МихаилМ) 16.01.13 14:48
без предварительного выяснения, почему увеличился размер transaction log,
нет смысла его усекать. втом числе и автоусекать.
22. Александр (Иной) 17.01.13 00:26
У меня база 90 Гб. В режиме Siple, ибо то что делают юзеры и программеры с базой часто в модели Full увеличивало базу раз в 5. Даже в Siple модели лог увеличивается, если есть большое количество изменений (15-30 Гб бывает).

Для откатов, делаете дифференцированные бекапы базы в продолжении для (периодичность скажем час) и всё что нужно есть.

Шринк да базе 90 Гб при полной модели при робочих юзерах (лог файл 50-150 Гб) нивжизнь не пройдёт, или будет делаться очень долго. Так как при полной модели в основной базе ещё нет изменений, которые хранятся в лог-файле, а если юзера работают с данными, изменения по которым должны быть записаны... Ну и немаловажный факт - количество юзеров. У меня их за 100 одновременно работающих. Транзакции никто таки не отменял...

Как одноразовая мера, чтобы если база выжрала всё место на диске и отказывается работать, вполне оно. Все равно никто работать уже не будет. Тогда да, это самый эффективный вариант.
23. Александр Крынецкий (echo77) 06.06.13 16:57
24. Виктор Фурсов (Vitek84) 24.06.13 12:00
как раз такая ситуация пришла. База была брошена, франчи установили, уехали и никому до неё не было дела - никакого бэкапа - так иногда только DT-шники выгружали и то когда вспомнят. НО после проведения реиндексации реструктуризации лог вырос до 300 ГБ при базе в 45 ГБ. и почти кончилось место на диске.

причем реально база 19,5 ГБ в локальной файловой копии.(45 ГБ стала после загрузки в существующую DT-архива - может подскажете как правильно вернуть назад???).

Сделал все как в статье (не стал БЭКАП делать ибо некуда. ограничился выгрузкой в ДТ-архив.) при неработающих пользователях. размер указал 30 ГБ. Шринк прошел очень быстро. Щас настраиваю планы обслуживания (пока на тестовой базе) и ставлю вопрос о приобритении винта специально под бэкапы.

Но у меня такой вопрос
Вроде установил модель обратно в FULL, но даже сделав реиндексацию таблиц после этого размер журнала не изменился. и мало того уже рабочий день заканчивается - куча проведеных документов была, но размер какой был такой остался и дата изменения не изменяется (последние изменения базы - 0:22 - ночью в базе никто не работает кроме меня:) а лога аж 22 числа, т.е. 2 дня назад) - у меня установлено автоувеличение лога на 200 Мб, а базы на 500 МБ, может быть дата изменения меняется только во время увеличения размера.
Хотя после реиндексации размер лога должен был увеличиться на 25 ГБ (так было до шринка) - может кто нибудь разъяснить почему лог на месте стоит? - планы бэкапов еще не подключал т.е никакого архивирования нет.
25. Сергей Чигирь (Kserken) 20.08.13 00:01
(23) echo77, не захотел создавать тестовую базу вот на ней и показал. Действительно, нужно будет изменить скрины.
26. ZLENKO.PRO (ZLENKO) 16.10.13 11:13
Есть не самый простой и быстрый, но очень надежный рецепт усечения "пустого" (т.е. данных там нет но файл не уменьшается) файла ldf. Применяю когда ничто другое не помогает. Правда если изначально (при создании базы) файл был создан определенного размера, то этот способ тоже не поможет.

Рецепт простой:
//начинаем транзакцию
BEGIN TRAN
GO
//делаем апдейт какого нибудь поля какой нибудь большой таблицы
UPDATE ....
GO
//отменяем транзакцию
ROLLBACK
GO

После этого файл можно усечь на размер заполненных данных в файле ldf.
Рецепт используем столько раз, сколько потребуется.
27. Андрей Старченко (dr.death) 17.10.13 05:55
Зачем используете ПОЛНУЮ модель, если шринкуете транзакционный лог? НУ да ладно это другая тема, а по этой можно просто выполнить запрос (Для примера имя БД "Base"):
BACKUP LOG Base TO DISK = '<D:\Backup\Base_Log.trn'
DBCC SHRINKFILE (Base_Log, 20) WITH NO_INFOMSGS

Затем файл с логом можно удалить, либо перенести на другой диск/сервер
28. Pogonii (pogonii) 02.12.13 15:45
Спасибо автору реально помогло. Такой вопрос а где можно почитать по поводу настройки SQL сервера поделитесь ссылками )))
29. DEN DEN (FKLDOZ) 04.12.13 09:55
Вчера столкнулись с проблемой большого лога. Спасибо автору, статья полезная. Мне не понятно одно, 1С Сервер может вопрос логов как-то регулировать?
30. Алекс Ю (AlexO) 17.06.14 16:58
(29) FKLDOZ,
1С Сервер может вопрос логов как-то регулировать?

1С вообще практически ничего не может на SQL. SQL-сервер - он сам по себе, и сам управляет своими логами.
31. Алекс Ю (AlexO) 14.11.14 12:52
(5) caponid,
есть стандартные процедуры с бекапом и обрезкой лога.

если про "Усечь журнал транзакций" в 2012 - то он не всегда отрабатывает. А лог нужно урезать обязательно.
(5) caponid,
а то что написано можно делать только на базе без подключенных пользователей

С пользователями делается.
(1) adhocprog,
Можно оставаться на Простой

Когда у вас будут вводить по десятку документов в секунду, тогда и оцените восстановление на любой момент времени.
(9) dvv01,
А почему сразу его не поставить в TRUE?

А потому, что принудительно лог очищается ВСЕГДА. А не как придется в случае автошринка при выгрузке.
А есть случаи, когда нужно обрезать только лог, без бэкапа...
32. Алекс Ю (AlexO) 14.11.14 12:55
(14) aspirator23,
Насчет того что "разрастается лог, который часто занимает весь диск" - это не обсуждаем.

Отчего же не обсуждаем? Вы знакомы с проблемой "база загружена не полностью" и её причиной?
(21) МихаилМ,
без предварительного выяснения, почему увеличился размер transaction log,
нет смысла его усекать.

как бы работа самой базы? Наставляемые обновления? Изменения в конфе? Нет?
Это все не приводит к увеличению лога?
33. tixis1c tixis1c (qwed557) 07.01.15 21:16
Из всего сказанного и из комментариев я что то не совсем понял. Если мы используем полную модель восстановления, сделали полный бэкап, обрезали лог базы, и если вдруг понадобиться восстановиться из бэкапа, то ничего не получиться?
DoctorRoza; +1 Ответить 2
34. Алексей Роза (DoctorRoza) 07.01.15 21:46
36. Алекс Ю (AlexO) 12.01.15 11:47
(33) qwed557,
и если вдруг понадобиться восстановиться из бэкапа, то ничего не получиться?
все получится - восстановится на момент бекапа полностью.
Полная же модель подразумевает - восстановление на любой момент времени, что невозможно, если данные транзакций уничтожены (лог транзакций стерт). Поэтому его в таком случае не трут, а также бэкапят вместе с базой. Но это именно для тех, кто понимает, что ему нужно. Для остальных - SIMPLE режим :)
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа