SQL Server Ring Buffer--deep understanding of ring buffer CONNECTIVITY

SQL Server Ring Buffer--deep understanding of ring buffer CONNECTIVITY

First we start with the XML returned from the connected ring buffer data.

SELECT CAST (record as XML) as Record_datafrom sys.dm_os_ring_bufferswhere ring_buffer_type= ' ring_buffer_connectivity '

Execute the above statement to get the following result:

650) this.width=650; "title=" clip_image001[6] "style=" border-top:0px;border-right:0px;border-bottom:0px; border-left:0px; "alt=" clip_image001[6] "src=" http://s3.51cto.com/wyfs02/M02/54/11/wKioL1R29lfS1f5MAAOmR_ Wppse646.jpg "height=" 213 "border=" 0 "/>

Click on the XML hyperlink to open the contents of the file to see more readable content, including a basic ring buffer connection Error record.

650) this.width=650; "title=" clip_image002[6] "style=" border-top:0px;border-right:0px;border-bottom:0px; border-left:0px; "alt=" clip_image002[6] "src=" http://s3.51cto.com/wyfs02/M00/54/11/ Wkiol1r29lnrpub4aaqbfx6856c856.jpg "height=" 516 "border=" 0 "/>

You can see that there are quite a lot of useful information in the XML document. Like Sniconsumererror,state and RemoteHost.

It is particularly important to note that the RecordType node, for which we identified above as "error", indicates a connection error message. To identify this type of connection ring Buffer, we can query the Sniconsumererror code number to pinpoint exactly what caused the error.

RecordType contain those values?

1. error– Connection Error

2. logintimers– Connection Timeout

3. connectionclose– Kill Process

You can convert XML data into readable information by using the following script:

; With ringbufferconnectivity as ( selectrecords.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]from ( select cast (record as xml)  as record_datafrom  sys.dm_os_ring_buffersWHERE ring_buffer_type=  ' ring_buffer_connectivity ')  TabACROSS  Apply record_data.nodes ('//record ')  AS records  (record)) Select rbc.*, m.textfrom  RINGBUFFERCONNECTIVITY RBCLEFT JOIN SYS.MESSAGES M ONRBC. ERROR = M.MESSAGE_ID AND M.LANGUAGE_ID = 1033WHERE RBC. Recordtype= ' Error '  --COMMENT OUT TO SEE ALL RECORDTYPESORDER BY RBC. Recordtime desc

After executing the above query, you will get the following readable result. In this query, we correlate the ring buffer data with the sys.messages view to crawl the text of the error ID. With this information we can trace the exact information that caused the error:login to fail.

650) this.width=650; "title=" clip_image003[6] "style=" border-top:0px;border-right:0px;border-bottom:0px; border-left:0px; "alt=" clip_image003[6] "src=" http://s3.51cto.com/wyfs02/M00/54/11/ Wkiol1r29lrzajh4aammbdemnq0680.jpg "height=" 201 "border=" 0 "/>

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1583446

SQL Server Ring Buffer--deep understanding of ring buffer CONNECTIVITY