Управление размером файла журнала транзакций
В этой статье описывается, как отслеживать размер журнала транзакций SQL Server, сжимать журнал транзакций, добавлять или увеличивать файл журнала транзакций, оптимизировать tempdb скорость роста журнала транзакций и управлять ростом файла журнала транзакций.
Эта статья относится к SQL Server. Хотя и очень похоже, сведения об управлении размером файлов журнала транзакций в Управляемый экземпляр SQL Azure см. в статье «Управление файловым пространством для баз данных в Управляемый экземпляр SQL Azure». Сведения о База данных SQL Azure см. в разделе «Управление пространством файлов для баз данных в База данных SQL Azure».
Общие сведения о типах дискового пространства для базы данных
Для управления файловым пространством базы данных важно разобраться со следующими объемами дискового пространства.
| Объем пространства базы данных | Определение | Комментарии |
|---|---|---|
| Место, занятое данными | Объем пространства, используемого для хранения данных базы данных. | Как правило, используемое пространство увеличивается (уменьшается) при операциях вставки (удаления). В некоторых случаях используемое пространство остается неизменным при операциях вставки или удаления в зависимости от объема и шаблона данных, участвующих в операции и фрагментации. Например, удаление одной строки на каждой странице данных не обязательно приведет к уменьшению используемого пространства. |
| Выделенное пространство данных | Объем форматированного файлового пространства, который стал доступным для хранения данных базы данных. | Объем выделенного пространства увеличивается автоматически, но никогда не уменьшается после удалений. Такое поведение гарантирует, что будущие вставки выполняются быстрее, так как пространство не нуждается в переформатировании. |
| Выделенное, но неиспользуемое пространство данных | Разница между объемом выделенного и используемого пространства данных. | Это количество представляет максимальный объем свободного пространства, которое можно освободить путем сжатия файлов данных базы данных. |
| Максимальный размер данных | Максимальный объем пространства, который можно использовать для хранения данных базы данных. | Объем выделенного пространства данных не может превышать максимальный размер данных. |
На следующей схеме показана связь между разными типами дискового пространства для базы данных.
Запрос одной базы данных для сведений о пространстве файлов
Используйте следующий запрос, чтобы вернуть объем выделенного места в файле базы данных и объем неиспользуемого пространства. Единицы результатов запроса указываются в МБ.
-- Connect to a user database SELECT file_id, type_desc, CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb, CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb, CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb, CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb FROM sys.database_files; Мониторинг используемого пространства журнала
Для мониторинга используемого пространства журнала используйте sys.dm_db_log_space_usage. Это динамическое административное представление возвращает сведения об используемом сейчас журналом объеме пространства и сообщает, когда журнал транзакций требует усечения.
Сведения о текущем размере файла журнала, его максимальном размере и параметре автоматического увеличения файла также можно использовать size max_size growth столбцы и столбцы для этого файла журнала в sys.database_files.
Избегайте переполнения содержащего журналы диска. Хранилище журналов должно отвечать требованиям к числу операций ввода-вывода в секунду и низкой задержке для транзакционной нагрузки.
Сжатие файла журнала
Чтобы уменьшить физический размер файла физического журнала, возвращая свободное место в файле операционной системе, сжать файл журнала. Сжатие делает разницу только в том случае, если файл журнала транзакций содержит неиспользуемое пространство.
Если файл журнала заполнен, скорее всего, из-за открытых транзакций, изучите , что предотвращает усечение журнала транзакций.
Операции сжатия не следует рассматривать как обычную операцию обслуживания. Файлы данных и журналов, увеличивающиеся из-за регулярных, повторяющихся бизнес-операций, не нуждаются в операциях сжатия. Команды сжатия могут повлиять на производительность базы данных во время выполнения, поэтому по возможности их следует выполнять в периоды низкого уровня использования. Не рекомендуется сжимать файлы данных, если из-за обычной рабочей нагрузки приложения файлы снова будут увеличиваться до того же выделенного размера.
Обратите внимание на потенциальное негативное влияние на производительность сжатия файлов базы данных, см. раздел «Обслуживание индекса» после сжатия.
Перед сжатием следует учесть факторы, которые могут вызвать задержку усечения журнала. Если после сжатия журнала снова потребуется дисковое пространство, размер журнала транзакций снова будет увеличиваться, что повлияет на производительность во время операций увеличения. Дополнительные сведения см. в Рекомендации.
Вы можете сжать файл журнала, только если база данных активна и хотя бы один виртуальный файл журнала (VLF) свободен. В ряде случаев сжатие невозможно до тех пор, пока не выполнена следующая операция усечения журнала.
Такие факторы, как длительная транзакция, могут хранить VLFs активными в течение длительного периода, могут ограничить сжатие журналов или даже предотвратить сжатие журнала вообще. Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала.
Сжатие файла журнала удаляет виртуальные файлы журнала, которые не содержат частей логического журнала (то есть, неактивные виртуальные файлы журнала). При сокращении файла журнала транзакций неактивные VLFS удаляются из конца файла журнала, чтобы уменьшить размер журнала до приблизительного целевого размера.
Дополнительные сведения об операциях сжатия см. по следующим ссылкам:
Сжатие файла журнала (без сжатия файлов базы данных)
- DBCC SHRINKFILE (Transact-SQL)
- Сжатие файла
Мониторинг событий сжатия файла журнала
Мониторинг пространства журнала
- sys.dm_db_log_space_usage (Transact-SQL)
- sys.database_files (Transact-SQL) (см size max_size . столбцы и growth файлы журнала.
Обслуживание индекса после сокращения
После завершения операции сжатия файлов данных индексы могут стать фрагментированными. Это снижает их эффективность оптимизации производительности для определенных рабочих нагрузок, таких как запросы с использованием масштабного сканирования. Если снижение производительности происходит после завершения операции сжатия, рассмотрите возможность обслуживания индекса для перестроения индексов. Имейте в виду, что перестроение индекса требует свободного места в базе данных и, следовательно, может привести к увеличению выделенного пространства, противодействуя эффекту сжатия.
Добавление или увеличение размера файла журнала
Вы можете выделить дополнительное место на диске, увеличив существующий файл журнала (если для этого достаточно места на диске) либо добавив файл журнала в базу данных, как правило, на другом диске. До тех пор, пока в журнале и на содержащем его дисковом томе достаточно свободного места, будет достаточного одного файла журнала транзакций.
- Чтобы добавить файл журнала в базу данных, используйте предложение ADD LOG FILE инструкции ALTER DATABASE . Это позволяет увеличить размер файла.
- Чтобы увеличить размер файла журнала, используйте предложение MODIFY FILE инструкции ALTER DATABASE с указанием синтаксиса SIZE и MAXSIZE . Дополнительные сведения см. в разделе ALTER DATABASE (Transact-SQL) File и Filegroup options.
Дополнительные сведения см. в Рекомендации.
Оптимизация размера журнала транзакций tempdb
Перезапуск экземпляра сервера изменяет размер журнала транзакций базы данных до исходного tempdb , предварительного автоматического увеличения размера. Это может снизить производительность журнала транзакций tempdb .
Эту нагрузку можно избежать, увеличив размер tempdb журнала транзакций после запуска или перезапуска экземпляра сервера. Дополнительные сведения см. в статье tempdb Database.
Управление увеличением размера файла журнала транзакций
Используйте инструкцию alter DATABASE (Transact-SQL) File и Filegroup options для управления ростом файла журнала транзакций. Обратите внимание на следующее:
- Чтобы изменить текущий размер файла в КБ, МБ, ГБ и ТБ, используйте параметр SIZE .
- Чтобы изменить шаг приращения размера, используйте параметр FILEGROWTH . Значение 0 указывает, что автоматическое приращение выключено и дополнительное пространство для файла не разрешено.
- Чтобы управлять максимальным размером файла журнала в КБ, МБ, ГБ и единицах ТБ или задать для роста значение UNLIMITED, используйте MAXSIZE этот параметр.
Дополнительные сведения см. в Рекомендации.
Рекомендации
Далее приведены некоторые общие рекомендации по работе с файлами журналов транзакций.
- Шаг приращения автоматического увеличения журнала транзакций, задаваемый параметром FILEGROWTH , должен быть достаточно большим, чтобы с запасом соответствовать потребностям транзакций рабочих нагрузок. Во избежание слишком частых увеличений размера файла журнала следует задать достаточно большое значение шагу роста файла журнала. Чтобы подбирать оптимальный размер журнала транзакций, рекомендуем отслеживать объем журнала, занимаемый в следующих случаях.
- Во время, необходимое для выполнения полного резервного копирования, так как резервные копии журнала создаются только после его завершения.
- Во время, необходимое для самых продолжительных операций обслуживания индекса.
- Во время, необходимое для выполнения наибольшего пакета в базе данных.
Версия Значения по умолчанию Начиная с SQL Server 2016 (13.x) Данные — 64 МБ. Файлы журналов — 64 МБ. Начиная с SQL Server 2005 (9.x) Данные — 1 МБ. Файлы журналов — 10 %. До SQL Server 2005 (9.x) Данные — 10 %. Файлы журналов — 10 %. - Большой автоматический увеличение может привести к приостановке базы данных во время выделения нового пространства, что может привести к истечении времени ожидания запроса.
- Приращение большого автоматического увеличения может создавать слишком мало и больших VLFs , а также может повлиять на производительность. Чтобы определить оптимальное распределение VLF для текущего размера журнала транзакций всех баз данных в данном экземпляре, и необходимый рост увеличивается для достижения требуемого размера, см. этот сценарий для анализа и исправления VLFs, предоставляемых командой SQL Tiger.
- Файл будет сжат либо до размера, в котором 25 % пространства не используется, либо до исходного размера, каким бы большим он ни был.
- Сведения об изменении параметра свойства auto_shrink см. в разделе «Просмотр» или «Изменение свойств базы данных» и параметров ALTER DATABASE SET (Transact-SQL).
Следующие шаги
- BACKUP (Transact-SQL)
- Устранение неполадок при переполнении журнала транзакций (ошибка SQL Server 9002)
- Раздел «Резервные копии журналов транзакций» в руководстве по архитектуре журнала транзакций SQL Server и управлению им
- Резервные копии журналов транзакций (SQL Server)
- Параметры инструкции ALTER DATABASE для файлов и файловых групп (Transact-SQL)
Обратная связь
Были ли сведения на этой странице полезными?
Как уменьшить размер log файла sql 1с
Торговля на SQL: база весит 6 Гб, а лог 11,5 Гб. Как этот лог обрезать? Shrink не помогает? Что будет, если просто убить ldf? Будет ругаться или просто создаст новый пустой?
шринк помогает.
Shrink не помогает. пробовал
шринк датабэйс помогает.
Ну говорю же — пробовал. файл остается такого же размера
а ругается хоть?
Так что, поможет ли убийство ldf файла?
(6) нет. процесс проходит нормально, но файл все такой же. ну мож пару мегабайт сбрасывает.
мне помогала чистка журнала регистраций
(9) Т.е. сначала журнал грохнуть, а потом шринк?Предлагаю сначала грохнуть сисадмина.
Ибо настолько тупы вопросы в тырнете плодятся пачаками..
(0) Резервное копирование базы данных средствами SQL сервера делается?
(0) Для дураков: Recovery mode установите в Simple + ежедневный full backup.(13) Ну зачем же сразу для дураков? Пусть будет как для нормальных людей Recovery mode — Full. Если будут делать регулярное резервное копирование базы данных и файла транзакций, то все будет в порядке.
(3) Shrink file с явным указанием размера помогает всегда. А вообще — (13), если париться не хочется.
Уменьшение размера журнала транзакций Microsoft SQL Server
Проблема
Рост файла журнала транзакций. С помощью команды DBCC SHRINKFILE не удается
уменьшить размер файла журнала транзакций до нужного размера .
Решение
Для решения описанной проблемы необходимо предварительно удалить неактивные
записи журнала транзакций с помощью команды BACKUP LOG, а затем уже с помощью
команды 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
use [your_database]
dbcc shrinkfile (your_logfile_logical_name, 0, truncateonly)Логические имя файла можете узнать с помощью
use [your_database_name]
select * from sys.database_files (правда не знаю есть ли в 2000-м такое представление)Может для начала спросить : А как ты шринк делаешь.
А то, к примеру, сверху в QA база Мастер торчит, а он удивляется, что лог не уменьшается.Сделай сначала бекап LDF, а потом он уже шринканется
Удаление работает.
Отключи базу от сервака, удали журнал транзакций, подключи. Будет создан новый пустой журнал транзакций.(21) с дуба рухнул?
—
дуб с маленькой буквы — типа дерево такое )))Ответил на поставленный вопрос.
Ещё раз скажу: журнал транзакций можно удалить, при присоединении будет создан новый.
В чём проблема, если в данном случае человеку текущий ldf человеку не нужен?(23) типа и данные не пропадуть? или плевать? ))
Данные в файле данных. Не пропадут.
(25) А при отсоединении базы все незавершенные транзакции будут автоматически «откачены» назад?(25) хм, я думал в мдф начальные данные + в лдф все изменения
и пока фулл бэкап не сделаешь данные в мдф не переносятся при модели фулл
—
и продолжаю так думать )))(27) Думай дальше.
(25) Нет, ни в коем случае. После того, как транзакция зафиксирована, она назад не откатится.(27) Неправильно думаешь
(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-файла, но для очистки журнала.
Как уменьшить размер log файла sql 1с
Два примера того, как возможно сократить журнал (уменьшить лог) транзакций СУБД SQL (файл *.ldf) для корректной работы 1С 8.
Чаще всего потребность в данной операции возникает из-за переполнения журнала транзакций и откаже корректной работы базы 1С 8. Сопровождается ошибкой: «Журнал транзакций для базы данных переполнен». Причина: «LOG_BACKUP»
Запускается SQL Server Management Studio. В ветке «Базы данных» дерева «Обозревателя объектов» находим базу данных по названию. Вызываем контекстное меню и в нем выбираем пункт «Создать запрос» и вводим текст:
ALTER DATABASE [name_db] SET RECOVERY SIMPLE
DBCC SHRINKFILE ([name_db], 50);
ALTER DATABASE [name_db] SET RECOVERY FULL, где [name_db] — имя (название) базы данных СУБД. В примере — «Бухгалтерия».
Но этот способ не всегда может сработать. Опустим причины и попытки разобраться и заставить работать этот метод. А перейдем к способу, который является интерактивным выражением уже представленного.
Правой кнопкой мыши на базе вызываем меню и переходим в свойства базы. Переходим на страницу «Параметры».
Нажатие на изображении увеличит его 
На странице «Параметры» для «Модели восстановления» выбирается значение «Простая». Запоминаем исходное значение, скорее всего это «Полная». Нужно будет потом вернуть это значение для модели восстановления.
Это была небольшая подготовка. Теперь выполним непосредственно сжатие (шринк) журнала (лога) транзакций. Для этого вызываем контекстное меню правой кнопкой мыши для базы. В подменю «Задачи» выбираем подменю «Сжать», в нем пункт «Файлы».
Нажатие на изображении увеличит его 
В открывшемся окне устанавливаем «Тип файла» — «Журнал». В «Операция сжатия» выбирается вариант «Реорганизовать страницы, перед тем освободить неиспользуемое место». И указывается новое значение размера файла журнала транзакций в поле вводе «Сжать файл до:». Указать приемлемый размер лога. Нажимается кнопка «ОК».
После выполнения операции сокращения вернем «Модель восстановления» в исходное значение «Полная»
Как ограничить рост 1с журнала транзакций 1С+MS-SQL?
Очень часто в форумах слышатся жалобы, что журнал транзакций в базе данных достиг размера, в разы, превышающего размер самой базы данных. Как же его можно ограничить?
Очень просто. Для этого:
- Открываем Enterprise Manager , находим там наш сервер и нашу базу данных, включаем свойства базы данных.
- Переходим на закладку « Options ».
- Выставляем опцию Recovery model в режим « Simple ».
- Ставим отметку в поле « Auto shrink ».
Режим « Simple » — служит для того, чтобы полностью отказаться от идеи хранения лога транзакций. Как только транзакция завершена со статусом «Успешно», лог по ней удаляется, но размер файла при этом не меняется. А вот, собственно, « Auto shrink » делает обрезку файла до размера содержащейся в нем информации.
На особо нагруженных базах не рекомендуется устанавливать опцию « Auto shrink », так как урезка лог файла создает дополнительную нагрузку на сервер. В любом случае, урезать лог файл можно и вручную. Для этого открываем Enterprise Manager , находим там наш сервер и нашу базу данных, по ней щелкаем правой кнопкой мыши и из выпадающего контекстного меню выбираем « Shrink Database. ».
Далее, нам откроется диалоговое выбора режима сжатия.
Красным отмечен очень интересный пункт. Эта опция позволяет не только обрезать лог и сжать файл данных, но и переупорядочить даные на страницах, путем более плотного их размещения. Размер базы данных может значительно уменьшиться для тех баз, где давно не выполнялось это действие.