Return
-
Title
Deadlock sql text field content size is truncated for SQL Server -
Description
When navigating to SQL PI | SQL Activity | Deadlocks, truncation is shown for the sql deadlock information.
The same happens when a SSMS file is downloaded from Foglight.
-
Resolution
The system_health event session is specifically prone to having the maximum, 5000, number of events collected but the ring_buffer, but only a smaller subset of the events can actually be output by the DMV due to the sizes of the events that get collected by that event session.The events most likely to have a fairly large size are the sp_server_diagnostics_component_output and the xml_deadlock_report events, since the sizes of the XML being returned by both of those events depends on the specifics of the conditions for when they fired.sp_server_diagnostics_component_output events have been encountered that are over 512KB in size on busy servers when problems occur, so that would significantly limit the available data that could be returned by the DMV for the ring_buffer target when one of those events is contained in the output XML. -
Additional Information
Source: https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/ f