Как бороться с uncontrollable log file growth in SQL Server?

[Virginian]

Все делаю по правилам, Recovery mode: Full, а он зараза растет и растет. Как прибить его я знаю, спецсредство имеется, но использовать его на регулярной основе не хочется, слишком тяжелая артиллерия.

Backup.jpg

[Dmitry67]

Репликации нет?
Старой транзакции не застряло? dbcc opentran()

[Колхозник]

(убрал; у ТС все правильно выглядит как есть)
Скореее всего - висит застарелая транзакция в логе и все держит (как Дима сказал).

[Dmitry67]

Кстати да, частота 2 часа весьма странная: overhead на создание бэкапа ничтожен, 5 мин можно поставить легко. Зато потеря данных будет меньше. Конечно, если это важная база.

[Virginian]

Репликации нет?
Старой транзакции не застряло? dbcc opentran()

No active open transactions.
DBCC execution completed.

Конкретно на этом сервере репликации нет, на других есть и та же картина.

[Virginian]

Кстати да, частота 2 часа весьма странная: overhead на создание бэкапа ничтожен, 5 мин можно поставить легко. Зато потеря данных будет меньше. Конечно, если это важная база.

Обычно я каждый час делаю, это база не такая важная, сделал через два.

[iDesperado]

в SQl2005 было туча багов на тему IL Snapshot, которые и на размер лога влияли. IL Snapshot включен, верно ?

[Virginian]

Спасибо за советы, давно уже с этим борюсь и все никак. У нас не так много транзакций, если что, введут потерянные данные вручную еще раз.
По идее лог сам должен чиститься после full backup, но этого не прoисходит.

Периодически приходится сразу после Full Backup делать вот такую убойную операцию:

backup log CarSales to disk = 'nul'


ALTER DATABASE CarSales set recovery simple
GO
CHECKPOINT
GO
DBCC SHRINKFILE (CarSales_log,1)
GO

ALTER DATABASE CarSales set recovery full
GO

[Virginian]

в SQl2005 было туча багов на тему IL Snapshot, которые и на размер лога влияли. IL Snapshot включен, верно ?

Вы имеете в виду Snapshot Isolation?

Нет, не включен.

[Dmitry67]

А что вы называете growth, рост размера LDF или просто размера used space в log? Каков типичный размер LDF и сколько процентов там обычно свободного места?

[Virginian]

А что вы называете growth, рост размера LDF или просто размера used space в log? Каков типичный размер LDF и сколько процентов там обычно свободного места?

Рост размера файла LDF конечно. Свободного места практически нет, в опциях unrestricted 10% growth.

[Dmitry67]

Еще вопросы
1 перевод в simple таки сбрасывает лог?
2 какой характерный размер ldf?
3 какой характерный размер бэкапп лога?

[Колхозник]

..По идее лог сам должен чиститься после full backup, но этого не прoисходит.

Периодически приходится сразу после Full Backup делать вот такую убойную операцию:

По моему у вас там какая-то конфузия вокруг full backup vs. transaction backup и в этом вся проблема.

Еще раз - full backup НЕ чистит лог файлы.

Transaction log backup - чистит лог файлы.

С этим разберитесь для начала.
На картинке вроде все прально нарисовано.... Но доверия чтой-то поубавилось...

[Virginian]

..По идее лог сам должен чиститься после full backup, но этого не прoисходит.

Периодически приходится сразу после Full Backup делать вот такую убойную операцию:

По моему у вас там какая-то конфузия вокруг full backup vs. transaction backup и в этом вся проблема.

Еще раз - full backup НЕ чистит лог файлы.

Transaction log backup - чистит лог файлы.

С этим разберитесь для начала.
На картинке вроде все прально нарисовано.... Но доверия чтой-то поубавилось...

Никакой конфузии. Для отправки в "NIL" требуется предыдущий полный бэкап, чисто технический момент.

А вот Transaction log backup к сожалению ничего не чистит, хотя и выполняется каждый час.

[Virginian]

Еще вопросы
1 перевод в simple таки сбрасывает лог?
2 какой характерный размер ldf?
3 какой характерный размер бэкапп лога?

Дима, сам по себе перевод не сбрасывает, он нужен чтобы shrink работал.
Бэкапп лога в активный период около 15-20 MB, но сразу после full backup он подскакивает до 29GB, то есть примерно до размера всего ldf.
ldf файл после этого стандартными командами не сжимается. По крайне мере раньше не сжимался, попробую проверить в это воскресение, если конечно ураган не достанет.

P.S. Поробовал сейчас на одной, shrink сработал, но сейчас транзакций нет, народ спит. На другой наоборот, не сработал.

[Dmitry67]

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

Судя по размерам бэкапа лога что-то его "держит" и пока мне это непонятно. Позже еще напишу, еще сонный )

[Dmitry67]

У вас есть QA environment, где вы можете развлечься с той же базой?

[Virginian]

У вас есть QA environment, где вы можете развлечься с той же базой?

Да, есть, но там мы не делаем бэкап лога, поскольку транзакций очень мало. Но это конечно нетрудно организовать. Конкретно в этой базе репликация есть, но есть одна и без репликации, можно с ней поиграться

[Dmitry67]

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

[Virginian]

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

Ну, есть одна без репликации, со стандартной структурой бэкапа (full - differential-log). 7GB - data, 7GB - log. В каждом файле примерно по гигабайту свободного пространства. Shrink не работает. Едиственный способ уменьшить лог - перевести в single и сделать бэкап лога либо в nil, либо в конкретный файл.После этого Shrink обрезает лог до минимума. Бьюсь с этой проблемой долго и давно, пока базы были маленькие особо это не доставало, но сейчас они растут как на дрожжах, только успеваю дисковое пространство добавлять.

[Dmitry67]

Секунду. Либо перевести в SIMPLE, либо сделать бэкап лога. Что-то одно, но не одно после другого. Какой бэкап лога если база в SIMPLE?

[StrangerR]

Если база в FULL то обязательно нужно делать бэкапы.

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

Если база с репликацией то еще репликация может удерживать лог даже после бэкапа. Тогда прибить репликацию если она застряла.

[StrangerR]

Тут есть одна обманка. База в FULL не обязательно приводит к росту логов. Пока не делалось ни одного бэкапа, база де факто будет в SIMPLE даже если она прописана как FULL. Но первый же бэкап или восстановление с бэкапа включит накопление логов. И для их чистки нужно их бэкапить, другого не дано.

** Едиственный способ уменьшить лог - перевести в single и сделать бэкап лог

ИЛИ а не И, так как у базы в СИМПЛЕ нельзя сделать бэкап лог.

[Virginian]

Если база в FULL то обязательно нужно делать бэкапы.

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

Если база с репликацией то еще репликация может удерживать лог даже после бэкапа. Тогда прибить репликацию если она застряла.

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

Устал я от этого маразма...

[Virginian]

Тут есть одна обманка. База в FULL не обязательно приводит к росту логов. Пока не делалось ни одного бэкапа, база де факто будет в SIMPLE даже если она прописана как FULL. Но первый же бэкап или восстановление с бэкапа включит накопление логов. И для их чистки нужно их бэкапить, другого не дано.

** Едиственный способ уменьшить лог - перевести в single и сделать бэкап лог

ИЛИ а не И, так как у базы в СИМПЛЕ нельзя сделать бэкап лог.

Это я написал не в той последовательности, как я уже приводил, база изначально находится в full.

backup log CarSales to disk = 'nul'

ALTER DATABASE CarSales set recovery simple

DBCC SHRINKFILE (CarSales_log,1)

ALTER DATABASE CarSales set recovery full

После этого все в шоколаде, но это уже из класса last resort.

Про обманку все правильно, если не было полного бэкапа,

backup log CarSales to disk = 'nul'

сразу выдает ошибку и так прямо и говорит, где full backup, дорогой.