1
2
3
4
5
6
7
8
9
10
11
12
13
 

CREATE DATABASE [$(DatabaseMonitoring)] ON  PRIMARY 
( NAME = N'DBMonitoring' , FILENAME=@DefaultData+N'\DBMonitoring_1.mdf' ,SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%), 
 FILEGROUP [QueueStorage] 
( NAME = N'DBMonitoring_2', FILENAME=@DefaultData+'\DBMonitoring_2.ndf' ,SIZE = 3072KB , MAXSIZE = 2048GB, FILEGROWTH = 20%)
 LOG ON 
( NAME = N'DBMonitoring_log', FILENAME=@DefaultLog+'\DBMonitoring.ldf' ,SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
)

ALTER DATABASE [$(DatabaseMonitoring)]
    SET TRUSTWORTHY ON

ALTER DATABASE [$(DatabaseMonitoring)]
    SET RECOVERY SIMPLE

1
2
3
4
5
6
 

IF NOT EXISTS (SELECT * FROM sys.databases
               WHERE name = 'DBMonitoring'
               AND IS_BROKER_ENABLED = 1)
BEGIN
    ALTER DATABASE $(DatabaseMonitoring) SET ENABLE_BROKER ;
END

1
2
3
4
5
6
7
8
9
10
 

CREATE QUEUE NotifyQueue
ON QueueStorage;
GO

CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] 
);
GO

1
2
3
4
5
6
7
 

 WAITFOR (
        RECEIVE TOP(1) 
            @message_type_name=message_type_name,  
            @message_body=message_body,      
            @dialog = conversation_handle    
            FROM NotifyQueue
    ), TIMEOUT 2000

1
 

SET @EventType = @message_body.value('(//EventType)[1]','varchar(50)')

1
2
 

IF (@EventType='DEADLOCK_GRAPH ' OR @EventType='LOCK_DEADLOCK' OR @EventType='LOCK_DEADLOCK_CHAIN' OR @EventType='LOCK_ESCALATION') 
    INSERT [DLHistoric] VALUES(getdate(),@EventType,@message_body)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 

IF @EventType='BLOCKED_PROCESS_REPORT'
    BEGIN
      INSERT INTO [dbo].[DLEventsLog]
         ([Session_ID]
         ,[EventTime]
         ,[Blocking_Spid]
         ,[Query_Statement]
         ,[WaitingForRessource]
         ,[Transaction_count]
         ,[ClientApplication]
         ,[Hostname]
         ,[LoginName]
         ,[WaitTime_ms]
         ,[LastBatchStartedTime]
           ,[lastBatchCompletedTime]
         ,[UpdateTime]
         ,[DB_Name]
         ,[lock_mode_request]
         ,[Isolationlevel]
         ,[Status]
         ,[PlanExec]
         ,[Transaction_id]
           ,[xactid]
           ,[BlockedProcesses]
           ,[UserConnections]
          )

    select   @message_body.value('(//blocking-process/process/@spid)[1]', 'int') as Session_ID
        ,@message_body.value('(//PostTime)[1]', 'datetime') as EventTime
        ,0 as Blocking_Spid
        ,@message_body.value('(//blocking-process/process/inputbuf)[1]', 'varchar(max)') as Query_Statement
        ,@message_body.value('(//blocked-process/process/@waitresource)[1]', 'varchar(50)') as WaitingForRessource
        ,@message_body.value('(//blocking-process/process/@transcount)[1]', 'int') as Transaction_count
        ,@message_body.value('(//blocking-process/process/@clientapp)[1]', 'varchar(50)') as ClientApplication
        ,@message_body.value('(//blocking-process/process/@hostname)[1]', 'varchar(50)') as Hostname
        ,@message_body.value('(//blocking-process/process/@loginname)[1]', 'varchar(50)') as LoginName
        ,@message_body.value('(//blocked-process/process/@waittime)[1]', 'int') as WaitTime_ms
        ,@message_body.value('(//blocking-process/process/@lastbatchstarted)[1]', 'datetime') as LastBatchStartedTime
        ,@message_body.value('(//blocking-process/process/@lastbatchcompleted)[1]', 'datetime') as LastBatchCompletedTime
        ,[UpdateTime]=getdate()
        ,db_name(@message_body.value('(//DatabaseID)[1]','int')) as [DB_Name]
        ,@message_body.value('(//blocked-process/process/@lockMode)[1]', 'varchar(50)') as Lock_Mode_Request
        ,@message_body.value('(//blocking-process/process/@isolationlevel)[1]', 'varchar(max)') as IsolationLevel
        ,@message_body.value('(//blocking-process/process/@status)[1]', 'varchar(50)') as [Status]
        ,(select qt.query_plan
          FROM sys.dm_exec_requests  qs
          cross apply sys.dm_exec_query_plan(qs.plan_handle) as qt
          where qs.session_id=@message_body.value('(//blocking-process/process/@spid)[1]', 'int'))as PlanExec
        ,@message_body.value('(//TransactionID)[1]', 'int') as [Transaction_ID]
        ,@message_body.value('(//blocking-process/process/@xactid)[1]', 'int') as [xactid]
        ,(select top 1 cntr_value from sys.sysperfinfo where (object_name='SQLServer:General Statistics' OR object_name= (Select('MSSQL$'+convert(varchar,SERVERProperty ('InstanceName'))+':General Statistics'))) and counter_name ='Processes blocked') as BlockedProcesses
        ,(select top 1 cntr_value from sys.sysperfinfo where (object_name='SQLServer:General Statistics' OR object_name= (Select('MSSQL$'+convert(varchar,SERVERProperty ('InstanceName'))+':General Statistics'))) and counter_name ='User Connections') as USerConnections

1
2
3
4
5
6
 

ALTER QUEUE [dbo].[NotifyQueue]
    WITH ACTIVATION (
         STATUS = ON,                    
         PROCEDURE_NAME = [LogEvents] ,
         MAX_QUEUE_READERS = 2,                        
         EXECUTE AS 'DBO'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 

CREATE EVENT NOTIFICATION BlockedProcessNotification
  ON SERVER
  FOR BLOCKED_PROCESS_REPORT
  TO SERVICE 'NotifyService', 'current database';
  GO

  CREATE EVENT NOTIFICATION Deadlock_Graph_EventsNotification
  ON SERVER
  FOR DEADLOCK_GRAPH 
  TO SERVICE 'NotifyService', 'current database';
  GO

  CREATE EVENT NOTIFICATION LOCK_EscalationNotification
  ON SERVER
  FOR LOCK_ESCALATION
  TO SERVICE 'NotifyService', 'current database';
  GO