Connectivity troubleshooting in SQL Server 2008 with the Connectivity Ring Buffer

Articolo
05/20/2008

SQL Server 2008 contains a new feature designed to help troubleshoot particularly tricky connectivity problems. This new feature, the Connectivity Ring Buffer, captures a record for each server-initiated connection closure, including each kill of a session or Login Failed event. To facilitate efficient troubleshooting, the ring buffer attempts to provide information to correlate between the client failure and the server’s closing action. 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. The Connectivity Ring Buffer’s records are exposed by a DMV which can be queried:

SELECT CAST(record AS XML) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

This will select all records as XML types; in Management Studio, you can click the records to get a more readable version. If you want to use SQL queries on the XML records to locate your particular problem, you can also use SQL Server’s XML support to turn this into a temp table and query against the records.

A basic ring buffer entry: killed SPID

An easy way to cause a server-initiated connection closure is to open two connections to a SQL Server, find the SPID of one of the connections, and then kill that spid from the other connection:

C:>osql -E
1> SELECT @@spid
2> go
------
51
(1 row affected)

C:>osql -E
1> kill 51
2> go
1>

If you try this and then query the ring buffer, you will get a result which will look like this:

<RecordType>ConnectionClose</RecordType>

</TdsBuffersInformation>

</TdsDisconnectFlags>

</ConnectivityTraceRecord>

<Stack>

Comments

Anonymous
September 28, 2008
What are the debug options for 2005? I have exactly that 258 error, and need to find out the root cause.
Anonymous
March 13, 2009
Sent: Monday, March 09, 2009 6:20 PM Subject: Ring Buffers in SQL2008   Hi, do you have any documentation/white
Anonymous
March 24, 2009
Q：对于复杂的连接问题，我如何获得更多的信息以用于纠错？ A： SQLServer2008包含了一个用于帮助特别困难的连接问题纠错的新特性-连接环缓冲区（ConnectivityRing...
Anonymous
July 27, 2011
(This comment has been deleted per user request)
Anonymous
April 02, 2012
We are seeing lots of cases where most of the delay is due to LoginTriggerAndResourceGovernorProcessingInMilliseconds. Can you direct us to where we can find more info on what could cause this? We are using SQL 2008 R2 on Win 2008. Thank You
Anonymous
June 20, 2012
Hi, I am trying to troubleshoot some intermittent connectivity errors to my Sql server 2008 R2 RTM version and used the sys.dm_os_ring_buffers for this task. However, in the output the record time displays wrong time even though the client and server machines have same system time and are in the same domain. It is 21st June 8:30 am EST here but the record time shows the following values: 2012-06-21 10:21:59.110 2012-06-21 10:21:59.110 This is weird. My client uses a legacy odbc connection (System DSN). Any ideas why the time recorded is wrong? Is my SQL machine receiving wrong packets? Please suggest some solution. Thanks Chandan
Anonymous
September 21, 2014
Hi, Great article to understand the underlying concept. I have the same issue in my environment and what I can see is LoginTaskEnqueuedInMilliseconds is taking more time(2290 out of 2803) here. So does this mean that SQL server is having resource contention which prevents to establish the connection or does it have network issue? Thanks in advance.!

Condividi tramite

Connectivity troubleshooting in SQL Server 2008 with the Connectivity Ring Buffer

A basic ring buffer entry: killed SPID

Comments

Risorse aggiuntive