Как стать автором
Обновить

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

Время на прочтение 4 мин
Количество просмотров 4.1K

Недавно столкнулся с проблемой "спама" в 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
  1. Очистить историю неотправленных сообщений

-- Если количество записей в таблице 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'
  1. Ищем и устраняем возможную причину в коде БД. Нужно искать в T-SQL коде: тему письма, текст письма, а также вызов процедуры msdb.dbo.sp_send_dbmail

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

select * from msdb..sysmail_allitems
where sent_status <> 'sent'
order by mailitem_id desc
  1. Запустить Database Mail

exec msdb.dbo.sysmail_start_sp
  1. Снова проверяем, что сообщения опять создаются. Если запрос возвращает одну или больше записей, то проблема со спамом писем еще не решена, а значит возвращаемся к пункту 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, которое мне часто помогает в быстром решении различных задач

Подробности про ImportExportDataSql

Поддерживающие типы конвертации:

  1. Бинарные поля из БД в файлы

  2. Запись файлов в БД

  3. Из БД в скрипт SQL

  4. Из Excel в SQL

  5. Из БД в CSV

  6. Из CSV в SQL

  7. Из CSV в БД

  8. Сохранить конфигурацию БД в SQL

  9. Сохранить из БД в БД

  10. RDL отчет (аналог портативного SSRS с поддержкой работы из командной строки)

Статья ImportExportDataSql — бесплатный конвертер данных MSSQL

Скачать Windows приложение ImportExportDataSql:
ImportExportDataSql x64 .NET Framework 3.5
ImportExportDataSql x86 .NET Framework 3.5

ImportExportDataSql x64 .NET Framework 4.5.2
ImportExportDataSql x86 .NET Framework 4.5.2

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

Подробности про FAQ Net

FAQ Net - это десктопное Windows приложение, позволяющее хранить документы Word в единой базе данных с возможностью быстрого поиска информации и иерархической структурой хранения. Приложение портативное (можно запускать с флешки). Встроены функции резервного копирования БД, печати и предварительного просмотра. При этом не обязательно, чтобы Word был установлен на компьютере.

Скачать Windows приложение FAQ Net:
FAQ Net x64 .NET Framework 2.0
FAQ Net x86 .NET Framework 2.0
FAQ Net x64 .NET Framework 4.5.2
FAQ Net x86 .NET Framework 4.5.2

Теги:
Хабы:
Если эта публикация вас вдохновила и вы хотите поддержать автора — не стесняйтесь нажать на кнопку
+4
Комментарии 4
Комментарии Комментарии 4

Публикации

Истории

Работа

.NET разработчик
65 вакансий

Ближайшие события

Московский туристический хакатон
Дата 23 марта – 7 апреля
Место
Москва Онлайн
Геймтон «DatsEdenSpace» от DatsTeam
Дата 5 – 6 апреля
Время 17:00 – 20:00
Место
Онлайн