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

Очень часто это связано с тем, что очень быстро увеличивается LOG-файл базы данных MS SQL.
Для того, чтобы понять причины быстрого роста необходимо понять вообще зачем нужен этот LOG-файл. Давайте рассмотрим структуру файлов, попытаемся сделать это упрощено.
Любая база данных MS SQL содержит файлы с двумя расширениями *.mdf и *.log.
MDF-файл - это файл с данными. Содержит сведения, необходимые для запуска базы данных, и ссылки на другие файлы в базе данных. Их может быть несколько.
LOG-файл - это файл журнала транзакций. Файлы журнала транзакций содержат сведения, используемые для восстановления базы данных. Для файлов журнала транзакций рекомендуется расширение LDF. LOG-файлов может быть несколько.
Справедливости ради, еще выделяют и NDF-файлы, но при работе в 1С они не используются (вторичные файлы данных, являются не обязательными).

Как это работает

Теперь рассмотрим как работает запись в БД MS SQL.
Сервер 1С:Предприятия записывает данные в mdf-файл(ы) и параллельно все транзакции связанные с изменением данных в mdf фиксируются в журнал транзакций или log-файл(ы). Причем если по какой-то причине в базе данных произойдет сбой, log-файл поможет нам восстановить данные практически на любой момент времени.
Важно понимать, что в самих log-файлах нет данных, там фиксируются ТОЛЬКО транзакции (действия). Грубо говоря запросы, которые изменяют данные в MDF. Упрощенно, это "тетрадь" куда записываются все изменения (добавления, изменения, удаления) в таблицах базы данных.
Все, конечно, гораздо сложнее, но тут мы приводим упрощенную схему.
Имея такую "тетрадочку" с логом транзакций можно "листать" изменения и добиться того, что система может "откатиться" на нужное время.

Почему растет LOG-файл (ldf)?

Понятное дело, что если записываются все изменения то лог-файл просто обязан расти. Всякие фоновые задания, которые пишут по одной записи в какой-нибудь регистр в 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-файл.

Сжатие log-файла MS SQL

После этого откроется окно:

Сжатие файла MS SQL

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

Шаг 2. Переключение на простую модель восстановления

Если вы хотите на корню решить вопрос с ростом логов, то вы можете переключить модель восстановления на простую (Simple). На самом первом скриншоте выше, переключите модель на простую и нажмите OK.

Так же возможно выполнения вот такого запроса:

USE [TestUIT] BACKUP LOG [TestUIT]  TO DISK='NULL'
GO
DBCC SHRINKFILE ([TestUIT_log], 1)
GO

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

Создание резервных копий журнала транзакций

Кроме способа описанного выше в MS SQL есть возможность создавать резервные копии журнала транзакций. Это можно сделать из Microsoft SQL Server Managment Studio:

Резервная копия журнала транзакций MS SQL

А следующим шагом:

Резервная копия журнала транзакций MS SQL

Важно! Делая бэкап журнала транзакций мы усекаем его. MS SQL понимает, что копия журнала сделана, а значит можно уменьшить размер log-файла.

Это же самое можно выполнить запросом:

BACKUP LOG TestUIT
TO DISK = 'C:\Backups\Logs\TestUIT.TRN'
WITH STATS
GO
Плюсы второго вариант очевидны, вы всегда можете восстановить данные (надо вам сделать эксперименты самостоятельно с этим), написать скрипт, который может это сделать автоматически. При этом после каждого бэкапа размер журнала транзакций будем сокращен. Если вам не нужно, вы всегда можете использовать первый вариант и простую модель восстановления БД.

Вот такие дела, друзья.
Всем удачи и берегите ваш MS SQL!

 06.04.2020 
 Автор:
 БД, MS SQL,


Быстро растет база MS SQL
Часто наши клиенты задают нам вопросы связанные с быстрым ростом размеров базы данных MS SQL. 1С:Пре... 2020-09-09T12:43:05+03:00
Быстро растет база MS SQL
Быстро растет база MS SQL
https://softonit.ru/articles/ms-sql/mssql-growing-fast/

Возврат к списку