0

I have added an extended event log that logs blocked_process_report events and xml_deadlock_report events.

I can exclude certain databases from the blocked_process_report by adding WHERE ([database_name]<>N'XXXXXXXXXXXXXXX').

But how do I exclude a database's xml_deadlock_report events? The property "sqlserver.database_name" appears to be set to "master" for a deadlock event, no matter where the deadlock occured. I suppose this is because in theory multiple databases could be affected by the same deadlock.

enter image description here

The server in question contains a database which constantly has deadlocks, but which is managed by a different entity and does not concern me. I would like to prevent these events from filling up the log.

This is the script used to create the log:

CREATE EVENT SESSION [blocked_process] ON SERVER 

ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([database_name]<>N'XXXXXXXXXXXXXXX')),

ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.username)) 

ADD TARGET package0.event_file(SET filename=N'D:\xxx\blocked_process.xel',max_file_size=(65536),max_rollover_files=(5),metadatafile=N'D:\xxx\blocked_process.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

1 Answer 1

1

I am using Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

SSMS: 13.0.16106.4

I can create a filter for deadlock without any error.

CREATE EVENT SESSION [blocked_process] ON SERVER 
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([database_name]<>N'test')),

ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.username)
    WHERE ([sqlserver].[database_name]<>N'test'))

ADD TARGET package0.event_file(SET filename=N'c:\blocked_process.xel',max_file_size=(65536),max_rollover_files=(5),metadatafile=N'c:\blocked_process.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Here is the picture where I am adding filter (if I am using GUI). enter image description here

2
  • 1
    The problem is that the swlserver.database_name for a deadlock event is not the affected database. For all the deadlocks I found in the log, the database_name is set to 'master', even if the affected tables are in a different database.
    – HugoRune
    Jul 3, 2017 at 8:44
  • (I have updated the question with a screenshot; sorry, my original question was not clear in this regard)
    – HugoRune
    Jul 3, 2017 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.