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 |