1c sql server файлы log ldf как уменьшить
Перейти к содержимому

1c sql server файлы log ldf как уменьшить

  • автор:

1c sql server файлы log ldf как уменьшить

Друзья, почти ежедневно сталкиваюсь с тем, что на курсе: Администратор 1С, при опросе студентов, на предмет «Как Вы организовали бэкап в MS SQL?». Очень редко кто пишет: «Да, помимо «полного» я делаю и бэкап журналов транзакций».

К сожалению, редко кто делает бэкап ЖТР (

И тем самым открывает прямой путь к таким проблемам как:

«Распух лог в MS SQL», «Сильно увеличился LDF», «Разрастается log, что делать?», «Журнал занял все свободное место на диске», и многое, многое другое.

В этой статье я не буду рассыпать терминами и сложными понятиями гуру специалиста DBA, нет!

Так как вижу реальную картину, реальную проблематику вопроса, на более чем 5000 тыс студентов (Что проходили у нас курс: Администратор 1С). И реальность она несколько в другом!

Большинство не делает бэкапов журналов транзакций, так как не понимает зависимостей (связей), между их созданием и размерами самого журнала (*ldf).

Собственно цель данной статьи, максимально понятно, на простом языке, объяснить и закрыть раз и навсегда проблему растущего лога в MS SQL!

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

Файл *LDF он же и есть наш журнал транзакций!

Что там хранится?

Каждая база данных SQL Server имеет журнал транзакций, в котором фиксируются все транзакции и производимые ими в базе изменения.

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

Если говорить еще проще, благодаря бэкапу ЖТР есть возможность восстановить базу фактически на любой момент времени (вплоть до нужной секунды)!

При этом следует понимать, что никаких по факту данных из 1С в прямом смысле этого слова в журнале нет!

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

Собственно отсюда и название «Журнал транзакций». Конечно на практике все сложнее, но в упрощенном для понимания виде все именно так.

Почему растет лог файл в MS SQL (*ldf) ?

К слову мы только что ответили на частый вопрос: «Вот у меня лог не разрастался» в базе «А», а в базе «Б» растет очень быстро».

Конечно если с базой «Б» пользователи работают интенсивно или различные фоновые, регламентные задачи (их много), безусловно, он будет расти быстрее, такова физика работы «MS SQL»!

«Простая» и «полная» модель восстановления

Да, «полная» модель восстановления подразумевает, что в журнал будем писать «По максимуму» все возможное. Все что сможет записать MS SQL, он туда запишет. Исключения конечно есть. К примеру, когда свободное место закончилось на диске или есть ограничения на сам лог (если установили). Есть и другие причины, но мы сейчас не об этом.

Нам важно понимать одно: «Полная» модель = «Полный» лог! А значит, есть возможность не терять данные, при необходимости восстановится на любой момент времени (фактически до секунды), а выполнив бэкап еще и «заключительного фрагмента журнала» и вообще ничего не потерять!

Только в «полной» модели мы должны работать! Она не зря «по умолчанию» в MS SQL!

«Активная» и «Неактивная» часть журнала

Сперва дадим ответ на вопрос: «Что происходит в момент создания бэкапа ЖТР ?»

Чтоб разобраться в этом вопросе, нам нужно понимать, что журнал транзакций может быть «условно разделен» на две части: «Активная» и «Неактивная» часть журнала.

Неактивная часть журнала транзакций содержит информацию о завершенных транзакциях и не используется сервером SQL Server в процессе восстановления данных. Вместо увеличения размера файла журнала транзакций SQL Server будет повторно использовать освободившееся пространство.

И вот в момент, когда мы создаем бэкап журналов транзакций, мы тем самым «усекаем» его «неактивную» часть (точнее это делает сам MS SQL), вплоть до начала его активной части!

При этом вначале всегда происходит его бэкап, а только после уже «усечение», как на рисунке выше.

Бэкап журналов нужно делать довольно часто (раз на 30-60 мин), особенно если с базой активно работают пользователи, он может вырасти довольно быстро, и конечно без автоматизации этого процесса не обойтись!

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

В «полной» модели восстановления бэкап журналов транзакций НЕОБХОДИМ! Если Вы не хотите в один прекрасный день обнаружить, что свободное место на диске, где он находится, уже закончилось!

Если ЛОГ уже вырос ?

Конечно, если Вы раньше не делали бэкапов журналов, лог соответственно вырос, то здесь одним бэкапом журналов транзакций не обойтись!

Если Вы хотите больше узнать о технической стороне 1С, тогда регистрируйтесь на первый бесплатный модуль курса: Администратор 1С >>>

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

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

Пункт 1. Модель восстановления

Устанавливаем модель восстановления простая (simple). ПКМ на базе / свойства (properties) / параметры (options) / модель восстановления (Recovery model) / простая (simple) / OK.

Открываем свойства базы данных

Выбираем модель восстановления - простая

Пункт 2. Сжатие лога транзакций

Сжимаем лог транзакций. ПКМ на базе / задачи (tasks) / сжать (shrink) / файлы (files) / установить тип файла (file type) / Журнал (log) / в операция сжатия (shrink action) / Реорганизовать страницы, перед тем осводить неиспользуемое место (reorganize pages before releseasing unused space) / сжать файл (shrink file to) / указать приемлемый размер лога.

Сжать файл лога транзакций.

Сжимаем до 0 Мб

Пункт 3. Меняем модель восстановление на изначальную

ПКМ на базе — свойства (properties) — параметры(options) / модель восстановления (recovery model) / полная (full) / OK

Выбираем модель восстановления - полная

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

В этой статье рассказывается о мониторинге размера журнала транзакций 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 .

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

Рекомендации

Далее приведены некоторые общие рекомендации по работе с файлами журналов транзакций.

Шаг приращения автоматического увеличения журнала транзакций, задаваемый параметром FILEGROWTH , должен быть достаточно большим, чтобы с запасом соответствовать потребностям транзакций рабочих нагрузок. Во избежание слишком частых увеличений размера файла журнала следует задать достаточно большое значение шагу роста файла журнала. Чтобы подбирать оптимальный размер журнала транзакций, рекомендуем отслеживать объем журнала, занимаемый в следующих случаях.

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

При активации autogrow для файлов журналов и данных с помощью параметра FILEGROWTH может быть лучше задать рост журнала через размер (size), а не процент (percentage). Это позволит более эффективно контролировать увеличение, так как процент будет характеризовать постоянно растущую величину.

  • Учитывайте, что журналы транзакций не могут использовать мгновенную инициализацию файлов, поэтому особо продолжительное время их роста имеет критическую важность.
  • Рекомендуется не устанавливать для журналов транзакций значение параметра FILEGROWTH выше 1024 МБ. Значения для параметра FILEGROWTH по умолчанию.

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

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

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

Вы можете настроить автоматическое сжатие файлов журналов. Но делать это не рекомендуется, и параметру базы данных auto_shrink по умолчанию задано значение FALSE. Если параметру auto_shrink задано значение TRUE, автоматическое сжатие уменьшает размер файла, только если в нем не использовано более 25 % объема.

Существует ситуация, когда LDF файл занимает много гигабайт места (файл с постфиксом _log), и его необходимо уменьшить.

Это происходит когда база в SQL находится в режиме Full, т.е. с фиксацией всех произведенных транзакций. Модель Full позволяет восстановить состояние базы SQL на любое время, в то время, как модель Simple не позволяет этого сделать, а только восстановить базу из бэкапа. Смысл модели Full в том, что в журнал транзакций LDF записываются ВСЕ транзакции и там остаются, ну до определенного времени, например, до операции shrink. Таким образом SQL последовательным откатом транзакций назад может восстановить состояние базы на любой момент времени периода записанных в LDF транзакций.

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

Первое что нужно сделать, перевести базу в модель восстановления Simple (при этом настроить механизм создания беэкапов базы, если этого до сих пор не сделано). Эту операцию можно делать «на ходу».
Однако, перевод в simple автоматически не уменьшает размер файла транзакций. Можно, провести операцию shrink (сжатие базы) сразу, но лучше сначала сделать полный бэкап базы средствами SQL (есть там в SQL-е по этому поводу одна маленькая хитрость), а потом сделать shrink как файлу базы MDF, так и файлу журнала транзакций LDF. Размер базы тоже должен уменьшиться, но не на много, а, вот, размер файла транзакций LDF, если было сделано все правильно, должен стать практически нулевым (в случае, когда в этом момент в базе нет активной работы пользователей).
Операции бэкапа средствами SQL, и shrink-а, можно делать не выгоняя пользователей, эти операции могут, разве что, сказаться на производительности. Настоятельная рекомендация сделать резервные копии перед началом этой операции.

Для выполнения операции по очистке логов необходимо запустить восстановление:

Если не охота мучиться с запросами можно сделать через GUI: правой кнопкой на базе -> задачи -> шринк -> файлы -> выбираем лог (там будет видно на сколько процентов можно уменьшить).
Иногда, если лог большой — например 50 Гб, то уменьшать (шринкать) его надо 2 раза — с первого раза уменьшается, но не полностью.

Читайте также:

  • Где можно использовать компьютеры на чипе
  • Как сделать файл step
  • 1c spa салон настройка
  • Файловый радар что это
  • Создание города в 3ds max

Как уменьшить файл логов транзакций SQL базы?

Дана база SQL с файлом логов транзакции.
Каждую ночь автоматически через Microsoft SQL Server Management Studio 2016 делаются полные бэкапы.
Модель восстановления — полная. Автосжатие отключено. Теперь о проблеме.
Размер базы — 4 Гб, логов — 49 Гб, что неприемлимо.
Хочу освободить физическое место на диске.
При выборе сжатия файла журнала он указывает на 17 Мб. (0%)

Отслеживать
32k 3 3 золотых знака 21 21 серебряный знак 40 40 бронзовых знаков
задан 20 фев 2018 в 9:00
user284751 user284751
3 2 2 серебряных знака 5 5 бронзовых знаков

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

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

Если вы не собираетесь делать регулярные бэкапы — модель восстановления нужно сменить на простую.

Отслеживать
ответ дан 20 фев 2018 в 10:18
Pavel Mayorov Pavel Mayorov
58.6k 7 7 золотых знаков 74 74 серебряных знака 147 147 бронзовых знаков

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

Также существует возможность усечения файла лога:

USE AdventureWorks2012; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO 

1c sql server файлы log ldf как уменьшить

Ибо настолько тупы вопросы в тырнете плодятся пачаками..

Уменьшение размера журнала транзакций 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)

1c sql server файлы log ldf как уменьшить

Чаще всего потребность в данной операции возникает из-за переполнения журнала транзакций и откаже корректной работы базы 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С 8 как сократить, очистить, уменьшить, лог, журнал транзакций, субд SQL

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

Правой кнопкой мыши на базе вызываем меню и переходим в свойства базы. Переходим на страницу «Параметры».

Нажатие на изображении увеличит его
1С 8 и MS SQL, шринк лога транзакций, журнала, инструкция по уменьшению

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

1С 8, как усечь журнал транзакций MSSQL Server

Это была небольшая подготовка. Теперь выполним непосредственно сжатие (шринк) журнала (лога) транзакций. Для этого вызываем контекстное меню правой кнопкой мыши для базы. В подменю «Задачи» выбираем подменю «Сжать», в нем пункт «Файлы».

Нажатие на изображении увеличит его
1С 8, SQL, как сократить размер журнала, уменьшить размер лога, транзакций

В открывшемся окне устанавливаем «Тип файла» — «Журнал». В «Операция сжатия» выбирается вариант «Реорганизовать страницы, перед тем освободить неиспользуемое место». И указывается новое значение размера файла журнала транзакций в поле вводе «Сжать файл до:». Указать приемлемый размер лога. Нажимается кнопка «ОК».

1С 8 и СУБД SQL server, руководство, инструкция, как уменьшить журнал, лог, транзакций

После выполнения операции сокращения вернем «Модель восстановления» в исходное значение «Полная»

MS SQL server, выбор модели восстановления для сжатия, сокращения журнала транзакций *.ldf для 1С 8

Быстро растет база MS SQL

Понятное дело, что если записываются все изменения то лог-файл просто обязан расти. Всякие фоновые задания, которые пишут по одной записи в какой-нибудь регистр в 1С делают изменения в данных, а следовательно, растет размер лога. Причем, чем больше изменений, тем больше растет ldf-файл. А такая операция, как обновление информационной базы часто ведет вообще к огромному росту, так как при обновлении информационной базы происходит много изменений в данных и это все фиксируется.
Так же на размер файла транзакций влияет и интенсивность работы пользователей. Если мы открываем один и тот же документ и каждый раз меняем один реквизит и записываем документ, то в mdf-файле ничего изменяться не будет, а вот в файле транзакций, будет 10 записей с транзакциями, каждая из которых что-то меняет.
В MS SQL возможно использование нескольких моделей восстановления данных. Это, собственно, механизм, который и отвечает за журнал транзакций.
Полная модель восстановления (Full) — фиксируются ВСЕ транзакции. При этой модели будет максимальный рост журнала транзакций, но при этом риска данных журналов практически нет.
С неполным протоколированием — похожа на полную модель восстановления, но уменьшает место, занимаемое журналами, за счет неполного протоколирования большинства массовых операций. Возможно восстановление до конца любой резервной копии.
Простая модель (Simple) — данные по журналам практически не фиксируются.

Посмотреть на вашу модель можно открыв Microsoft SQL Server Managment Studio, щелкнув на нашу БД правой кнопкой:

Модель восстановления базы данных MS SQL

Методы борьбы с размерами файла транзакций MS SQL

SHRINK (сжатие) лога транзакций
Шаг 1. Сжатие log-файла

Откроем Microsoft SQL Server Managment Studio и «сожмем» log-файл.

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

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

Эта статья относится к SQL Server. Хотя и очень похоже, сведения об управлении размером файлов журнала транзакций в Управляемый экземпляр SQL Azure см. в статье «Управление файловым пространством для баз данных в Управляемый экземпляр SQL Azure». Сведения о База данных SQL Azure см. в разделе «Управление пространством файлов для баз данных в База данных SQL Azure».

Общие сведения о типах дискового пространства для базы данных

Для управления файловым пространством базы данных важно разобраться со следующими объемами дискового пространства.

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

На следующей схеме показана связь между разными типами дискового пространства для базы данных.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

Запрос одной базы данных для сведений о пространстве файлов

Используйте следующий запрос, чтобы вернуть объем выделенного места в файле базы данных и объем неиспользуемого пространства. Единицы результатов запроса указываются в МБ.

-- 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)

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *