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

Автор: Sergey Syrovatchenko

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

Первым делом открыл 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 Гб.

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