Unable to show XML. The following error happened: Unexpected end of file has occurred. Line 24137, position 629.

when you execute below query to get system_health status and when click on RingBuffer

SELECT CAST(xet.target_data as xml) FROM sys.dm_xe_session_targets xet 

JOIN sys.dm_xe_sessions xe 

ON (xe.address = xet.event_session_address) 

WHERE xe.name = 'system_health' 

you get below error

TITLE: Microsoft SQL Server Management Studio

------------------------------

Unable to show XML. The following error happened:

Unexpected end of file has occurred. Line 24137, position 629.

One solution is to increase the number of characters retrieved from the server for XML data.

 To change this setting, on the Tools menu, click Options.

------------------------------

BUTTONS:

OK

------------------------------

In order to resolve this issue:-

On SSMS – Click on Tools > options> Query Results >> SQL Server >>General >> Results to Grid >> XML data > Unlimited.

Open a new SSMS window and execute the above query again and issue will not come.

Thanks for Reading..