Обрезать лог файл sql

Обновлено: 01.07.2024

Проблема
Рост файла журнала транзакций. С помощью команды DBCC SHRINKFILE не удается
уменьшить размер файла журнала транзакций до нужного размера .
Решение
Последовательность команд, которую нужно исполнить в Query Analyzer, выглядит
следующим образом:
BACKUP LOG Имя_Базы_Данных WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE(Имя_Файла_Журнала_Транзакций)
go
Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.

(0) Судя по тому, что у вас файл не уменьшается - модель восстановления full и бэкапы журнала транзакций вы не делаете.. Меняйте на simple. Для очистки журнала транзакций используйте команды:
backup log your_database with truncate_only
Может для начала спросить : А как ты шринк делаешь.
А то, к примеру, сверху в QA база Мастер торчит, а он удивляется, что лог не уменьшается.
Сделай сначала бекап LDF, а потом он уже шринканется
Удаление работает.
Отключи базу от сервака, удали журнал транзакций, подключи. Будет создан новый пустой журнал транзакций.
(21) с дуба рухнул?
---
дуб с маленькой буквы - типа дерево такое )))
Ответил на поставленный вопрос.
Ещё раз скажу: журнал транзакций можно удалить, при присоединении будет создан новый.
В чём проблема, если в данном случае человеку текущий ldf человеку не нужен?
(25) А при отсоединении базы все незавершенные транзакции будут автоматически "откачены" назад?
(25) хм, я думал в мдф начальные данные + в лдф все изменения
и пока фулл бэкап не сделаешь данные в мдф не переносятся при модели фулл
---
и продолжаю так думать )))
(27) Думай дальше.
(25) Нет, ни в коем случае. После того, как транзакция зафиксирована, она назад не откатится.
(29) Я немного не то имел в виду. Попробую переформулировать. Данные записываются в mdf файл по чекпойнту, либо лэйзи райтером. При отсоединении БД - данные уже измененные в памяти будут записаны в файл?
(31) Если не дадите пользователю завершить транзакцию (т.е. прервав его сессию), тогда изменения записаны не будут.
(27) и зря Вы так продолжаете думать) В жунале транзакций содержится информация о транзакциях. Активная часть - не зафикисрованные транзакции. Модель восстановления simple не используется для восстановления на определенный момент времени, т.е. данные о зафиксированных транзакциях, условно говоря, удаляются. В модели восстановления full/bulk-logged, информация о зафиксированных транзакциях перестает быть нужной после создания бэкапа журнала транзакций.
(32) Еще раз. Я записал документ и корректно завершил работу с системой. Все изменения были произведены в памяти. Данные будут записаны только по чекпойнту, либо lazy writer'ом. Вы уверены, что при отсоединении БД, будет вызван чекпойнт или запустится lazy writer?
"При перезапуске, служба SQL Server использует журнал транзакций для обнаружения завершенных транзакций, которые внесли изменения в данные, но не были записаны на диск и незавершенных транзакций" (http://www.sqlservercentral.com/articles/64582/ - пруфлинк, четвертый абзац раздела "How does SQL use the log?")
(32) Просто метод аттача без журнала какой-то "варварский" :). Я понимаю его необходимость при переносе базы и повреждении ldf-файла, но для очистки журнала.
(34) Чтобы устранить все сомнения, лучше, конечно же, сначала попробовать на тестовой базе.

(23) >журнал транзакций можно удалить, при присоединении будет создан новый.

Ну не совсем так, определённые шаманские действия все же нужно произвести:

Данный метод работает только для версии SQL2000
sp_configure 'allow updates', 1
reconfigure with override
go

select status from sysdatabases where name = '<db_name>'

и запоминаем/записываем значение на случай неудачи ребилда лога

update sysdatabases set status= 32768 where name = '<db_name>'

SQL Server скажет - Warning: The log for database '<db_name>' has been rebuilt.

9. Если все нормально, то там же выполняем
sp_dboption '<db_name>', 'single user', 'true'
go

9a.
Если Вам не удалось перевести базу в single user mode, то для проверки целостности данных можно попробовать dbo only mode
sp_dboption '<db_name>', 'dbo use only', 'true'

10. Если все в порядке, то
go

(37) Линуксоид? Любишь в гамаке в ластах в противогазе и стоя?

Нормальные люди для этого исползуют sp_attach_single_file_db.

(38) Ты FAQ читал?
У меня как правило ldf терялись не спроста, или диск наворачивался или еще что.
А в этом случае база суспект и никакой sp_attach_single_file_db не поможет.
Вывалит ошибку.

А самому ldf удалять, чтобы лог уменьшить это конечно придумать надо.

(40) Слишком много времени займет.

А можно ли из QA принудительно записать все накопленные изменения (транзакции), а потом грохнуть ldf?

Логи транзакций в MS SQL имеют обыкновение разрастаться, что иногда может привести к окончанию места на диске. Чтобы этого не происходило, в SQL Server существует операция урезания логов (Truncate). Урезание логов производится автоматически, в зависимости от модели восстановления:

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

Подобная ситуация, как правило, происходит с моделью восстановления Full, при использовании которой лог нельзя обрезать до тех пор, пока в резервную копию не попали все транзакции. Это необходимо для того, чтобы обеспечить наличие непрерывную последовательность номеров (LSN) записей в журнале. Соответственно для урезания надо либо сделать полный бэкап базы, либо (что проще и быстрее) временно перевести ее в режим Simple.

Для урезания лога открываем Management Studio, выбираем нужную базу, кликаем на ней правой клавишей мыши и в открывшемся контекстном меню выбираем пункт «Properties». Переходим на вкладку «Options» и изменяем модель восстановления базы (Recovery model) на Simple.

Затем в том же контекстном меню переходим в раздел Tasks -> Shrink -> Files. В поле File type выбираем Log, в поле File name указываем имя файла логов. В поле «Shrink action» выбираем «Reorganize pages before releasing unused space», задаем желаемый размер файла и жмем ОК.

После завершения операции возвращаем режим восстановления базы обратно в Full.

Тоже самое можно проделать из Query Analizer с помощью скрипта:

USE ″Имя базы″
ALTER DATABASE ″Имя базы″ SET RECOVERY SIMPLE
DBCC SHRINKFILE (″Имя файла лога″, ″Желаемый размер″);
ALTER DATABASE ″Имя базы″ SET RECOVERY FULL

Это всего лишь один из способов быстрого уменьшения размера логов. Не самый красивый 🙂 но наиболее простой и эффективный.

В этой статье рассказывается о мониторинге размера журнала транзакций SQL Server, сжатии журнала транзакций, добавлении или увеличении файла журнала транзакций, оптимизации скорости роста журнала транзакций tempdb, а также об управлении размером файла журнала транзакций.

Мониторинг используемого пространства журнала

Для мониторинга используемого пространства журнала используйте sys.dm_db_log_space_usage. Это динамическое административное представление возвращает сведения об используемом сейчас журналом объеме пространства и сообщает, когда журнал транзакций требует усечения.

Для получения сведений о текущем размере файла журнала, его максимальном размере и параметре автоматического увеличения файла вы можете также использовать столбцы size, max_size и growth для данного файла журнала в представлении sys.database_files.

Избегайте переполнения содержащего журналы диска. Хранилище журналов должно отвечать требованиям к числу операций ввода-вывода в секунду и низкой задержке для транзакционной нагрузки.

Уменьшение размера файла журнала

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

Такие факторы, как долго выполняемые транзакции, из-за которых виртуальные файлы журналов длительное время остаются в активном состоянии, могут ограничить или вовсе не допустить возможность сжатия журнала. Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала.

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

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

Сжатие файла журнала (без сжатия файлов базы данных)

Мониторинг событий сжатия файла журнала

Мониторинг пространства журнала

sys.database_files (Transact-SQL) (См. столбцы size, max_size и growth файла или файлов журнала.)

Добавление или увеличение размера файла журнала

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

Чтобы добавить файл журнала в базу данных, используйте предложение ADD LOG FILE инструкции ALTER DATABASE . Это позволяет увеличить размер файла.
Чтобы увеличить размер файла журнала, используйте предложение MODIFY FILE инструкции ALTER DATABASE с указанием синтаксиса SIZE и MAXSIZE . Дополнительные сведения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп.

Дополнительные сведения см. в разделе Рекомендации этой статьи.

Оптимизация размера журнала транзакций tempdb

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

Этого можно избежать с помощью увеличения размера журнала транзакций базы данных tempdb после запуска или перезапуска экземпляра сервера. Дополнительные сведения см. в статье tempdb Database.

Управление увеличением размера файла журнала транзакций

Для управления увеличением файла журнала транзакций используйте инструкцию ALTER DATABASE (Transact-SQL) с параметрами для файлов и файловых групп. Следует отметить следующее.

Чтобы изменить текущий размер файла в КБ, МБ, ГБ и ТБ, используйте параметр SIZE .
Чтобы изменить шаг приращения размера, используйте параметр FILEGROWTH . Значение 0 указывает, что автоматическое приращение выключено и дополнительное пространство для файла не разрешено.
Чтобы установить максимальный размер файла журнала в КБ, МБ, ГБ и ТБ или задать неограниченный размер (UNLIMITED), используйте параметр MAXSIZE .

Дополнительные сведения см. в разделе Рекомендации этой статьи.