Создание предупреждения для базы данных SQL Server
Если вы используете операционную систему Microsoft Windows Server, используйте графическое средство «Монитор системы» для измерения производительности SQL Server. Можно использовать системный монитор для создания предупреждения, которое будет выводиться, когда счетчик компонента System Monitor достигает порогового значения. В ответ на предупреждение системный монитор запускает приложение, такое как пользовательское приложение, созданное для обработки условий предупреждения. Например можно создать предупреждение, которое будет выводиться, когда число взаимоблокировок превысит заданное значение.
Оповещения также можно определить с помощью SQL Server Management Studio и агента SQL Server. Дополнительные сведения см. в статье Оповещения.
Дополнительные сведения об использовании системного монитора для настройки оповещения базы данных SQL Server см. в статье Настройка оповещения базы данных SQL Server (Windows).
Создание предупреждения по номеру сообщения
В Управляемом экземпляре Azure SQL в настоящее время поддерживается большинство функций агента SQL Server (но не все). Подробные сведения см. в статье Различия между Управляемым экземпляром SQL Azure и SQL Server.
В этой статье описано, как создать предупреждение агента Microsoft SQL Server, возникающее в SQL Server при появлении ошибки с определенным номером, с помощью среды SQL Server Management Studio или Transact-SQL.
Перед началом
Ограничения
- SQL Server Management Studio предоставляет простой графический способ управления всей системой предупреждений и рекомендуется для настройки инфраструктуры предупреждений.
- События, сформированные посредством процедуры xp_logevent , появляются в базе данных master. Поэтому xp_logevent не вызывает срабатывание предупреждения, если только значение аргумента @database_name для предупреждения не равно ‘master’ или NULL.
Безопасность
Разрешения
По умолчанию только члены предопределенной роли сервера sysadmin могут выполнять процедуру sp_add_alert.
Использование среды SQL Server Management Studio
Создание предупреждения по номеру сообщения
- В обозревателе объектов щелкните знак «плюс», чтобы развернуть сервер, на котором необходимо создать предупреждение по номеру ошибки.
- Щелкните знак «плюс», чтобы развернуть Агент SQL Server.
- Щелкните правой кнопкой пункт Предупреждения и выберите Создать предупреждение.
- В поле Имя диалогового окна Создание предупреждения введите имя этого предупреждения.
- Установите флажок Включено , чтобы разрешить выдачу предупреждения. По умолчанию флажок Включить установлен.
- В списке Тип выберите Предупреждение о событии SQL Server.
- В разделе Определение предупреждения о событиив списке Имя базы данных выберите базу данных для установки ограничения на предупреждение относительно конкретной базы банных.
- В разделе Предупреждение будет выдано на основаниивыберите Номер ошибки, а затем введите допустимый номер ошибки для предупреждения. Или нажмите кнопку Серьезность и выберите определенный уровень серьезности, который вызовет предупреждение.
- Чтобы ограничить сообщение определенной последовательностью символов, установите флажок в поле Создавать предупреждение, если сообщение содержит и введите ключевое слово или строку символов в поле Текст сообщения. Максимальное количество символов равно 100.
- Нажмите кнопку OK.
Использование Transact-SQL
Создание предупреждения по номеру сообщения
- В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.
-- adds an alert (Test Alert) that runs the Back up -- the AdventureWorks2022 Database job when fired -- assumes that the message 55001 and the Back up -- the AdventureWorks2022 Database job already exist. USE msdb ; GO EXEC dbo.sp_add_alert @name = N'Test Alert', @message_id = 55001, @severity = 0, @notification_message = N'Error 55001 has occurred. The DB will be backed up. ', @job_name = N'Back up the AdventureWorks2022 Database' ; GO Дополнительные сведения см. в разделе sp_add_alert (Transact-SQL).
Для чего используются предупреждения sql
Использование SQL Server Agent для отправки предупреждений на электронную почту
Для оперативного реагирования администратора на какие-либо события, происходящие на SQL-сервере, необходимо его вовремя предупредить об этих событиях. Для этого удобно использовать предупреждения SQL Server Agent. Далее будут рассмотрены настройки, необходимые для отправки таких предупреждений на электронную почту.
Шаг 1. Настройка SQL Server для отправки электронной почты.
Шаг 2. Назначение логину, от имени которого SQL Server Agent подключается к SQL Server, роли DatabaseMailUserRole в базе данных msdb.
Шаг 3. Назначение созданного на шаге 1 почтового профиля профилем по умолчанию для логина SQL Server Agent.
Подробно шаги 1-3 описаны в статье Настройка SQL Server 2005 для отправки электронной почты.
Шаг 4. Настройка SQL Server Agent. В свойства агента SQL Server на вкладке Система предупреждений необходимо установить флажок Включить почтовый профиль и указать почтовую систему и созданный почтовый профиль:
После этой настройки необходим перезапуск службы SQL Server Agent!
Шаг 5. Создание оператора. Для создания оператора, который будет получать уведомления по электронной почте, необходимо в MSSMS (Microsoft SQL Server Management Studio) раскрыть вкладку Агент SQL Server и из контекстного меню вкладки Операторы выбрать Создать оператора. Затем указать его имя и адрес электронной почты:
- события самого SQL-сервера (также называются ошибками, хотя не всегда являются ими);
- выход значения счетчика системного монитора за указанные пределы;
- возникновение события WMI.
Затем на вкладке Ответ нужно установить флаг Уведомить операторов, выбрать в списке нужного и установить у него флаг Электронная почта:
Для того, чтобы полный текст ошибки был включен в отправляемое сообщение, необходимо на вкладке Параметры установить флаг По электронной почте.
3.7. Предупреждения MS Sql Server
Предупреждения (Alert или я еще люблю говорить Тревога) позволяют контролировать работу вашего сервера. Вы можете создавать тревоги на определенные события сервера (ошибки данных, ошибки запросов, ошибки или удачные выполнения работ и т.д.) и направлять сообщения определенным операторам. Когда мы создавали работы, то для них автоматически создаются тревоги, если указан оператор, который должен получать информацию об удачном или не удачном выполнении работы.
Благодаря тревогам, администратор может сидеть в нескольких тысячах километров от SQL сервера. Если произойдет внештатная ситуация, то администратор получит e-mail сообщение и сможет подключиться к серверу и исправить ошибку. Без тревоги, администратору пришлось бы постоянно контролировать работу сервера и наблюдать за большим количеством параметров.
Планировщики, как и работы, выполняются сервисом SQL Service Agent, поэтому он должен быть запущен, желательно, чтобы он запускался автоматически после загрузки ОС.
3.7.1. Создание сообщений
Для создания собственных сообщений (message) используется процедура SQL сервера sp_addmessage. В общем виде эта процедура выглядит следующим образом:
sp_addmessage [ @msgnum = ] msg_id , [ @severity = ] severity , [ @msgtext = ] 'msg' [ , [ @lang = ] 'language' ] [ , [ @with_log = ] 'with_log' ] [ , [ @replace = ] 'replace' ]
Параметров не так уж и много, поэтому давайте рассмотрим их, прежде чем напишем реальный пример:
- Номер (идентификатор) сообщения, который должен начинаться с 500001;
- Уровень критичности. К нему предъявляются такие же правила, как и у функции RAISERROR;
- Текст сообщения, максимальный размер которого 255 символов;
- Язык сообщения. По умолчанию используется нулевое значение и язык, установленный в системе;
- Нужно ли писать о событии в журнал сообщений Windows. Если в этом параметре указано true, то в журнал будет записано сообщение об ошибке. Если false, сообщение не обязательно будет записано в журнал, тут уже все зависит от того, как оно было сгенерировано;
- Если сообщение с указанным номером существует, то в этом параметре можно указать команду REPLACE. Это означает, что существующую ошибку с указанным номером надо заменить.
Давайте создадим свое сообщение:
EXEC sp_addmessage 60001, 16, 'Ошибка добавления записи'
Чуть позже мы увидим, как воспользоваться сообщениями.
Давайте рассмотрим, как можно удалять сообщения. Для этого используется процедура sp_dropmessage:
sp_dropmessage [ @msgnum = ] message_number [ , [ @lang = ] 'language' ]
Процедуре передается два параметра: номер удаляемого сообщения и язык. Если язык не указан, то будут удалены сообщения для всех национальных языков, существующих в сообщении.
3.7.2. Создание предупреждения
Для создания предупреждения (alert) используется процедура sp_add_alert, которая выглядит следующим образом:
sp_add_alert [ @name = ] 'name' [,[@message_id = ] message_id ] [,[@severity = ] severity ] [,[@enabled = ] enabled ] [,[@delay_between_responses = ] delay_between_responses ] [,[@notification_message = ] 'notification_message' ] [,[@include_event_description_in = ] include_event_description_in ] [,[@database_name = ] 'database' ] [,[@event_description_keyword=]'event_description_keyword_pattern'] [, <[@job_id = ] job_id | [ @job_name = ] 'job_name' >] [,[@raise_snmp_trap = ] raise_snmp_trap ] [,[@performance_condition = ] 'performance_condition' ] [,[@category_name = ] 'category' ]
Рассмотрим доступные параметры этой процедуры:
- @name – имя тревоги, которое должно быть уникальным и по нему система будет в дальнейшем идентифицировать тревогу;
- @message_id – номер ошибки, на которую должно реагировать тревога. В MS SQL Server предопределено достаточно много ошибок, на которые вы можете создать тревоги. Чтобы увидеть их, просмотрите таблицу sysmessages в базу данных master:
SELECT * FROM master..sysmessages
В разделе 3.7.1 мы увидели, как создавать собственные сообщения ошибок.
- @severity – число от 1 до 25, определяющее уровень критичности торевоги. Если вы указали параметр @message_id, то параметр @severity должен быть равен нулю;
- @enabled – если параметр равен 1, то тревога является активной, иначе (если равно нулю) оно не будет генерироваться и операторы не получат уведомление;
- @delay_between_responses – задержка в секундах между событием и действием на это событие. В качестве действия может быть одно или более уведомлений на E-mail или пейджер оператора или выполнение определенной работы. По умолчанию задержки нет (значение 0) и действие произойдет сразу после генерации тревоги;
- @notification_message – в этом параметре вы можете задать дополнительный текст тревоги, которое будет добавлено к сообщению, отправляемому оператору;
- @include_event_description_in – параметр определяет, куда необходимо добавлять сообщение тревоги. В этом параметре можно указывать одно из следующих значений (или сумму):
- 0 – никуда;
- 1 – к e-mail сообщению;
- 2 – к сообщению на пейджер;
- 4 – к сообщению, отправляемому net send.
Например, если вы хотите, чтобы текст добавлялся к e-mail сообщению и к сообщению NET SEND, то необходимо указать число 5 (сумма чисел 1 и 4);
- @database_name – в этом параметре можно задать базу данных. Если этот параметр не задан, то сообщение будет генерироваться для всех баз данных;
- @job_id – позволяет задать идентификатор работы, которая должна выполняться в ответ на тревогу. Если этот параметр указан, то нельзя указывать @job_name;
- @job_name — позволяет задать имя работы, которая должна выполняться в ответ на тревогу. Если этот параметр указан, то нельзя указывать @job_id;
- @raise_snmp_trap – не используется;
- @performance_condition – тревоги могут создаваться для параметров производительности, например, генерация сообщения в случае превышения сервером загрузки в 90%. Если вы создаете такое сообщение, то в этом параметре вы можете указать параметр, условие и значение. В качестве параметра может использоваться объект производительности, счетчик производительности или имя экземпляра счетчика. В качестве условия могут быть знаки больше, меньше или равно. Значение – это числовое значение счетчика;
- @category_name – имя категории тревоги;
Процедура должна выполняться в базе данных msdb, потому что процедура принадлежит этой базе данных.
Давайте добавим собственное предупреждение или как уже много раз называл эту штуку — тревогу:
EXEC sp_add_alert @name = 'Тестовая тревога', @message_id = 60001, @severity = 0, @include_event_description_in = 7, @notification_message = 'Было сгенерировано сообщение 60001'
В данном примере мы создаем тревогу с названием ‘Тестовая тревога’, которая будет реагировать на сообщение с номером 60001. Сообщение с таким номером было создано нами в разделе 3.7.1.
Теперь посмотрим, как создать тревогу для уже существующих сообщений. В таблице sysmessages базы данных master я нашел сообщение с номером 9002, которое генерируется в случае, если файл журнала полный. Давайте рассмотрим, как с помощью тревоги и работы можно решить классическую задачу с увеличения файла базы данных и очисткой журнала.
Как мы уже знаем, файлы базы данных и файлы журналов могут увеличиваться бесконечно, но это далеко не всегда удобно. Многие администраторы хотят контролировать выделяемое сервером дисковое пространство, поэтому отказываются от автоматического увеличения. Для журнала транзакций можно не увеличивать размер файла, а уменьшать его очисткой. Получается, что наша задача отловить создать тревогу, которая будет реагировать на переполнение журнала и запускать работу очистки журнала. Это можно сделать следующим образом:
EXEC sp_add_alert @name = 'Файл журнала полный', @message_id = 9002, @severity = 0, @include_event_description_in = 4, @database_name = 'FlenovSQLBook', @job_name = 'Работа очистки журнала',
Теперь создадим работу, которая будет очищать журнал. Да, мы еще не рассматривали резервное копирование, которое необходимо для решения данной задачи и данная тема будет рассмотрена в 4-й главе. Если вы не знакомы с этими командами, то не обращайте внимания на операторы Transact-SQL. Вернитесь к ним после прочтения 4-й главы. Сейчас наша задача создать работу, а заодно лишний раз потренироваться и закрепить пройденный материал на практике. Итак, работу и два шага выполняющих резервирование и обрезание журнала вы можете увидеть в листинге 3.9.
Листинг 3.9. Создание работы из 2-х шагов резервирования журнала
-- Создание работы резервирования и очистки журнала EXECUTE sp_add_job @job_name = 'Работа очистки журнала', @enabled = 1, @owner_login_name = 'sa', @description = 'Работа, которая резервирует и очищает журнал', @notify_level_eventlog = 2, @notify_level_email = 3, @notify_level_netsend = 3, @delete_level= 0 -- Добавить шаги EXECUTE sp_add_jobstep @job_name = 'Работа очистки журнала', @step_id = 1, @step_name = 'Резервирование журнала', @subsystem = 'TSQL', @command = 'BACKUP LOG FlenovSQLBook to disk = ''C:\Backup\FlenovSQLBook_log.bak''', @server = '', @database_name = 'FlenovSQLBook' EXECUTE sp_add_jobstep @job_name = 'Работа очистки журнала', @step_id = 2, @step_name = 'Обрезание журнала', @command = 'Обрезание журнала', @database_name = 'master', @subsystem = 'TSQL', @command = 'BACKUP LOG FlenovSQLBook WITH TRUNCATE_ONLY'
Сообщение 9002 является критичным и генерируется, когда журнал уже полный и сервер не смог сохранить в нем информацию о транзакции. Если нет автоматического увеличения, то серверу негде будет хранить информацию о транзакции и такая транзакция откатывается, а значит, данные не будут сохранены. Так будет со всеми последующими транзакциями, пока в журнале не появиться свободное пространство, поэтому данный метод увеличения журнала нельзя назвать эффективным, потому что с момента переполнения журнала до момента освобождения пространства в журнале сервер будет доступен только для чтения. Именно поэтому, освобождением должна заниматься работа, которая должна выполняться мгновенно, без задержек и не ждите, пока в работу сервера не вмешается администратор, который в этот момент может спать.
Файл данных просто так обрезать не получиться. Вы же не можете просто так удалить все данные из таблиц и начать их заполнять с нуля. В этом случае, увеличение должно происходить, но все же может быть работа, которая будет делать увеличения с помощью Transact-SQL запроса и выделять пространства там, где прописано в сценарии администратором.
Обновление
Для обновления тревоги используется процедура sp_update_alert. У этой процедуры такие же параметры, как и у процедуры создания тревоги sp_add_alert. Общий вид достаточно большой, потому что содержит достаточно много параметров, и ради экономии места мы не будем рассматривать общий вид.
Для удаления тревоги используется процедура sp_delete_alert, которой нужно передать в качестве параметра только имя удаляемой тревоги, например, так:
EXEC sp_delete_alert 'Файл журнала полный'
Получение информации
Для получения информации о тревогах, используется процедура sp_help_alert, которая выглядит следующим образом:
sp_help_alert [ [ @alert_name = ] 'alert_name' ] [ , [ @order_by = ] 'order_by' ] [ , [ @alert_id = ] alert_id ] [ , [ @category_name = ] 'category' ]
У этой процедуры четыре параметра и все они являются не обязательными. Давайте рассмотрим каждый параметр:
- @alert_name – имя сообщения, информацию о котором, необходимо получить;
- @order_by – отсортировать список по определенному параметру;
- @alert_id – идентификатор сообщения, информацию которого необходимо определить;
- @category_name – имя категории.
Если выполнить процедуру sp_help_alert без параметров, то результатом будут все тревоги SQL сервера:
EXEC sp_help_alert
Результатом будет таблица, состоящая из полей, которые идентичны по имени и назначению параметрам, которые мы рассматривали у процедуры добавления тревоги sp_add_alert.
Отобразим тот же список, но отсортируем результирующий список по параметру message_id:
EXEC sp_help_alert @order_by='message_id'
3.7.3. Создание уведомления
У нас есть тревога и есть сообщение. Для создания уведомления необходимо связать эти два объекта, чтобы в ответ на тревогу генерировалось сообщение и направлялось оператору. Такую связь можно назвать уведомлением и для его создания используется процедура sp_add_notification, которая выглядит следующим образом:
sp_add_notification [ @alert_name = ] 'alert' , [ @operator_name = ] 'operator' , [ @notification_method = ] notification_method
Здесь у нас три параметра:
- @alert_name – имя тревоги;
- @operator_name – имя оператора, который должен получать уведомление;
- @notification_message – метод, которым оператор будет получать уведомления об ошибке:
- 1 – на e-mail адрес;
- 2 – на пейджер;
- 4 – командой NET SEND.
Может быть несколько методов получения уведомления. Для этого в параметре @notification_message нужно указать сумму значений методов. Например, если нужно информировать оператора по e-mail и на пейджер, то в параметре @notification_message указываем значение 3 (1+2).
Примеры использования
Прежде чем создавать уведомление добавим оператора:
exec sp_add_operator @name = 'Администратор', @netsend_address ='192.168.77.11'
Чтобы наглядно увидеть результат работы, я задал IP адрес своего компьютера, чтобы получать NET SEND сообщение.
Следующий пример показывает, как создать уведомление, которое будет получать администратор для тревоги, созданной в разделе 3.7.2:
EXEC sp_add_notification @alert_name='Тестовая тревога', @operator_name='Администратор', @notification_method=4
Одна тревога может направлять сообщения нескольким операторам. Следующий пример добавляет уведомление еще одного оператора для тревоги с именем ‘Тестовая тревога’:
EXEC sp_add_notification @alert_name='Тестовая тревога', @operator_name='Михаил', @notification_method=4
Вот теперь вы можете увидеть результат работы на примере. Для этого необходимо сгенерировать сообщение с помощью функции RAISERROR (более подробно о RAISERROR мы поговорим в разделе 4.3.2):
RAISERROR (60001, 16, 1)
В ответ на это, я получил NET SEND сообщение.
Если вы используете NET SEND сообщения, то убедитесь, что в вашей ОС запущен сервис Messenger, без которого отправка сообщения будет невозможной.
Обновление
Для обновления уведомления используется процедура sp_update_notification, которая выглядит следующим образом:
sp_update_notification [@alert_name =] 'alert', [@operator_name =] 'operator', [@notification_method =] notification
Параметры такие же, как и при создании уведомления sp_add_notification, только параметр @alert_name определяет тревогу, которую надо обновить, а параметр @operator_name определяет оператора. С помощью параметра и @notification_method можно задать новый метод уведомления.
Удаление
Для удаления уведомления используется процедура sp_delete_notification, которая в общем виде выглядит следующим образом:
sp_delete_notification [ @alert_name = ] 'alert' , [ @operator_name = ] 'operator'
Параметр @alert_name определяет тревогу, которую надо удалить, а параметр @operator_name определяет удаляемого оператора.