SQL Server deadlocks are missing and not displayed in dashboard (4310482)

Foglight is not picking up deadlock activity on a monitored SQL Server instance.

Foglight generates an email notification or alarm advising a deadlock condition has occurred, but the Activity dashboard does not display any data indicating a deadlock has or is occurring.

Cause 1

Some deadlocks cannot be captured by the trace mechanism.

Cause 2

No Deadlocks are occurring on the SQL Instance. Confirm on the SQL Instance it is registering deadlocks.

Cause 3

Foglight initially displays only the last 60 minutes of activity by default.

Cause 4

Foglight only recognized International standard date and time format. FglAM and the SQL Server is using Thailand date and time format. For e.g. 2555-MM-DD. 

Cause 5  (Not relevant for Foglight Cloud users)

The is a maximum limit of 100 rows set for the deadlocks displaying in the Foglight view.

Cause 6  (Not relevant for Foglight Cloud users)

The number of deadlocks may be limited by the DBSS-MaxDeadlocks registry value. 
 

Cause 7

Performance issues on the host caused by the Extended Events collection used by default in the Deadlocks collection
 

Cause 8 

SQL Server Extended Events deadlock collection hanging and failing on time out

Cause 9

Deadlock SQL text field content size is truncated for SQL Server
 

Cause 10

Deadlocks not detected with "Failed to Process Deadlock XML" in SQL Server agent logs

Resolution 1

Please note that the SQL Server cartridge changed to use Extended Events instead of traces for SQL Server versions above SQL Server 2005. This can be adjusted manually in the Agent Status Properties to switch the configuration back to using Traces.

Resolution 2

A. Confirm deadlock activity on the SQL instance by enabling TRACE FLAGS 1204 and 1222 with Global option(-1) and check the SQL Server Error log

Check enabled Trace options with:  DBCC TRACESTATUS

- or - 

B. Execute query on SQL Server Instance to view current Extended Events(MSSQL 2008+) system health data:

NOTE: If no events are returned or activity logged , deadlocks should not register in Foglight, but the above query may only evaluate current data in cache and my not contain past historical events due to instance restarts or log rolls.

Confirm deadlocks are happening in the instance by creating a test deadlocks as described in Knowledgebase article 4378359. New deadlocks will register within 5 mins or as fast as 60 seconds. 

Resolution 3

Note the actual time of the event in the Alarm or Email Notification and adjust the dashboard Timeline (zonar) to match the timeframe of the actual event. e.g. 4h, 8h, 24h, 48h, Yesterday, Last week, etc

Resolution 4

Setup FglAM and SQL Server instance to use International standard date and time format.

Resolution 5  (Not relevant for Foglight Cloud users)

The 100 record limit in the deadlock collection can be changed in the view definition in Foglight as shown in the image below.

Click To See Full Image.

Resolution 6  (Not relevant for Foglight Cloud users)

The number of deadlocks displayed in the SQL Server agent is managed by the DBSS-MaxDeadlocks registry value.

1. Every sample (extended events) collect all deadlocks that occurred since last sample
2. The number of deadlocks shown on the dashboard is controlled by "DBSS-MaxDeadlocks" registry value. 
   1. if the number of deadlocks is less than the Registry Variable "DBSS-MaxDeadlocks", then all deadlocks are shown and their number is displayed
   2. if the number of deadlocks is bigger than the Registry Variable, Foglight shows "Last <DBSS-MaxDeadlocks value> deadlocks"
3. The list can be sorted by Time (Asc or Desc order)

Resolution 7

Refer to Knowledgebase article 4228897  
 

Resolution 8

Refer to knowledgebase article 4374214
 

Resolution 9 

Refer to knowledgebase article 4228620  

Resolution 10

Refer to knowledgebase article 4305787