История про msdb размером в 42 Гб

  • Tutorial
Недавно выдалась минутка посмотреть почему старый тестовый сервер безбожно тормозил… К нему я не имел никакого отношения, но меня одолевал спортивный интерес разобраться, что с ним не так.

Первым делом открыл Resource Monitor и взглянул на общую нагрузку. Процесс sqlserv.exe нагружал ЦП под 100% и формировал большую дисковую очередь, которая была за 300… при том, что значение выше единицы уже считается проблемным.

При анализе дисковой активности заметил непрерывные IO операции в msdb:

D:\SQL_2012\SYSTEM\MSDBData.mdf
D:\SQL_2012\SYSTEM\MSDBLog.ldf

Посмотрел на размер msdb:

SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024
FROM sys.database_files

и включил режим «рука-лицо»:

name         size           space_used
------------ -------------- ---------------
MSDBData     42626.000000   42410.374395
MSDBLog      459.125000     6.859375

Файл данных занимал 42 Гб… Взяв небольшую паузу я начал разбираться в чем причина такого нездорового объема msdb и как побороть проблемы с производительностью сервера.

Проверил ресурсоемкие запросы, которые выполнялись на сервере:

SELECT
      r.session_id
    , db = DB_NAME(r.database_id)
    , r.[status]
    , p.[text]
    --, sql_text = SUBSTRING(p.[text], (r.statement_start_offset / 2) + 1,
    --        CASE WHEN r.statement_end_offset = -1
    --            THEN 2147483647
    --            ELSE ((r.statement_end_offset - r.statement_start_offset) / 2) + 1
    --        END)
    , r.cpu_time
    , r.total_elapsed_time
    , r.reads
    , r.writes
    , r.logical_reads
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) p
WHERE r.[sql_handle] IS NOT NULL
    AND r.session_id != @@SPID
ORDER BY logical_reads DESC

На первом месте гордо расположилась системная хранимая процедура:

db       status   text                                  elapsed_time reads   writes  logical_reads
-------- -------- ------------------------------------- ------------ ------- ------- ---------------
msdb     running  create procedure [sys].[sp_cdc_scan]  6739344      618232  554324  2857923422

Из названия которой можно догадаться, что речь идет о CDC (Change Data Capture), который применяется как средство для отслеживания измененных данных. CDC основан на чтении журнала транзакций и всегда работает в асинхронном режиме за счет использования Service Broker.

Из-за проблем в конфигурации, при попытке отправить Event Notification для Service Broker, сообщение может не достигнуть места назначения и тогда архивируется в отдельной таблице… Сильно занудно сказано… В общем, если часто используется Service Broker, то нужно мониторить sys.sysxmitqueue. Когда в данной таблице идет постоянный прирост данных, то это либо баг, либо мы неправильно используем Service Broker.

Вот этим запросом можно вернуть список объектов и их размер:

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

После выполнения получил следующие результаты:

object_id   obj                               type total_rows   total_size 
----------- --------------------------------  ---- ------------ -----------
68          sys.sysxmitqueue                  S    6543502968   37188.90
942626401   dbo.sysmail_attachments           U    70           2566.00
1262627541  dbo.sysmail_attachments_transfer  U    35           2131.01
1102626971  dbo.sysmail_log                   U    44652        180.35
670625432   dbo.sysmail_mailitems             U    19231        123.39
965578478   dbo.sysjobhistory                 U    21055        69.05
366624349   dbo.backupfile                    U    6529         14.09 
727673640   dbo.sysssispackages               U    9            2.98  
206623779   dbo.backupset                     U    518          1.88  
286624064   dbo.backupfilegroup               U    3011         1.84  

Сразу скажу, что все таблицы в этом списке мы не оставим без внимания. Но сперва нужно очистить sys.sysxmitqueue.

Удалить данные напрямую из sys.sysxmitqueue не получится, поскольку эта таблица является системным объектом (S). После непродолжительных поисков я нашел способ как заставить SQL Server очистить эту таблицу. При создании нового Service Broker автоматически удаляются все ассоциированные со старым брокером сообщения.

USE msdb
GO

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE

Но перед выполнение команды настоятельно рекомендуется отключить SQL Server Agent и перевести SQL Server в Single-User Mode. Удаление существующих сообщений во всех очередях Service Broker заняло у меня минут десять. По завершению выполнения я получил следующее сообщение:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

После перезагрузки службы SQL Server все проблемы с производительностью ушли… душа радовалась и на этом можно было бы поставить точку. Но вспомним, что это была не единственная большая таблица в msdb. Давайте разберемся с остальными…

Для тех, кто любит отправлять почту через Database Mail нужно знать, что SQL Server всю почтовую рассылку логирует и хранит в msdb. Все почтовые вложения, которые отправляются с телом письма там аккуратненько сохраняются… Поэтому очень рекомендуется периодически очищать эту информацию. Делать это можно руками, т.е. смотреть какие таблицы нужно почистить:

SELECT o.name, p.[rows]
FROM msdb.sys.objects o
JOIN msdb.sys.partitions p ON o.[object_id] = p.[object_id]
WHERE o.name LIKE 'sysmail%'
    AND o.[type] = 'U'
    AND p.[rows] > 0

Либо использовать уже готовые хранимые процедуры sysmail_delete_mailitems_sp и sysmail_delete_log_sp:

DECLARE @DateBefore DATETIME 
SET @DateBefore = DATEADD(DAY, -7, GETDATE())

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DateBefore --, @sent_status = 'sent'
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @DateBefore

История выполнения заданий SQL Server Agent также сохраняется в msdb. Когда записей в логе становится много с ним становится не сильно удобно работать, поэтому я стараюсь его периодически чистить sp_purge_jobhistory:

DECLARE @DateBefore DATETIME 
SET @DateBefore = DATEADD(DAY, -7, GETDATE())

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @DateBefore

Еще нужно упомянуть, про информацию о резервных копиях, которая логируются в msdb. Старые записи о созданных бекапах можно удалять sp_delete_backuphistory:

DECLARE @DateBefore DATETIME 
SET @DateBefore = DATEADD(DAY, -120, GETDATE())

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore

Но нужно помнить про один нюанс — при удалении базы данных записи о ее резервных копиях не удаляются из msdb:

USE [master]
GO

IF DB_ID('backup_test') IS NOT NULL BEGIN
    ALTER DATABASE [backup_test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [backup_test]
END
GO

CREATE DATABASE [backup_test]
GO

BACKUP DATABASE [backup_test] TO DISK = N'backup_test.bak'
GO

DROP DATABASE [backup_test]
GO

SELECT *
FROM msdb.dbo.backupset
WHERE database_name = 'backup_test'

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

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'backup_test'

Небольшие выводы...

Системная база msdb используется многими компонентами SQL Server, например, такими как Service Broker, SQL Server Agent и Database Mail. Стоит отметить, что нет готового плана обслуживания, который бы учитывал написанное выше, поэтому важно периодически проводить профилактические меры. В моем случае, после удаления лишней информации и усечения файла размер msdb стал 200 Мб против изначальных 42 Гб.

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

Если хотите поделиться этой статьей с англоязычной аудиторией:
How to reduce MSDB size from 42Gb to 200Mb
Поделиться публикацией

Комментарии 28

    +9
    Как же не хватает таких статей!
      +9
      Я могу написать, как я чистил логи за 5 лет (которые я бережно переносил с каждым бекапом).
        0
        С радостью бы почитал такую информацию. Заранее спасибо.
          +2
          Специально для вас напишу в блог.
      +1
      А я правильно понял, что в БД накопилось 42Гб транзакций которых почему-то никто не удалял, но постоянно сканировал линейным методом? Если это не так, то что тормозило тогда?
        +1
        Возможно ключ к разгадке как раз CDC который не успевал применятся на таблицы с изменениями, отчего и копилась очередь.
          +2
          Вот и бекап пригодился…

          В общем ситуация была следующей: CDC выполнялась по расписанию и внутри обращалась к sys.transmission_queue, который в свою очередь смотрел на sys.sysxmitqueu. На плане можете увидеть, что делался Index Seek огромное число раз и именно по этому убивался диск и ЦП при выполнении:



          План я взял из dbForge исключительно из-за наглядности, поскольку в SSMS надписи на «стрелками» нет.
            0
            а статистика верная на sys.sysxmitqueu?
              0
              Нет, если говорить про момент, когда я анализировал проблему. Первоначально были планы ее обновить. Но потом принял решение почистить таблицу описанные выше способом.
                0
                И забыл добавить… почему не обновил статистику. Поскольку объект системный его напрямую нельзя трогать и такая вот команда выполняться не будет:

                UPDATE STATISTICS sys.sysxmitqueu WITH FULLSCAN
                

                Msg 2706, Level 16, State 6, Line 1
                Table 'sysxmitqueu' does not exist.
                

          0
          Простите за оффтопик, но очень уж два вопроса мучают последние эн лет. Может, они еще кого-то тоже мучают.

          Почему в mssql вместо ansi sql кавычек используются квадратные скобки, а вместо точки с запятой — слово GO? Есть этому какое-то объяснение?
            +3
            Можно и кавычки. Но это на один Shift больше.
            А точка с запятой необходима, если будете пользоваться with()select — там перед with она необходима. Соответственно, если б была разделителем, никто б не смог создать процедуру с этой конструкцией. Из-за чего невозможно пользоваться плагином к Eclipse. Ну и вообще интересно, где это такое в стандарте. Насколько помню, там точка с запятой ставится после команды и mssql именно так и ведёт себя. А вот пакеты разделяются GO и это не стандарт языка, а формат для Management Studio.
            А вообще всё это по историческим причинам.
              +1
              Так при использовании CTE точка с запятой и отделяет with от предыдущей команды, чтобы было отличие от табличных хинтов.
              +1
              Квадратные скобки удобнее в языках программирования, например в C#: "SELECT [Name]" вместо @"SELECT ""Name""".
                +1
                точка с запятой так же может разделять стейтменты внутри блока. GO совершенно не замена её.
                +1
                Да, у нас msdb тоже сильно растет из-за резервного копирования 300-500 маленьких баз.

                Но на этот случай вы как раз можете создать визардом план обслуживания типа «Очистка журнала», включающий очистку
                msdb.dbo.sp_delete_backuphistory
                msdb.dbo.sp_purge_jobhistory
                msdb..sp_maintplan_delete_log
                  0
                  Кто то может подсказать как зачистить
                  dbo.sysmaintplan_logdetail, у меня 33 гигабайт в этой таблице, встроеные зачистки и сжатия результата не дают…
                    0
                    Такой вариант не помог?

                    USE msdb
                    GO
                    
                    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
                    GO
                    
                    TRUNCATE TABLE msdb.dbo.sysmaintplan_logdetail
                    TRUNCATE TABLE msdb.dbo.sysmaintplan_log
                    GO
                    
                    ALTER TABLE dbo.sysmaintplan_log WITH CHECK
                        ADD CONSTRAINT FK_sysmaintplan_log_subplan_id
                        FOREIGN KEY(subplan_id)
                        REFERENCES dbo.sysmaintplan_subplans (subplan_id)
                    GO
                    
                    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
                    GO
                      0
                      Еще не пробовал. Сейчас попробую, насколько опасно на ходу делать (базы юзают вов всю)?
                        0
                        А что опасного? Если нет никаких блокировок на изменению схемы, то все нормально сможеть выполниться. Решите для себя нужны ли Вам эти данные. В них хранится история запуска планов обслуживания, насколько я помню. Что такое сжатие в Вашем понимании? Есть на уровне ROW, PAGE и отдельный фетиш — ColumnStore (начиная с 2014го).
                          0
                          Я спрашиваю, нет вообще опыта и понимания что может MS удумать…
                          Сейчас запустим. Делаю пока резерв.
                        0
                        Что то не так.
                        image
                          0
                          Я лишь вижу, что команда отработала успешно. К тому же сервер бы не мешало обновить. Уже есть SP4, а вы все на RTM сидите.
                            0
                            Обновить да… ;( Но меня смутило «строк 0» и время 00:00:00
                              0
                              Можно попробовать в следующий раз написать более медленный вариант скрипта по удалению :) но суть не поменяется. А вообще-то советую свежие обновления на машину поставить и посмотреть что и как можно сконфигурировать, чтобы все быстрее шевелилось. Например, за счет Instant File Initialization.
                                0
                                Таблицу удалило. С топ 10 она пропала. Но на размер базы в общем не повлияло. Как было 30 с гаком так и осталось.
                                  0
                                  Так сделайте усечение файла данных. SQL Server за Вас это не будет делать (если конечно одна плохая настройка не включена по дефолту). Почитайте как сделать Shrink Flies.
                                    0
                                    Готово. С 38 гиг на 40 мегабайт.

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

                    Самое читаемое