История про msdb размером в 42 Гб
Недавно выдалась минутка посмотреть почему старый тестовый сервер безбожно тормозил… К нему я не имел никакого отношения, но меня одолевал спортивный интерес разобраться, что с ним не так.
Первым делом открыл 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
На первом месте гордо расположилась системная хранимая процедура:
session_id db status text cpu_time total_elapsed_time reads writes logical_reads
---------- -------- -------- ------------------------------------- ----------- ------------------ ------- --------- ---------------
62 msdb running create procedure [sys].[sp_cdc_scan] 111638 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 Гб.
Надеюсь из этого поста вышла поучительная история о пользе постоянного администрирования… не только пользовательских, но и системных баз данных.
