Давайте рассмотрим упрощённый пример организации уведомлений о тупиковой блокировке и сделаем так, чтобы по электронной почте можно было получить письмо с графом блокировок.
Подготовительные действия
Для начала настройте на
тестовом сервере Database
Mail и убедитесь, что всё работает правильно. С возможными проблемами с
почтой поможет статья: Устранение
неполадок в работе компонента Database Mail
У меня с настройками возникла
только одна проблема, пришлось учесть, что вызов системной хранимой процедуры в
следующем ниже примере будет выполняться не в том контексте, в котором я привык
работать :) В общем, пришлось на время сделать её доступной всем желающим. Вот
один из способов это сделать:
USE MSDB
GO
GRANT EXECUTE ON msdb.dbo.sp_send_dbmail TO public
GO
Для того, чтобы задействовать
очереди событий для интересующих нас баз данных, нужно включить компонент
Service Broker. Как это сделать для базы AdventureWorks показано в следующем
сценарии:
|
USE master GO ALTER DATABASE
[AdventureWorks] SET ENABLE_BROKER WITH NO_WAIT -- с базой должен работать брокер SELECT name, is_broker_enabled FROM sys.databases -- проверка, что брокер включён
GO |
Создание очереди, службы,
сообщения и маршрута
В качестве места базирования наших очередей и сообщений мы выберем системную базу данных tempdb. Она есть на любом сервере и Service Broker для неё включён по умолчанию. Первым делом мы заготавливаем процедуру, которая будет вызываться при регистрации интересующего нас события:
USE tempdb
GO
CREATE PROCEDURE
[p_Notify] AS
DECLARE
@subject nvarchar(128), @body nvarchar(MAX)
DECLARE
@NotificationStore TABLE (message_body varbinary(MAX));
-- извлекаем
сообщения из очереди и помещаем их в табличную переменную
RECEIVE TOP (1) message_body FROM tempdb..DEADLOCK_Queue INTO @NotificationStore
-- собираем
тему и содержимое электронного письма
SELECT TOP (1) @body = CAST(message_body AS nvarchar(MAX)) FROM @NotificationStore
SELECT @subject = CAST(CURRENT_TIMESTAMP AS varchar) + ' Случился DEADLOCK'
IF @body IS NOT NULL EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ПОЧТОВЫЙ ПРОФИЛЬ' -- профиль по умолчанию и не скрытый
,@recipients = 'АДРЕСАТ@ХОСТ.ru' -- адресат
,@subject = @subject
,@body = @body
GO
-- создаём очередь
CREATE QUEUE DEADLOCK_Queue WITH
STATUS = ON
,RETENTION = OFF
,ACTIVATION ( PROCEDURE_NAME = tempdb..p_Notify
, MAX_QUEUE_READERS = 1
, EXECUTE AS 'dbo'
)
GO
-- создаём службу
CREATE SERVICE
DEADLOCK_Notify ON QUEUE DEADLOCK_Queue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
-- определяем локальную маршрутизацию
CREATE ROUTE
DEADLOCK_Route WITH SERVICE_NAME = 'DEADLOCK_Notify'
, ADDRESS = 'LOCAL';
GO
-- создаём
уведомление уровня сервера для событий появления графа тупиковой блокировки
CREATE EVENT NOTIFICATION DEADLOCK_Event ON SERVER FOR DEADLOCK_GRAPH
-- если нужны
все виды сообщений о блокировках, используйте TRC_LOCKS
TO SERVICE 'DEADLOCK_Notify', 'current database'
-- проверка
SELECT * FROM sys.server_event_notifications
WHERE name = 'DEADLOCK_Event';
SELECT * FROM sys.server_events
GO
После того, как
представленный выше сценарий будет успешно применён на тестовом сервере, будет
создано всё необходимое для получения почтовых сообщений. В теле письма будет
правильный XML, у которого в теге <TextData> лежит стандартный граф
взаимоблокировки, который открывается тэгом <deadlock-list>. Если его
вырезать и сохранить в файле с расширением xdl, то при открытии такого файла в
приложении SQL Server Management Studio, вы увидите привычную схему блокировки,
обычно получаемую с помощью SQL Server Profiler.
Удаление созданных для
тестовых целей объектов
Удалить следы наших сценариев
в базе данных tempdb помогут следующие команды:
|
-- DROP PROCEDURE p_Notify -- DROP EVENT NOTIFICATION DEADLOCK_Event
ON SERVER -- DROP ROUTE DEADLOCK_Route -- DROP SERVICE DEADLOCK_Notify -- DROP
QUEUE DEADLOCK_Queue -- ALTER DATABASE [AdventureWorks] SET
DISABLE_BROKER WITH NO_WAIT |
Комментариев нет:
Отправить комментарий