SQL 2008 - New Functionality to the dm_os_ring_buffers for Connectivity Troubleshooting

Published Jan 15 2019 12:34 PM 85 Views
Microsoft
First published on MSDN on Dec 28, 2009

Hi,

I wanted to make everybody aware of this feature in SQL 2008.

Are you tired of having to use NetMon to narrow down a connectivity issue with SQL Server 2008 or have to wait for an elusive connectivity error to reoccur?

A new ring buffer called "RING_BUFFER_CONNECTIVITY' has been added to the dmv sys.dm_os_ring_buffers in SQL 2008 RTM.

This will automatically log server-side initiated connection closures , if you see nothing in the dmv, then most likely the client reset/closed the connection. You can enable any connection closure (client or server) logging with trace flag 7827.

Please read this blog for more information!

http://blogs.msdn.com/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-serv...

So if SQL Server 2008 is still online since the connection failure, make sure to capture the information from the sys.dm_os_ring_buffers base on the query in the blog above, as it may give you enough information to narrow down your troubleshooting to the client or server without costly netmon traces.

Hope this helps!

Eric Burgess
SQL Server Escalation Team


%3CLINGO-SUB%20id%3D%22lingo-sub-315917%22%20slang%3D%22en-US%22%3ESQL%202008%20-%20New%20Functionality%20to%20the%20dm_os_ring_buffers%20for%20Connectivity%20Troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315917%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2028%2C%202009%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EHi%2C%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EI%20wanted%20to%20make%20everybody%20aware%20of%20this%20feature%20in%20SQL%202008.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20Are%20you%20tired%20of%20having%20to%20use%20NetMon%20to%20narrow%20down%20a%20connectivity%20issue%20with%20SQL%20Server%202008%20or%20have%20to%20wait%20for%20an%20elusive%20connectivity%20error%20to%20reoccur%3F%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EA%20new%20ring%20buffer%20called%20%22RING_BUFFER_CONNECTIVITY'%20has%20been%20added%20to%20the%20dmv%20sys.dm_os_ring_buffers%20in%20SQL%202008%20RTM.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EThis%20will%20automatically%20log%20%3CB%3E%20server-side%20initiated%20connection%20closures%20%3C%2FB%3E%20%2C%20if%20you%20see%20nothing%20in%20the%20dmv%2C%20then%20most%20likely%20the%20client%20reset%2Fclosed%20the%20connection.%20You%20can%20enable%20any%20connection%20closure%20(client%20%3CB%3E%20or%20%3C%2FB%3E%20server)%20logging%20with%20trace%20flag%207827.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EPlease%20read%20this%20blog%20for%20more%20information!%3C%2FP%3E%0A%20%20%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fsql_protocols%2Farchive%2F2008%2F05%2F20%2Fconnectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx%22%20mce_href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fsql_protocols%2Farchive%2F2008%2F05%2F20%2Fconnectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fblogs.msdn.com%2Fsql_protocols%2Farchive%2F2008%2F05%2F20%2Fconnectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx%20%3C%2FA%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3ESo%20if%20SQL%20Server%202008%20is%20still%20online%20since%20the%20connection%20failure%2C%20make%20sure%20to%20capture%20the%20information%20from%20the%20sys.dm_os_ring_buffers%20base%20on%20the%20query%20in%20the%20blog%20above%2C%20as%20it%20may%20give%20you%20enough%20information%20to%20narrow%20down%20your%20troubleshooting%20to%20the%20client%20or%20server%20without%20costly%20netmon%20traces.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CB%3EEric%20Burgess%20%3C%2FB%3E%20%3CBR%20%2F%3E%20SQL%20Server%20Escalation%20Team%20%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-315917%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2028%2C%202009%20Hi%2C%26nbsp%3BI%20wanted%20to%20make%20everybody%20aware%20of%20this%20feature%20in%20SQL%202008.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315917%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econnectivity%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETroubleshooting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 12:34 PM
Updated by: