SQL Server Script: Finding past connectivity issues October 25th, 2012

Vinod Kumar

In my day job I meet a lot of DBA’s who try to send some really interesting requirements. Recently, one of these proactive customer wanted to know if there were any past errors that they can query from the DMVs about connectivity issues. This was interesting and I had sent them the below script for use.

CONVERT (VARCHAR(30), Getdate(), 121)
AS [Run_Time],
Dateadd (ms, ( ORB.[timestamp]  OSI.ms_ticks ), Getdate())
AS Time_Stamp,
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/RecordType)[1]’, ‘varchar(50)’) AS [Action],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/RecordSource)[1]’, ‘varchar(50)’) AS [Source],
Cast(record AS XML).value(‘(//Record/ConnectivityTraceRecord/Spid)[1]’,‘int’) AS [SPID],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/RemoteHost)[1]’, ‘varchar(100)’) AS [RemoteHost],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/RemotePort)[1]’, ‘varchar(25)’) AS [RemotePort],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/LocalPort)[1]’, ‘varchar(25)’) AS [LocalPort],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]’, ‘int’) AS [isPhysicalConnectionIsKilled],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]’, ‘int’) AS [isDisconnectDueToReadError],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]’, ‘int’) AS [isNetworkErrorFound],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]’, ‘int’) AS [isErrorBeforeLogin],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]’, ‘int’) AS [isSessionKilled],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]’, ‘int’) AS [isNormalDisconnect],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout)[1]’, ‘int’) AS [isNormalLogout],
Cast(record AS XML).value(‘(//Record/@id)[1]’, ‘bigint’)
AS [Record_Id],
Cast(record AS XML).value(‘(//Record/@type)[1]’, ‘varchar(30)’) AS [Type],
Cast(record AS XML).value(‘(//Record/@time)[1]’, ‘bigint’)
AS [Record_Time]
FROM   sys.dm_os_ring_buffers ORB
CROSS JOIN sys.dm_os_sys_info OSI
AND Cast(record AS XML).value (‘(//Record/ConnectivityTraceRecord/Spid)[1]’, ‘int’) <> 0
ORDER  BY ORB.timestamp ASC 

There are so many interesting hidden information that is available inside a DMV that we miss to visualize. If you want, we can also look into why the connection got lost or why it didn’t go through.


If you want to be a proactive DBA then using the DMV can be tricky as the values can be lost in a restart of the instance. But using other mechanisms will also help –

  1. Use default logging of Invalid Logins.
  2. Use SQL Server Auditing to track Failed Logins

Being proactive DBA is a challenge and we need to use every trick inside SQL Server to achieve our requirements. Hope you found the script useful.

Tags: , , , , , , ,

This entry was posted on Thursday, October 25th, 2012 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply