Отслеживание событий сервера с помощью уведомлений о событиях

В MS SQL 2005 вместе с компонентом Service Broker появился новый способ наблюдения за событиями на сервере. Это объект уведомление о событиях. Данный объект создается на уровне сервера с привязкой к определенной базе данных. Объект подписывается на набор событий, о которых он получает уведомления в момент когда события происходят.
Особенность уведомлений о событиях состоит в том, что они интегрированы с Service Broker. В результате появляется возможность автоматического запуска своего кода в процедуре активации именно в тот момент, когда возникает событие. Для сохранения информации о событии такой подход гораздо удобнее чем периодическая проверка хранилища логов в файловой системе или где-то еще, как того требуют другие средства логирования (например, профайлер). Это создает сходство с ddl-триггерами, только тут нельзя делать откат. В качестве примера создадим уведомление о событии, которое позволяет автоматически сохранять в таблицах структурированные данные о графе взаимоблокировок. Для начала создадим базу данных с таблицами, в которых будут храниться наши логи.

use master
go

if db_id ( N'LogStore' ) is null
begin
 create database LogStore
  on primary
  (
   name  = LogStoreData,
   filename = N'C:\В\DbData\LogStoreData.mdf',
   size  = 100 Mb,
   filegrowth = 100 Mb,
   maxsize  = unlimited
  )
  log on
  (
   name  = LogStoreLog,
   filename = 'C:\В\TranLog\LogStoreLog.ldf',
   size  = 100 Mb,
   filegrowth = 100 Mb,
   maxsize  = unlimited
  )
end
go

use LogStore
go

-- Событие графа взаимоблокировки несет в себе информацию об участниках взаимоблокировки.
-- Каждый участник имеет набор атрибутов. Для их хранения создадим два справочника.
if object_id ( N'dbo.AttrTypes', N'U' ) is null
begin
 create table dbo.AttrTypes
 (
  iAttrTpId smallint identity ( 1, 1 ) not null,
  vcAttrTpName varchar ( 100 )   not null,
  constraint PK_AttrTypes_iAttrTpId primary key clustered
   ( iAttrTpId asc ) on [primary],
  constraint AK_AttrTypes_vcAttrTpName unique nonclustered
   ( vcAttrTpName asc ) on [primary]
 ) on [primary]
end
go

insert into dbo.AttrTypes ( vcAttrTpName )
 values
 ( 'База данных' ),
 ( 'Логин' ),
 ( 'Хост' ),
 ( 'Уровень изоляции транзакций' ),
 ( 'Приложение' ),
 ( 'Файл базы данных' ),
 ( 'Объект' ),
 ( 'Индекс' ),
 ( 'Тип блокировки' )
go

if object_id ( N'dbo.Attrs', N'U' ) is null
begin
 create table dbo.Attrs
 (
  iAttrId   int identity ( 1, 1 ) not null,
  iAttrTpId  smallint  not null,
  vcAttrName  varchar ( 100 )  not null,
  constraint PK_Attrs_iAttrId primary key clustered
   ( iAttrId asc ) on [primary],
  constraint AK_Attrs_vcAttrName_iAttrTpId unique nonclustered
   ( vcAttrName asc, iAttrTpId asc ) on [primary],
  constraint FK_Attrs_iAttrTpId foreign key ( iAttrTpId )
   references dbo.AttrTypes ( iAttrTpId ) on update cascade on delete no action
 ) on [primary]
end
go

-- Следующая таблица содержит одну строку на каждое событие. Здесь только идентификатор события и время.
if object_id ( N'dbo.Deadlocks', N'U' ) is null
begin
 create table dbo.Deadlocks
 (
  iEventId int identity ( 1, 1 ) not null,
  dtDate  datetime  not null,
  constraint PK_Deadlocks_iEventId primary key clustered
   ( iEventId asc ) on [primary]
 ) on [primary]
end
go

-- Таблица с перечнем участников взаимоблокировки. В ней перечислены атрибуты участников, а также полем с признаком жертвы.
if object_id ( N'dbo.DeadlockChains', N'U' ) is null
begin
 create table dbo.DeadlockChains
 (
  iEventId  int   not null,
  iProcessId  int identity ( 1, 1 ) not null,
  vcProcessId  varchar ( 20 )  not null,
  bVictim   bit   not null,
  vcResource  varchar ( 100 )  not null,
  iLoginId  int   not null,
  iHostId   int   not null,
  iIsolationId  int   not null,
  iAppId   int   not null,
  dtLastBatchStart datetime  not null,
  dtLastBathEnd  datetime  not null,
  iLockId   int   not null,
  vcBatch   varchar ( 8000 ) not null,
  constraint PK_DeadlockChains_iProcessId primary key clustered
   ( iProcessId asc ) on [primary],
  constraint FK_DeadlockChains_iEventId foreign key ( iEventId )
   references dbo.Deadlocks ( iEventId ) on update no action on delete no action,
  constraint FK_DeadlockChains_iLoginId foreign key ( iLoginId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action,
  constraint FK_DeadlockChains_iHostId foreign key ( iHostId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action,
  constraint FK_DeadlockChains_iIsolationId foreign key ( iIsolationId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action,
  constraint FK_DeadlockChains_iAppId foreign key ( iAppId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action,
  constraint FK_DeadlockChains_iLockId foreign key ( iLockId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action
 ) on [primary]
end
go

-- Здесь хранится перечень ресурсов, которые образовали цепочку.
if object_id ( N'dbo.DeadlockRes', N'U' ) is null
begin
 create table dbo.DeadlockRes
 (
  iEventId int   not null,
  iResId  int identity ( 1, 1 ) not null,
  iDbId  int   not null,
  iFileId  int   not null,
  iPageId  int   not null,
  iObjId  int   not null,
  iIndId  int   not null,
  iHobtId  bigint   not null,
  constraint PK_DeadlockRes_iResId primary key clustered
   ( iResId asc ),
  constraint FK_DeadlockRes_iEventId foreign key ( iEventId )
   references dbo.Deadlocks ( iEventId ) on update cascade on delete no action,
  constraint FK_DeadlockRes_iDbId foreign key ( iDbId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action,
  constraint FK_DeadlockRes_iFileId foreign key ( iFileId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action,
  constraint FK_DeadlockRes_iObjId foreign key ( iObjId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action,
  constraint FK_DeadlockRes_iIndId foreign key ( iIndId )
   references dbo.Attrs ( iAttrId ) on update no action on delete no action
 ) on [primary]
end
go

-- У каждого ресурса может быть перечень владельцев.
if object_id ( N'dbo.ResOwners', N'U' ) is null
begin
 create table dbo.ResOwners
 (
  iResId  int not null,
  iProcessId int not null,
  iModeId  int not null,
  constraint PK_ResOwners_iResId_iProcessId primary key clustered
   ( iResId asc, iProcessId asc ) on [primary],
  constraint FK_ResOwners_iResId foreign key ( iResId )
   references dbo.DeadlockRes ( iResId ) on update cascade on delete no action,
  constraint FK_ResOwners_iProcessId foreign key ( iProcessId )
   references dbo.DeadlockChains ( iProcessId ) on update cascade on delete no action
 ) on [primary]
end
go

-- У каждого ресурса может быть перечень процессов, ожидающих получения блокировки на данный ресурс.
if object_id ( N'dbo.ResWaiters', N'U' ) is null
begin
 create table dbo.ResWaiters
 (
  iResId  int not null,
  iProcessId int not null,
  iModeId  int not null,
  constraint PK_ResWaiters_iResId_iProcessId primary key clustered
   ( iResId asc, iProcessId asc ) on [primary],
  constraint FK_ResWaiters_iResId foreign key ( iResId )
   references dbo.DeadlockRes ( iResId ) on update cascade on delete no action,
  constraint FK_ResWaiters_iProcessId foreign key ( iProcessId )
   references dbo.DeadlockChains ( iProcessId ) on update cascade on delete no action
 ) on [primary]
end
go

В результате получилась такая диаграмма:

Теперь создадим объекты брокера в базе данных. Понадобится создать только очередь и службу, так как для типов сообщений и контракта используются специальные системные объекты, имеющиеся в каждой базе данных.

alter database LogStore set enable_broker with rollback immediate
go

if object_id ( N'dbo.LogDeadlockGraph', N'P' ) is null
begin
 exec sp_executesql N'create proc dbo.LogDeadlockGraph as return'
end
go

if object_id ( N'dbo.Logger', N'SQ' ) is null
begin
 create queue dbo.Logger
  with
   status = on,
   retention = off,
   activation
   (
    status = on,
    procedure_name = dbo.LogDeadlockGraph,
    max_queue_readers = 4,
    exec as owner
   )
end
go

if not exists
(
 select *
 from sys.services
 where name = N'LogService'
)
begin
 create service LogService
  on queue dbo.Logger ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )
end
go

if not exists
(
 select *
 from sys.server_event_notifications
 where name = N'ServerLogger'
)
begin
 create event notification ServerLogger
  on server
  for deadlock_graph
  to service 'LogService', 'current database'
end
go

Теперь обновим процедуру активации. Данная процедура будет читать очередь, получая XML-представление о событии. Для парсинга XML используются методы типа данных XML и выражения языка XQuery. Общая схема работы процедуры состоит в следующем.

1. Наполняем табличную переменную с перечнем участников взаимоблокировки.
2. Наполняем справочник значениями атрибутов участников.
3. Наполняем нормализованную таблицу dbo.DeadlockChains
4. Наполняем табличную переменную списков ресурсов с их блокировками от участников. При этом для простоты рассматриваем случай, когда ресурсы это строки или ключи.
5. Наполняем справочник атрибутами ресурсов
6. Наполняем нормализованную таблицу dbo.DeadlockRes. При этом для наполнения используем merge+output для сохранения соответствия узлов ресурсов и идентификаторов ресурсов.
7. Наполняем таблицы с владельцами и заблокированными соединениями.

Для конвертации xml-документа в таблицу используем метод nodes. Ниже приведен полный код хранимой процедуры.

alter proc dbo.LogDeadlockGraph
as
begin
 set nocount, xact_abort on
 
 begin try
  declare @xBody xml, @vcVictProcessId varchar ( 100 ), @dtEventTime datetime, @iEventId int, @vcSql nvarchar ( max ), @vcErr nvarchar ( max )
  begin tran
   ;
   receive top ( 1 ) @xBody = try_convert ( xml, message_body )
   from dbo.Logger
   if @@rowcount = 0
   begin
    return
   end
   
   declare @tblProcesses table
   (
    vcId  varchar ( 100 )  not null,
    vcResource varchar ( 100 )  not null,
    vcLogin  varchar ( 100 )  not null,
    vcHost  varchar ( 100 )  not null,
    vcIsolation  varchar ( 100 )  not null,
    vcApp  varchar ( 100 )  not null,
    dtStart  datetime   not null,
    dtEnd  datetime   not null,
    vcBatch  varchar ( 8000 )  not null,
    vcLock  varchar ( 100 )   not null
   )
   
   insert into @tblProcesses
   (
    vcId,
    vcResource,
    vcLogin,
    vcHost,
    vcIsolation,
    vcApp,
    dtStart,
    dtEnd,
    vcBatch,
    vcLock
   )
   select
    vcId  = left ( isnull ( process.value ( '(@id)[1]', 'nvarchar ( max )' ), '' ), 100 ),
    vcResource = left ( isnull ( process.value ( '(@waitresource)[1]', 'nvarchar ( max )' ), '' ), 100 ),
    vcLogin  = left ( isnull ( process.value ( '(@loginname)[1]', 'nvarchar ( max )' ), '' ), 100 ),
    vcHost  = left ( isnull ( process.value ( '(@hostname)[1]', 'nvarchar ( max )' ), '' ), 100 ),
    vcIsolation = left ( isnull ( process.value ( '(@isolationlevel)[1]', 'nvarchar ( max )' ), '' ), 100 ),
    vcApp  = left ( isnull ( process.value ( '(@clientapp)[1]', 'nvarchar ( max )' ), '' ), 100 ),
    dtStart  = isnull ( process.value ( '(@lastbatchstarted)[1]', 'nvarchar ( max )' ), '1900-01-01' ),
    dtEnd  = isnull ( process.value ( '(@lastbatchcompleted)[1]', 'nvarchar ( max )' ), '1900-01-01' ),
    vcBatch  = left ( isnull ( process.value ( '(inputbuf)[1]', 'nvarchar ( max )' ), '' ), 8000 ),
    vcLock  = left ( isnull ( process.value ( '(@lockMode)[1]', 'nvarchar ( max )' ), '' ), 100 )
   from @xBody.nodes ( '/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process' ) processes ( process )
   
   ;
   with Attrs
   as
   (
    select vcAttr, vcAttrTp
    from
    (
     select distinct vcLogin, N'Логин' from @tblProcesses
     union all
     select distinct vcHost, N'Хост' from @tblProcesses
     union all
     select distinct vcIsolation, N'Уровень изоляции транзакций' from @tblProcesses
     union all
     select distinct vcApp, N'Приложение' from @tblProcesses
     union all
     select distinct vcLock, N'Тип блокировки' from @tblProcesses
    ) attrs ( vcAttr, vcAttrTp )
   )
   insert into dbo.Attrs ( iAttrTpId, vcAttrName )
    select attrtp.iAttrTpId, newattrs.vcAttr
    from
     Attrs newattrs
      inner join
     dbo.AttrTypes attrtp on newattrs.vcAttrTp = attrtp.vcAttrTpName
      left outer join
     dbo.Attrs attrs on
      attrs.iAttrTpId = attrtp.iAttrTpId and
      attrs.vcAttrName = newattrs.vcAttr
    where attrs.iAttrId is null
   set @vcVictProcessId = @xBody.value ( '(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/@victim)[1]',
       'nvarchar ( max )' )
   set @dtEventTime = (
    select dtEnd
    from @tblProcesses
    where vcId = @vcVictProcessId
   )
   
   insert into dbo.Deadlocks ( dtDate )
    values ( @dtEventTime )
   set @iEventId = scope_identity ()
   
   ;
   with AttrData
   as
   (
    select attrs.iAttrId, attrs.vcAttrName, attrtp.vcAttrTpName
    from
     dbo.Attrs attrs
      inner join
     dbo.AttrTypes attrtp on attrs.iAttrTpId = attrtp.iAttrTpId
   )
   insert into dbo.DeadlockChains
   (
    iEventId, vcProcessId, bVictim, vcResource, iLoginId, iHostId, iIsolationId, iAppId,
    dtLastBatchStart, dtLastBathEnd, iLockId, vcBatch
   )
   select @iEventId, vcId, case when vcId = @vcVictProcessId then 1 else 0 end, vcResource,
    attrlog.iAttrId, attrhost.iAttrId, attrlevel.iAttrId, attrapp.iAttrId, processes.dtStart, processes.dtEnd,
    attrlock.iAttrId, processes.vcBatch
   from
    @tblProcesses processes
     inner join
    AttrData attrlog on processes.vcLogin = attrlog.vcAttrName and attrlog.vcAttrTpName = N'Логин'
     inner join
    AttrData attrhost on processes.vcHost = attrhost.vcAttrName and attrhost.vcAttrTpName = N'Хост'
     inner join
    AttrData attrlevel on processes.vcIsolation = attrlevel.vcAttrName and attrlevel.vcAttrTpName = N'Уровень изоляции транзакций'
     inner join
    AttrData attrapp on processes.vcApp = attrapp.vcAttrName and attrapp.vcAttrTpName = N'Приложение'
     inner join
    AttrData attrlock on processes.vcLock = attrlock.vcAttrName and attrlock.vcAttrTpName = N'Тип блокировки'
   
   declare @tblResData table
   (
    iResId  int  not null,
    iDbId  int  not null,
    iPageId  int  not null,
    iFileId  int  not null,
    iHobtId  bigint  not null,
    vcObj  varchar ( 600 ) not null,
    vcIndex  varchar ( 200 ) not null,
    vcMode  varchar ( 100 ) not null,
    vcOwner  varchar ( 100 ) not null,
    vcOwnerMode varchar ( 100 ) not null,
    vcWaiter varchar ( 100 ) not null,
    vcWaiterMode varchar ( 100 ) not null
   )
   
   set @vcSql = N'
   select
    ResData.iResId,
    ResData.iDb,
    ResData.iPage,
    ResData.iFile,
    ResData.iHobtId,
    ResData.vcObj,
    ResData.vcIndex,
    ResData.vcMode,
    vcOwner   = left ( isnull ( own.value ( ''(/owner-list/owner/@id)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
    vcOwnerMode  = left ( isnull ( own.value ( ''(/owner-list/owner/@mode)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
    vcWaiter  = left ( isnull ( wait.value ( ''(/waiter-list/waiter/@id)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
    vcWaiterMode = left ( isnull ( wait.value ( ''(/waiter-list/waiter/@mode)[1]'', ''nvarchar ( max )'' ), '''' ), 100 )
   from
    (
     select
      iResId = row_number () over ( order by ( select 1 ) ),
      iDb  = isnull ( ResData.Res.value ( ''(@dbid)[1]'', ''int'' ), -1 ),
      iPage = isnull ( ResData.Res.value ( ''(@pageid)[1]'', ''int'' ), -1 ),
      iFile = isnull ( ResData.Res.value ( ''(@fileid)[1]'', ''int'' ), -1 ),
      iHobtId = isnull ( ResData.Res.value ( ''(@hobtid)[1]'', ''bigint'' ), -1 ),
      vcObj = left ( isnull ( ResData.Res.value ( ''(@objectname)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
      vcIndex = left ( isnull ( ResData.Res.value ( ''(@indexname)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
      vcMode = left ( isnull ( ResData.Res.value ( ''(@mode)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
      xOwner = ResData.Res.query ( ''owner-list'' ),
      xWaiter = ResData.Res.query ( ''waiter-list'' )
     from @xBody.nodes ( ' +
      iif
      (
       @xBody.exist ( '/EVENT_INSTANCE/TextData/deadlock-list/deadlock/resource-list/ridlock' ) = 1,
       '''/EVENT_INSTANCE/TextData/deadlock-list/deadlock/resource-list/ridlock''',
       '''/EVENT_INSTANCE/TextData/deadlock-list/deadlock/resource-list/keylock'''
      )
     + ' ) ResData ( Res )
    ) ResData
     cross apply
    ResData.xOwner.nodes ( ''.'' ) owners ( own )
     cross apply
    ResData.xWaiter.nodes ( ''.'' ) waiters ( wait )
   '
   insert into @tblResData
   (
    iResId,
    iDbId,
    iPageId,
    iFileId,
    iHobtId,
    vcObj,
    vcIndex,
    vcMode,
    vcOwner,
    vcOwnerMode,
    vcWaiter,
    vcWaiterMode
   ) exec sp_executesql @vcSql, N'@xBody xml', @xBody = @xBody
   
   ;
   with AttrData
   as
   (
    select distinct vcAttrTpName, vcAttrName
    from
    (
     select distinct 'База данных', isnull ( db_name ( iDbId ), '' ) from @tblResData
     union all
     select distinct 'Файл базы данных', isnull ( dbfil.name, '' )
     from @tblResData fildata left outer join sys.master_files dbfil on
        fildata.iDbId = dbfil.database_id and fildata.iFileId = dbfil.file_id
     union all
     select distinct 'Объект', vcObj from @tblResData
     union all
     select distinct 'Индекс', vcIndex from @tblResData
     union all
     select distinct 'Тип блокировки', vcMode from @tblResData
     union all
     select distinct 'Тип блокировки', vcOwnerMode from @tblResData
     union all
     select distinct 'Тип блокировки', vcWaiterMode from @tblResData
    ) attr ( vcAttrTpName, vcAttrName )
   )
   insert into dbo.Attrs ( iAttrTpId, vcAttrName )
    select attrtp.iAttrTpId, attrdata.vcAttrName
    from
     AttrData attrdata
      inner join
     dbo.AttrTypes attrtp on attrdata.vcAttrTpName = attrtp.vcAttrTpName
      left outer join
     dbo.Attrs attr on
      attr.iAttrTpId = attrtp.iAttrTpId and
      attr.vcAttrName = attrdata.vcAttrName
    where attr.iAttrId is null
   
   declare @tblResIds table ( iResId int not null, iResNewId int not null )
   
   ;
   with AttrData
   as
   (
    select attrs.iAttrId, attrs.vcAttrName, attrtp.vcAttrTpName
    from
     dbo.Attrs attrs
      inner join
     dbo.AttrTypes attrtp on attrs.iAttrTpId = attrtp.iAttrTpId
   )
   merge dbo.DeadlockRes as trg
   using
   (
    select iResId, iEventId = @iEventId, iDbId = attrdb.iAttrId, iFileId = attrfil.iAttrId,
        res.iPageId, iObjId = attrobj.iAttrId, iIndId = attrind.iAttrId, res.iHobtId
    from
     (
      select distinct
       iResId,
       iDbId,
       iPageId,
       iFileId,
       iHobtId,
       vcObj,
       vcIndex,
       vcMode
      from @tblResData
     ) res
      inner join
     AttrData attrdb on attrdb.vcAttrTpName = N'База данных' and attrdb.vcAttrName = isnull ( db_name ( res.iDbId ), '' )
      inner join
     AttrData attrobj on attrobj.vcAttrTpName = N'Объект' and attrobj.vcAttrName = res.vcObj
      inner join
     AttrData attrind on attrind.vcAttrTpName = N'Индекс' and attrind.vcAttrName = res.vcIndex
      left outer join
     sys.master_files fil on
      fil.database_id = res.iDbId and
      fil.file_id = res.iFileId
      
      inner join
     AttrData attrfil on attrfil.vcAttrTpName = N'Файл базы данных' and attrfil.vcAttrName = isnull ( fil.name, '' )
   ) as src on 1 = 0
   when not matched then insert ( iEventId, iDbId, iFileId, iPageId, iObjId, iIndId, iHobtId )
    values ( src.iEventId, src.iDbId, src.iFileId, src.iPageId, src.iObjId, src.iIndId, src.iHobtId )
   output src.iResId, inserted.iResId
   into @tblResIds ( iResId, iResNewId )
   ;
   
   insert into dbo.ResOwners ( iResId, iProcessId, iModeId )
    select resids.iResNewId, pr.iProcessId, attr.iAttrId
    from
     @tblResData resdata
      inner join
     @tblResIds resids on resdata.iResId = resids.iResId
      inner join
     dbo.DeadlockChains pr on
      pr.iEventId = @iEventId and
      pr.vcProcessId = resdata.vcOwner
      
      inner join
     (
      select attr.iAttrId, attr.vcAttrName
      from
       dbo.Attrs attr
        inner join
       dbo.AttrTypes attrtp on
        attr.iAttrTpId = attrtp.iAttrTpId and
        attrtp.vcAttrTpName = 'Тип блокировки'
     ) attr on resdata.vcOwnerMode = attr.vcAttrName
   
   insert into dbo.ResWaiters ( iResId, iProcessId, iModeId )
    select resids.iResNewId, pr.iProcessId, attr.iAttrId
    from
     @tblResData resdata
      inner join
     @tblResIds resids on resdata.iResId = resids.iResId
      inner join
     dbo.DeadlockChains pr on
      pr.iEventId = @iEventId and
      pr.vcProcessId = resdata.vcWaiter
      
      inner join
     (
      select attr.iAttrId, attr.vcAttrName
      from
       dbo.Attrs attr
        inner join
       dbo.AttrTypes attrtp on
        attr.iAttrTpId = attrtp.iAttrTpId and
        attrtp.vcAttrTpName = 'Тип блокировки'
     ) attr on resdata.vcWaiterMode = attr.vcAttrName
  commit tran
 end try
 begin catch
  if xact_state () <> 0
  begin
   rollback tran
  end
  set @vcErr = concat ( error_message (), ' ', error_number (), ' ', error_line () )
  ; throw 60000, @vcErr, 1
 end catch
end
go

Протестируем работу уведомления, создав классическую взаимоблокировку. Откроем два соединения. В первом запустим такой код:

create table ##data1 ( i int )
create table ##data2 ( i int )

begin tran

insert into ##data1 ( i )
 values ( 1 )

Во втором соединении запустим код:

begin tran

insert into ##data2 ( i )
 values ( 1 )

В первом попробуем обратиться к таблице, заблокированной вторым соединением:

update ##data2
 set i += 1

Аналогично поступим со вторым соединением:

update ##data1
 set i += 1

В течение нескольких секунд менеджер блокировок обнаруживает неразрешимую цепочку и выбирает второе соединение в качестве жертвы взаимоблокировки. Сделав запрос к таблицам, видим результаты.

declare @iEventId int = 1
select evn.vcProcessId, evn.bVictim, evn.vcResource,
 vcLogin = attrlog.vcAttrName,
 vcHost = attrhost.vcAttrName,
 vcIsolation = attrisol.vcAttrName,
 vcApp = attrapp.vcAttrName,
 evn.dtLastBatchStart, evn.dtLastBathEnd,
 vcLock = attrlock.vcAttrName,
 evn.vcBatch
from
 dbo.DeadlockChains evn
  inner join
 dbo.Attrs attrlog on evn.iLoginId = attrlog.iAttrId
  inner join
 dbo.Attrs attrhost on evn.iHostId = attrhost.iAttrId
  inner join
 dbo.Attrs attrisol on evn.iIsolationId = attrisol.iAttrId
  inner join
 dbo.Attrs attrapp on evn.iAppId = attrapp.iAttrId
  inner join
 dbo.Attrs attrlock on evn.iLockId = attrlock.iAttrId
where evn.iEventId = @iEventId

select vcDb = attrdb.vcAttrName, vcDbFile = attrfil.vcAttrName, res.iPageId, res.iHobtId,
 vcObj = attrobj.vcAttrName, vcIndex = attrind.vcAttrName,
 vcOwner = resowns.vcProcessId, vcOwnerLockMode = lockown.vcAttrName, vcWaiter = reswait.vcProcessId,
 vcWaiterLockMode = lockwait.vcAttrName
from
 dbo.DeadlockRes res
  inner join
 dbo.Attrs attrdb on res.iDbId = attrdb.iAttrId
  inner join
 dbo.Attrs attrfil on res.iFileId = attrfil.iAttrId
  inner join
 dbo.Attrs attrobj on res.iObjId = attrobj.iAttrId
  inner join
 dbo.Attrs attrind on res.iIndId = attrind.iAttrId
  inner join
 dbo.ResOwners own on res.iResId = own.iResId

  inner join
 dbo.DeadlockChains resowns on
  resowns.iEventId = res.iEventId and
  own.iProcessId = resowns.iProcessId
  
  inner join
 dbo.ResWaiters wait on res.iResId = wait.iResId
  inner join
 dbo.DeadlockChains reswait on
  reswait.iEventId = res.iEventId and
  wait.iProcessId = reswait.iProcessId

  inner join
 dbo.Attrs lockown on own.iModeId = lockown.iAttrId
  inner join
 dbo.Attrs lockwait on wait.iModeId = lockwait.iAttrId
where res.iEventId = @iEventId

Полный перечень наборов событий можно посмотреть в специальном системном представлении sys.event_notification_event_types. Тут есть много интересных событий. Например, можно получать уведомления при обнаружении поврежденной страницы в базе данных, нехватки памяти при операциях сортировки или построении hash-таблицы, изменении уровня потребления памяти сервером, расширении файлов базы, эскалации блокировок, изменении в доступах, настройках базы или сервера, наличии заблокированных соединений и многом другом.