I encountered an interesting
question
over on the MSDN forums concerning a poster that was reporting
experiencing an issue whereby the “sa” account kept being locked out.
In scenarios such as this the SQL Server Error Log is your friend, as
it can be configured to record failed login attempts for an instance.
Of course pursuing that avenue of exploration would make for a rather
dull blog post and so instead we’re going to take a look at using the
lesser known DMV
sys.dm_os_ring_buffers.
Note that you use this particular DMV at your own peril considering
Books Online states that:
“The following SQL Server Operating
System–related dynamic management views are Identified for informational
purposes only. Not supported. Future compatibility is not guaranteed.”
In other words don’t go making use of this DMV as an integral part of
your monitoring solution but rather be mindful of it’s existence as a
possible tool for ad-hoc use.
Sounds like just the sort of thing we should look into. The DMV
sys.dm_os_ring_buffers maintains
approximately 1000 records, before wrapping around and replacing the
oldest entries first. It exposes four columns but we’re primarily only
interested in one of them,
record, which contains XML data. Yippee! We’ll also filter the results to include just the connectivity ring buffer data.
SELECT CAST(record as xml) AS record_data |
FROM sys.dm_os_ring_buffers |
WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY' |
Executing the statement above will produce output similar to the following:
Clicking an XML fragment hyperlink will open the contents into a more
readable format in a new tab in SSMS for you similar to below, showing a
basic ring buffer connectivity
error record.
<Record id="5577586" type="RING_BUFFER_CONNECTIVITY" time="9300997329"> |
<ConnectivityTraceRecord> |
<RecordType>Error</RecordType> |
<RecordSource>Tds</RecordSource> |
<SniConnectionId>4BBB38FE-B46A-4D6E-917F-5D473464448B</SniConnectionId> |
<SniConsumerError>18456</SniConsumerError> |
<SniProvider>7</SniProvider> |
<RemoteHost>10.323.12.32</RemoteHost> |
<RemotePort>1234</RemotePort> |
<LocalHost>10.132.321.32</LocalHost> |
<LocalPort>1888</LocalPort> |
<RecordTime>2/18/2014 14:2:58.588</RecordTime> |
<TdsInputBufferError>0</TdsInputBufferError> |
<TdsOutputBufferError>0</TdsOutputBufferError> |
<TdsInputBufferBytes>320</TdsInputBufferBytes> |
<PhysicalConnectionIsKilled>0</PhysicalConnectionIsKilled> |
<DisconnectDueToReadError>0</DisconnectDueToReadError> |
<NetworkErrorFoundInInputStream>0</NetworkErrorFoundInInputStream> |
<ErrorFoundBeforeLogin>0</ErrorFoundBeforeLogin> |
<SessionIsKilled>0</SessionIsKilled> |
<NormalDisconnect>0</NormalDisconnect> |
</ConnectivityTraceRecord> |
<frame id="0">0X0000000001D6C34B</frame> |
<frame id="1">0X0000000001D68FDD</frame> |
<frame id="2">0X0000000002A81001</frame> |
<frame id="3">0X0000000001267E98</frame> |
<frame id="4">0X0000000000E215AD</frame> |
<frame id="5">0X0000000000E21492</frame> |
<frame id="6">0X00000000009EBBD8</frame> |
<frame id="7">0X00000000009EB8BA</frame> |
<frame id="8">0X00000000009EB6FF</frame> |
<frame id="9">0X0000000000F08FB6</frame> |
<frame id="10">0X0000000000F09175</frame> |
<frame id="11">0X0000000000F09839</frame> |
<frame id="12">0X0000000000F09502</frame> |
<frame id="13">0X00000000757C37D7</frame> |
<frame id="14">0X00000000757C3894</frame> |
<frame id="15">0X000000007796652D</frame> |
As you can see there’s some rather useful information contained within the XML document. Things such as
SniConsumerError, State and the
RemoteHost responsible for the connection.
The
RecordType node is particularly relevant for the
troubleshooting case our forum friend had, with a node value of “Error”,
indicating a connection error naturally. By identifying a connectivity
ring buffer record of this type, we can then look-up the
SniConsumerError code to establish precisely what error was encountered.
Other
RecordType‘s include
LoginTimers (handy for troubleshooting connection timeouts) and
ConnectionClose (can be used to identify killed Spids).
Great you say but a busy DBA does not want to have to click through
hoards of XML documents in order to find possible information of
interest. Let’s get shredding then:
;WITH RingBufferConnectivity as |
records.record.value('(/Record/@id)[1]', 'int') AS [RecordID], |
records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType], |
records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime], |
records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [Error], |
records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State], |
records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid], |
records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost], |
records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort], |
records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS [LocalHost] |
( SELECT CAST(record as xml) AS record_data |
FROM sys.dm_os_ring_buffers |
WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY' |
CROSS APPLY record_data.nodes('//Record') AS records (record) |
FROM RingBufferConnectivity RBC |
LEFT JOIN sys.messages M ON |
RBC.Error = M.message_id AND M.language_id = 1033 |
WHERE RBC.RecordType='Error' |
ORDER BY RBC.RecordTime DESC |
Executing the query above produces a more readable result set such as
the one below. You can see that in the query we joined our ring buffer
data to the
sys.messages catalog view in order to grab the
text for the Error id. Splendid. Using this information we track down the precise source responsible for the Error: Login failed.
(Note: Sensitive information has been omitted from results above, RemoteHost etc.)
Take a look at the DMV
sys.dm_os_ring_buffers and keep it in mind as a potential troubleshooting aid for the next time you have a connectivity issue to diagnose.
0 Commentaires