Hi,


We have SQL server 2012 SP2 installed on Windows 2012R2 Server. I noticed that sometimes, clients cannot connect to the databases. When I check the odbc connection, it fails to get connected.

And in Management Studio the Icon, that represent the server, changes to white circle (please see the screenshot).

I must restart the Windows server to get back the connectivity.

How can I address the problem please?

Thanks.

Hello,

Maybe we can get some help from the ring buffer. Please share the errors (error, state, text).

;WITH RingBufferConnectivity as

(           SELECT

                        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]

            FROM

            (           SELECT CAST(record as xml) AS record_data

                        FROM sys.dm_os_ring_buffers

                        WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'

            ) TabA

            CROSS APPLY record_data.nodes('//Record') AS records (record)

)

SELECT RingBufferConnectivity.*, m.text

FROM RingBufferConnectivity RingBufferConnectivity

LEFT JOIN sys.messages M ON

            RingBufferConnectivity.Error = M.message_id AND M.language_id = 1033

WHERE RingBufferConnectivity.RecordType='Error'

ORDER BY RingBufferConnectivity.RecordTime DESC

  

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Hi,

Can you tell me please how to run this script?

Regards,

Hello,

Open SQL Server Management Studio, connect to the SQL Server instance and run the query. Please read the procedure on the following tutorials:

https://msdn.microsoft.com/en-us/library/ms166996(v=sql.110).aspx

https://msdn.microsoft.com/en-us/library/ms166585(v=sql.110).aspx

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Moderator Monday, July 06, 2015 3:17 PM

Thank you,

I've executed the query and got this message "Login failed for user '%.*ls'.%.*ls%.*ls"

Hello,

State 8 means invalid password. Thats the reason connections are failing. You can read more about this on the following resource:

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx    

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Moderator 10 hours 51 minutes ago