Уменьшение размера базы MSDB в MS SQL2016

Многие используют регламентные задания (Maintenance plan) в MS SQL без очистки после задания. Результатом является увеличение размера одной из системных баз данных — MSDB. Нужно добиться уменьшение размера базы MSDB. Прежде нужно посмотреть из-за чего база выросла. Первым делом делаем резервную копию базы, с которой будем работать. Создаём запрос:

USE msdb
GO

SELECT TOP(10)
      o.[object_id]
    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    WHERE i.is_disabled = 0
        AND i.is_hypothetical = 0
    GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC

Должен получится примерный результат:

Теперь делаем новый запрос:

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];
ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];
truncate table msdb.dbo.sysmaintplan_logdetail;
truncate table msdb.dbo.sysmaintplan_log;
ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])
REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);
ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])
REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

и теперь можно усечь баз таким запросом:

DBCC SHRINKFILE (MSDBData, 1000)

Так же для уменьшения размера базы данных в MS SQL через SQL Server Management Studio можно использовать следующие методы:

Использование SQL Server Management Studio: правой кнопкой мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database) → Нажать на «ОК».

Использование Transact SQL Command: открыть SQL Server Management Studio, подключиться к необходимой базе данных, нажать на «Создать запрос» (New Query), в открывшемся окне прописать соответствующую команду и нажать кнопку «Выполнить» (Execute).

Сжатие на уровне строк: ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW).

Сжатие на уровне страниц: ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=PAGE).

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

Уменьшение размера базы MSDB так же можно включить в отдельное регламентное задание. Мы готовы подобрать под вас сервер и заняться его настройками для максимально эффективной работы.