Database Mail. SQL Server. Остановка «спама»

Недавно столкнулся с проблемой «спама» в MSSQL и чтобы не забыть как решать эту проблему, решил написать статью.

Введение

В MSSQL встроена служба «Database Mail», которая позволяет отправлять письма. Служба не содержит почтового сервера, вместо этого она хранит настройки для подключения к почтовому серверу.
Очередь почтовых сообщений и история писем хранится в системной базе msdb
Рассмотрим случай, когда служба «Database Mail» начинает очень часто отправлять одни и те же сообщения (проще говоря «спамить»)

Общий алгоритм действий

1. Обнаружение проблемы. В почте за небольшой промежуток времени пришло множество писем с одинаковым содержимым

2. Отключение почтового сервера

3. Отключение Database Mail (exec msdb.dbo.sysmail_stop_sp)

4. Выяснение причины спама в SQL Server и исправление

5. Включение Database Mail (exec msdb.dbo.sysmail_start_sp)

6. Включение почтового сервера

Чтобы не рассылать спам, отключаем почтовый сервер или любым доступным способом прерываем связь между SQL Server и почтовым сервером
После отключения почтового сервера, служба «Database Mail» продолжает работать и вся очередь писем продолжает накапливаться в базе msdb, со статусом failed (письма не доставлены)

Вяснение причины «спама» в SQL Server и исправление

Для того, чтобы отследить отправляются ли письма при отключенном почтовом сервере в Database Mail, необходимо:

1. Остановить Database Mail

exec msdb.dbo.sysmail_stop_sp

2. Очистить историю неотправленных сообщений

-- Если количество записей в таблице msdb.sysmail_allitems очень большое, -- то используйте параметр @sent_before для ограничения удаляемых записей exec msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'unsent' exec msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'retrying' exec msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'failed'

3. Ищем и устраняем возможную причину в коде БД. Нужно искать в T-SQL коде: тему письма, текст письма, а также вызов процедуры msdb.dbo.sp_send_dbmail

4. Проверить, что сообщений больше нет. Запрос должен возвращать пустой результат:

select * from msdb..sysmail_allitems where sent_status <> 'sent' order by mailitem_id desc

4. Запустить Database Mail

exec msdb.dbo.sysmail_start_sp

5. Снова проверяем, что сообщения опять создаются. Если запрос возвращает одну или больше записей, то проблема со спамом писем еще не решена, а значит возвращаемся к пункту 1 (соответственно, включать почтовый сервер рано, иначе он будет рассылать спам). Если запрос ничего не возвращает, значит проблема решена и можно включать почтовый сервер

select * from msdb..sysmail_allitems where sent_status <> 'sent' order by mailitem_id desc

Дополнительные запросы в помощь

Проверка текущего статуса службы Database Mail:

exec msdb..sysmail_help_queue_sp

Просмотр логов событий службы Database Mail

select * from msdb..sysmail_event_log order by log_id desc

После удаления истории сообщений будет не лишним сжать базу msdb:

DBCC SHRINKFILE(MSDBData, 512) DBCC SHRINKFILE(MSDBLog, 512)

Причина спама

После остановки «Database Mail» в мониторинге репликаций обнаружены сообщения: Mail not queued. Database Mail is stopped. Use sysmail_start_sp_ to Start Database Mail. (Source: MSSQLServer, Error number: 14641)
Так обнаружилось, что спам рассылают репликации.

По репликации выясняем, какая таблица рассылает спам.
Как оказалось, на эту таблицу создан триггер, в котором реализована отправка сообщения об изменении записей (хотя, условия проверки, что поля таблицы действительно изменяются — не было)
Транзакционная репликация для этой таблицы каждую секунду отправляла 10 писем
Ошибки в мониторинге репликаций объясняются тем, что из-за того, что в триггере есть отправка сообщения и Database Mail отключен, записи не могли изменяться в таблице

Как оказалось, таблица реплицировала записи из одной БД в другую внутри одного экземпляра MSSQL сервера, поэтому репликации заменили на синоним (create synonym).
Проблема была решена удалением репликации, после чего записи в таблице, на которой создан триггер, больше не изменялись и письма, соответственно, не отправлялись.

Ссылки на мои бесплатные приложения

ImportExportDataSql — это десктопное приложение для разработчиков SQL Server, которое мне часто помогает в быстром решении различных задач

FAQ Net — записная книжка, программа заметок