Как уменьшить размер базы данных на MS SQL

В некоторых случаях может понадобиться уменьшить размер базы данных на MS SQL или перенести часть данных в отдельную базу (это может потребоваться для создания ресурсоёмких отчётов, чтобы не блокировать основную базу и не выделять под копию базы такое же количество места).

Если говорить о месте на диске, которое занимает база MS SQL, то оно занимается данными, индексами и может быть просто зарезервировано. Помимо базы, для неё на диске хранится журнал транзакций. Его, так же, как и базу, можно сжимать. При установке модели восстановления "Простой" (Simple) журнал не хранится на диске и практически не занимает место.

Для просмотра места, занимаемого в базе таблицами, рекомендуется использовать готовый отчёт в бесплатной среде управления СУБД MS SQL Management Studio "Использование дисковой памяти верхними таблицами" (Top tables by disk Usage).

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

Регулярная чистка таблиц или очистка после резервного копирования

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

Этот способ предполагает то, что можно выделить таблицы и использовать SQL Job, который будет производить операции над базой:

будет через структуру по определённым ключам получать эти таблицы;
исключать таблицы, которые содержат метаданные (более надёжный вариант);
будет чистить остальные таблицы через команду TRUNCATE TABLE.

Также нужно сделать SQL Job, который будет разворачивать бэкап и чистить его от данных. Этот SQL Job можно будет запускать на резервной копии базы данных тогда, когда он нужен, или запланировать его выполнение в планировщике, чтобы периодически сам выполнялся.

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

Внимание!

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

Ниже представлен список некоторых системных таблиц.

Таблица	Описание	Как чистить
Entity.Model.History	История изменения сущностей: кто, когда создал, изменил, удалил объект системы.	Можно чистить с помощью команды TRUNCATE TABLE.
WorkflowTrackingItem	Данные, которые используются для отрисовки карты процесса, маршрута его выполнения: какие задачи были выполнены, какие выполняются, в каких возникли ошибки.
EntityActionHistory	Отображаемая в карточках объектов (документов, задач, процессов) история сущности, в том числе добавленные комментарии, вложения, вопросы.
SchedulerJobRunInfo	Информация о запуске задач планировщика.	Её чистить не рекомендуется, поскольку могут быть запущенные задачи планировщика. Можно почистить по статусу "2" (выполнено или завершились с ошибкой) с помощью команды TRUNCATE TABLE или DELETE.
BlobStoreHolder	Настройки отображения гридов, настройки страниц, содержимое страниц.	Можно почистить выборочно, например, персонализацию отображения гридов. В ключе пользовательских элементов таблицы отображается /u_. Например, /u_Ivanov. Чистить с помощью команды TRUNCATE TABLE или DELETE.
TaskBasePermission	Права доступа к экземплярам задач, документов.	Можно почистить выборочно по экземплярам. Чистить с помощью команды TRUNCATE TABLE или DELETE.
DmsObjectPermission	Права доступа к экземплярам объектов, папкам.

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

Перед тем как чистить таблицы, остановите сервер. Не останавливая сервер, можно почистить таблицы Entity.Model.History, WorkflowTrackingItem, EntityActionHistory с помощью TRANCATE TABLE.

Команда	Описание
TRUNCATE TABLE [dbo].[имя таблицы]	Удаление строк таблицы, не завязано на размере данных, очистка данных будет происходить быстро.
Update [dbo].[имя таблицы] SET [название колонки в БД] = NULL WHERE <условие>	Очистка значений колонок, выставляется значение NULL. Рекомендуется использовать для сброса логинов и паролей; данных для подключения к почтовому серверу, LDAP и т. п.; временных данных в таблицах конфигурации.
ALTER TABLE [dbo].[имя таблицы] REBUILD	Позволяет привести занимаемое таблицей место на диске к размеру хранимых данных (с учётом размера страниц). Используется, когда таблицы: почистили с помощью команд DELETE, Update SET; таблицы часто чистятся и обновляются системой, приводя к сильной фрагментации таблицы.
sp_spaceused 'имя таблицы'	Получить размер каждой конкретной строчки.
DATALENGTH ([название колонки в БД])	Узнать размер строки в колонке.
SUM(DATALENGTH ([название колонки в БД])	Узнать, какой размер занимает вся колонка.
DELETE FROM [dbo].[имя таблицы] WHERE <условие>	Удалить строки, удовлетворяющие условию.
DECLARE @id bigint = <max id to delete> *DECLARE @i bigint = (SELECT COUNT ()/1000+2 FROM [WorkflowTrackingItem] WHERE Id < @id) WHILE (@i > 0) BEGIN DELETE TOP (1000) FROM [WorkflowTrackingItem] WHERE Id < @id SET @i = @i - 1 END;**	Пример запроса, который позволяет чистить данные в рабочее время (в данном случае по условию максимального Id, но можно использовать и другие критерии). При большом количестве очищаемых записей и итераций может привести к переполнению лога транзакций. *Нужно соблюдать баланс между размером пачки и количеством итераций. Больший размер пачки приведёт к уменьшению количества итераций, но длительным блокировкам в базе. Меньший размер пачки приведёт к росту количества итераций. Запрос ощутимо деградирует по производительности при количестве итераций более 5000.

Уменьшение размера базы без резервного копирования

Получить базу меньшего размера можно с помощью репликации. Репликация — это копирование данных, при котором вы можете настроить, что реплицируется по данным, какие данные не нужно копировать. Этот способ более трудоёмкий в настройке, но проще в использовании. Подробнее о репликации в MS SQL вы можете почитать на сайте Microsoft: docs.microsoft.com/ru-ru/sql/relational-databases/replication/sql-server-replication?view=sql-server-ver15.

Основные требования к репликации:

сервер, на котором будет производиться репликация;
файловое хранилище между сервером и продовой базой (для служебных операций: записи снимка базы и транзакций);
дополнительная ВМ по мощности такая же, как и продовая.

Примечание

Недостаток этого способа: при появлении новых таблиц на проде (распространителе репликации) их придётся вручную подключать к репликации (обновлять подписку), потому что публикация хранит список реплицируемых таблиц и фильтры для репликации. Следовательно, этот метод требует регулярного администрирования.