Reading the Connectivity Ring Buffer for Dropped Connections

The connectivity ring buffer is a small, short-lived log of recent connection events inside SQL Server. When an application says “the connection dropped” and the error log is silent, it is worth a look. Treat what you find as a clue, not as a full history.

Why look at it at all

Here is a call I have taken in many forms. The application team says users got kicked out at 2 AM. You open the SQL Server error log. Nothing. No error, no restart, no clue. The buffer is the next place to look.

It lives in sys.dm_os_ring_buffers, next to many other ring buffer types. It is a fixed-size loop in memory. New records overwrite the oldest ones, and a restart wipes it. So the sooner you look, the better your odds.

Read the raw records first

Filter for RING_BUFFER_CONNECTIVITY and cast the record column to XML. The first query counts what is there and shows how far back it reaches. The second shows the newest raw records. Look at them before you write any clever parsing.

SELECT COUNT(*) AS RecordCount,
       MIN(timestamp) AS OldestTimestamp,
       MAX(timestamp) AS NewestTimestamp
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_CONNECTIVITY';

SELECT TOP (5) timestamp, TRY_CONVERT(xml, record) AS RecordXml
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_CONNECTIVITY'
ORDER BY timestamp DESC, record DESC;

My test server held 16 records. Yours will hold a different number, and some servers hold none at all. An empty result is an answer too. It usually means nothing happened since the last restart, or the buffer has already rolled over.

See which fields your server writes

The XML layout is not a promised, stable format, so do not copy field names from a blog post, mine included. Ask your own records which elements they contain. The query below lists every element name and how often it shows up.

WITH R AS
(SELECT TRY_CONVERT(xml, record) AS x
 FROM sys.dm_os_ring_buffers
 WHERE ring_buffer_type = N'RING_BUFFER_CONNECTIVITY'),
E AS
(SELECT n.value('local-name(.)', 'nvarchar(100)') AS ElementName
 FROM R CROSS APPLY x.nodes('//*') AS t(n))
SELECT ElementName, COUNT(*) AS Occurrences
FROM E
GROUP BY ElementName
ORDER BY ElementName;

On my server, every record has RecordType, Spid, RemoteHost and a group of disconnect flags. Other record types may carry other fields. I only saw ConnectionClose here, so that is all I will show.

Pull the useful fields into columns

Once you have seen the paths, write them out in full. The query below returns one row per record. A flag that is missing in a record comes back as NULL, and NULL is not the same as zero. Keep the raw XML around for anything you cannot explain.

WITH R AS
(SELECT timestamp, TRY_CONVERT(xml, record) AS x
 FROM sys.dm_os_ring_buffers
 WHERE ring_buffer_type = N'RING_BUFFER_CONNECTIVITY')
SELECT TOP (10) timestamp,
  x.value('(/Record/@id)[1]', 'int') AS RecordId,
  x.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'nvarchar(50)') AS RecordType,
  x.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS Spid,
  x.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'nvarchar(256)') AS RemoteHost,
  x.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]', 'int') AS NormalDisconnect,
  x.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]', 'int') AS SessionIsKilled,
  x.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]', 'int') AS DisconnectDueToReadError
FROM R
WHERE x IS NOT NULL
ORDER BY timestamp DESC, RecordId DESC;

On my test server the rows are all ConnectionClose records from the local machine. NormalDisconnect is 0, DisconnectDueToReadError is 0, and SessionIsKilled is 1. I would not build a theory on one flag. Read them together, and compare them with what the application logged.

Turn the timestamp into a real time

The timestamp column is not a date. It counts milliseconds since the machine started, on the same clock as ms_ticks in sys.dm_os_sys_info. Subtract the two and you get the age of a record. Add that offset to the current time and you get a real time.

The record XML also carries its own RecordTime as text. It is in UTC, so compare it with SYSUTCDATETIME, not with your local clock. On my server the two columns agreed to within a second.

SELECT TOP (5) r.timestamp AS RingTimestamp,
       (i.ms_ticks - r.timestamp) / 1000 AS AgeSeconds,
       DATEADD(MILLISECOND, r.timestamp - i.ms_ticks, SYSUTCDATETIME()) AS DerivedUtc,
       TRY_CONVERT(xml, r.record).value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'nvarchar(50)') AS RecordTimeText
FROM sys.dm_os_ring_buffers AS r
CROSS JOIN sys.dm_os_sys_info AS i
WHERE r.ring_buffer_type = N'RING_BUFFER_CONNECTIVITY'
ORDER BY r.timestamp DESC, r.record DESC;

Grab it early, and say what it cannot tell you

If disconnects keep coming back, copy the raw XML into a table on a schedule, together with the collection time and the server start time. Do not poll every second.

I did not create a failed login on this shared test server, so I cannot show what an error record looks like. On your own test server you can try it once: connect with a wrong password, then rerun the queries. Please do not hammer a real account. You will lock it out and set off the security team.

When you report, say which records you had, which fields you read, and what period the buffer plausibly covers. If the server restarted after the symptom, say the evidence is gone. A client cancel, a login failure and a network drop all look like “disconnected” to a user, so line up the times with the error log and the application log.

The queries only read, so you can run them any time you are curious.

A ring buffer record is not your history, it is a short-lived clue.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.