Copy the script in SQL Management Studio
Run it
If you want you use take the data to temp table for other formatting purposes.
Copy the script in SQL Management Studio
Run it
If you want you use take the data to temp table for other formatting purposes.
set nocount on IF Object_id('tempdb..#CTE_ring_buffer_Connectivity_Temp') IS NOT NULL DROP TABLE #CTE_ring_buffer_Connectivity_Temp create table #CTE_ring_buffer_Connectivity_Temp ( RecordTime datetime, text varchar(4096), id int, type varchar(50), time varchar(50), RecordType varchar(50), RecordSource varchar(50), Spid int, SniConnectionId uniqueidentifier, SniProvider int, OSError int, SniConsumerError int, State int, RemoteHost varchar(50), RemotePort varchar(50), LocalHost varchar(50), LocalPort varchar(50), RecordTime2 datetime, TotalLoginTimeInMilliseconds bigint, LoginTaskEnqueuedInMilliseconds bigint, NetworkWritesInMilliseconds bigint, NetworkReadsInMilliseconds bigint, SslProcessingInMilliseconds bigint, SspiProcessingInMilliseconds bigint, LoginTriggerAndResourceGovernorProcessingInMilliseconds bigint, TdsInputBufferError int, TdsOutputBufferError int, TdsInputBufferBytes int, PhysicalConnectionIsKilled int, DisconnectDueToReadError int, NetworkErrorFoundInInputStream int, ErrorFoundBeforeLogin int, SessionIsKilled int, NormalDisconnect int, message_id int, language_id int, severity int, is_event_logged int, text2 varchar(4096)) -- Up to 1k records in the ring buffer are persisted for as long as the server is online, and after 1000 records, the buffer wraps around and begins replacing the oldest records. -- Define the CTE expression name and column list. ; WITH CTE_ring_buffer_Connectivity as -- Define the CTE query. ( SELECT record.value('(Record/@id)[1]', 'int') as id,--column 1 record.value('(Record/@type)[1]', 'varchar(50)') as type, record.value('(Record/@time)[1]', 'varchar(50)') as time, record.value('(Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(50)') as RecordType, record.value('(Record/ConnectivityTraceRecord/RecordSource)[1]', 'varchar(50)') as RecordSource, record.value('(Record/ConnectivityTraceRecord/Spid)[1]', 'int') as Spid, record.value('(Record/ConnectivityTraceRecord/SniConnectionId)[1]', 'uniqueidentifier') as SniConnectionId, record.value('(Record/ConnectivityTraceRecord/SniProvider)[1]', 'int') as SniProvider, record.value('(Record/ConnectivityTraceRecord/OSError)[1]', 'int') as OSError, record.value('(Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') as SniConsumerError, record.value('(Record/ConnectivityTraceRecord/State)[1]', 'int') as State, record.value('(Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(50)') as RemoteHost, record.value('(Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(50)') as RemotePort, record.value('(Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(50)') as LocalHost, record.value('(Record/ConnectivityTraceRecord/LocalPort)[1]', 'varchar(50)') as LocalPort, record.value('(Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') as RecordTime, record.value('(Record/ConnectivityTraceRecord/LoginTimers/TotalLoginTimeInMilliseconds)[1]', 'bigint') as TotalLoginTimeInMilliseconds, record.value('(Record/ConnectivityTraceRecord/LoginTimers/LoginTaskEnqueuedInMilliseconds)[1]', 'bigint') as LoginTaskEnqueuedInMilliseconds, record.value('(Record/ConnectivityTraceRecord/LoginTimers/NetworkWritesInMilliseconds)[1]', 'bigint') as NetworkWritesInMilliseconds, record.value('(Record/ConnectivityTraceRecord/LoginTimers/NetworkReadsInMilliseconds)[1]', 'bigint') as NetworkReadsInMilliseconds, record.value('(Record/ConnectivityTraceRecord/LoginTimers/SslProcessingInMilliseconds)[1]', 'bigint') as SslProcessingInMilliseconds, record.value('(Record/ConnectivityTraceRecord/LoginTimers/SspiProcessingInMilliseconds)[1]', 'bigint') as SspiProcessingInMilliseconds, record.value('(Record/ConnectivityTraceRecord/LoginTimers/LoginTriggerAndResourceGovernorProcessingInMilliseconds)[1]', 'bigint') as LoginTriggerAndResourceGovernorProcessingInMilliseconds, record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'int') as TdsInputBufferError, record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'int') as TdsOutputBufferError, record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'int') as TdsInputBufferBytes, record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]', 'int') as PhysicalConnectionIsKilled, record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]', 'int') as DisconnectDueToReadError, record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]', 'int') as NetworkErrorFoundInInputStream, record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]', 'int') as ErrorFoundBeforeLogin, record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]', 'int') as SessionIsKilled, record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]', 'int') as NormalDisconnect --record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout)[1]', 'int') as NormalLogout from (SELECT CAST(record as xml) as record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY') as tab ) INSERT INTO #CTE_ring_buffer_Connectivity_Temp SELECT c.RecordTime,m.[text],* FROM CTE_ring_buffer_Connectivity c LEFT JOIN sys.messages m ON c.SniConsumerError = m.message_id AND m.language_id = 1033 ORDER BY c.RecordTime DESC go select * from #CTE_ring_buffer_Connectivity_Temp set nocount off