Thanks Jeff.

Thank you.

Thanks for the inputs. Yes, as you mentioned I think we are over doing on the disks. I can see a lot of I/O taking longer than 15 secs messages...

Thank you.

Thank you Alej.

Thank you very much Sir.

Its a table created by INFORMATICA ETL TOOL. so we dont have control over it.

Heard about DBCC Cleantable cmd. will it be useful in this case?

As mentioned, the Log backups are stopped. Looks like some connections are active on primary replica. This is what causing the log gap.

This is code which is working for me.

CREATE EVENT SESSION [XE_testdb_DEADLOCKS] ON SERVER

ADD EVENT sqlserver.error_reported(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER')),

ADD EVENT sqlserver.rpc_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER')),

ADD EVENT sqlserver.sp_statement_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER')),

ADD EVENT sqlserver.sql_batch_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE...

Hi All,

Removed the filter clause in the extended event and was able to see the deadlock graph xevent trace. Looks like deadlocks happening in master. It worked for me after...

Is there any issue with the ACTION selection w.r.t. xml_deadlock_report ? I removed and tested but didn't make much difference.

Applied the latest CU for sql server 2017. still the issue....

• This reply was modified 6 years ago by vsamantha35.

I am testing this XE on Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise...

No Sir, I don't see any tab.

Hi Sir,

Removed the system filter. However, still it is not capturing deadlock graph. Don't know why.

CREATE EVENT SESSION [xe_deadlocks] ON SERVER

ADD EVENT sqlserver.error_reported(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb')),

ADD EVENT sqlserver.rpc_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb')),

ADD EVENT sqlserver.sp_statement_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb')),

ADD...

Thanks for the detailed explanation. I will check with the app team if they can really reproduce issue.