SHARE
TWEET

Connection timeouts in Ring Buffer

SQLSoldier Nov 9th, 2017 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare @LanguageID int;
  2.  
  3. Select @LanguageID = lcid
  4. From sys.syslanguages
  5. Where name = @@Language;
  6.  
  7. WITH RingBufferXML
  8. As (SELECT CAST(record as xml) AS RecordXML
  9.     FROM sys.dm_os_ring_buffers
  10.     WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'),
  11. RingBufferConnectivity
  12. As (SELECT x.y.value('(/Record/@id)[1]', 'int') AS [RecordID],
  13.         x.y.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS RecordType,
  14.         x.y.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS RecordTime,
  15.         x.y.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS Error,
  16.         x.y.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS State,
  17.         x.y.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS SPID,
  18.         x.y.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS RemoteHost,
  19.         x.y.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS RemotePort,
  20.         x.y.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS LocalHost,
  21.         x.y.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TotalTime)[1]', 'int') AS TotalTime,
  22.         x.y.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/EnqueueTime)[1]', 'int') AS EnqueueTime,
  23.         x.y.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetWritesTime)[1]', 'int') AS NetWritesTime,
  24.         x.y.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetReadsTime)[1]', 'int') AS NetReadsTime,
  25.         x.y.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/TotalTime)[1]', 'int') AS SslTotalTime,
  26.         x.y.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/TotalTime)[1]', 'int') AS SspiTotalTime,
  27.         x.y.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime)[1]', 'int') AS TriggerAndResGovTime
  28.     FROM RingBufferXML
  29.     CROSS APPLY RecordXML.nodes('//Record') AS x(y))
  30. SELECT RBC.*, m.text
  31. FROM RingBufferConnectivity RBC
  32. LEFT JOIN sys.messages M ON
  33.     RBC.Error = M.message_id AND M.language_id = @LanguageID
  34. WHERE RBC.RecordType IN ('Error', 'LoginTimers')
  35. ORDER BY RBC.RecordTime DESC;
RAW Paste Data
Top