MS SQL скрипт (T-SQL) — shrink .log (Transaction Log) файлов баз данных.

--Variables for cursors
DECLARE @DBName nvarchar(100)

--Cursor for temp..LogFiles filling 
DECLARE Cursor_for_EnumDBs CURSOR 
FOR SELECT name FROM sys.databases
         WHERE name <> 'master' AND
               name <> 'tempdb' AND
	     name <> 'model' AND
	     name <> 'msdb' AND
               name <> 'NoShrinkLogDB'
         ORDER BY name;
OPEN Cursor_for_EnumDBs
FETCH NEXT FROM Cursor_for_EnumDBs INTO @DBName WHILE @@FETCH_STATUS = 0
BEGIN
	--Dynamic query for Transaction Log shrink
	EXEC('
        		USE ' + @DBName + ';
	    	DECLARE @LogName nvarchar(100);
	    	SELECT name INTO #LogFiles FROM sys.database_files WHERE type = 1 AND state = 0;
	    	DECLARE Cursor_for_EnumLogFiles CURSOR FOR SELECT * FROM #LogFiles 
	    	OPEN Cursor_for_EnumLogFiles
	    	FETCH NEXT FROM Cursor_for_EnumLogFiles INTO @LogName WHILE @@FETCH_STATUS = 0
	    	BEGIN
		 	DBCC SHRINKFILE(@LogName);
	        	    	FETCH NEXT FROM Cursor_for_EnumLogFiles INTO @LogName
	    	END
	    	CLOSE Cursor_for_EnumLogFiles
	     	DEALLOCATE Cursor_for_EnumLogFiles
		')
	FETCH NEXT FROM Cursor_for_EnumDBs INTO @DBName	
END
CLOSE Cursor_for_EnumDBs
DEALLOCATE Cursor_for_EnumDBs

clear

Зачем вообще уменьшать журнал транзакций SQL Server

Журнал транзакций (Transaction Log) — критически важная часть MS SQL Server. Он отвечает за восстановление данных, репликацию, Always On и корректную работу транзакций.
Но со временем .ldf-файлы могут разрастаться до десятков и сотен гигабайт, особенно если:

• не настроены регулярные бэкапы логов;
• база работает в режиме FULL или BULK_LOGGED;
• были массовые операции (DELETE, BULK INSERT, INDEX REBUILD);
• ранее возникали ошибки дискового пространства.

Shrink логов — это не лечение, а разовая операция, которую нужно делать осознанно и правильно.

Когда shrink журнала транзакций оправдан

Использовать shrink имеет смысл только в конкретных ситуациях:

• после аварийного роста .ldf;
• при миграции базы на новый сервер;
• перед переносом БД или бэкапом на внешний носитель;
• после разовой массовой очистки данных;
• если точно известно, что текущий размер лога больше никогда не понадобится.

💡 Если лог растёт регулярно — shrink лишь маскирует проблему, а не решает её.

Когда shrink делать НЕ стоит

Shrink логов — частая ошибка администраторов. Не стоит выполнять его:

• на продуктивных системах «по расписанию»;
• при активных транзакциях;
• если не настроены бэкапы журналов;
• без понимания режима восстановления базы;
• на системных БД без крайней необходимости.

⚠️ Регулярный shrink = фрагментация + деградация производительности.

Влияние режима восстановления (Recovery Model)

Поведение журнала напрямую зависит от режима восстановления базы данных:

• FULL — лог очищается только после backup log;
• BULK_LOGGED — частично логгируемые операции;
• SIMPLE — лог очищается автоматически.

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

Почему лог не уменьшается после shrink

Частая ситуация: команда выполнена, а размер .ldf не меняется. Основные причины:

• активные транзакции;
• незавершённые репликации;
• Always On / Mirroring;
• открытые курсоры;
• long-running queries;
• отсутствие backup log (для FULL).

Shrink не может «отрезать» активную часть журнала — это нормально.

Лучшие практики работы с Transaction Log

Чтобы больше не возвращаться к shrink:

• настроить регулярные backup log;
• заранее задавать разумный размер .ldf;
• использовать автогроу не в MB, а в фиксированном шаге;
• контролировать long-running транзакции;
• мониторить использование лога;
• разделять data и log по разным дискам.

Shrink — это экстренная мера, а не часть рутинного обслуживания.

FAQ — частые вопросы

Можно ли делать shrink на рабочей базе?
Можно, но только при минимальной нагрузке и с пониманием последствий.

Опасен ли shrink?
Нет, если сделан правильно. Да — если использовать регулярно.

Нужно ли делать shrink после каждого backup log?
Нет. Это типичная ошибка.

Shrink уменьшает занятое место или максимальный размер?
Shrink уменьшает физический размер файла, но не меняет логику роста.

Советы администратора

• Сделай snapshot или полный backup до shrink
• Никогда не автоматизируй shrink
• Проверяй активные транзакции перед операцией
• Планируй размер логов заранее, а не «лечи последствия»
• Один правильный backup log лучше десяти shrink’ов