Solved

Network error code 0x2745 occurred while establishing a connection; the connection has been closed

Posted on 2015-01-09
13
693 Views
Last Modified: 2015-01-16
Hi,

We are facing issue with Intermediate   disconnections between DB and Application

SQL Server version:SQL Server 2008R2 with SP1

Log showing below errors :

Network error code 0x2745 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 35904 ms, enqueued 6843 ms, network writes 202053 ms, network reads 6 ms, establishing SSL 31888 ms, negotiating SSPI 0 ms, validating login 1 ms, including user-defined login processing 0 ms. [CLIENT: xxx.xx.xx.xxx]
0
Comment
Question by:GK GK
  • 6
  • 6
13 Comments
 
LVL 25

Expert Comment

by:Mohammed Khawaja
ID: 40539894
Seen this before, problem was caused by Symantec Endpoint Protection Client software.  If you are using Symantec or any other Endpoint protection software, could you disable and try it again.
0
 

Author Comment

by:GK GK
ID: 40540006
Thanks Mohammed

I will wait some more time from experts
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 40542234
Hello,

I don't think SQL Server closes this connection, is it possible that SQL Server loses its connection with domain controller? Have you seen any other errors in the SQL Server error log near those posted here? Please check Windows Security event log for any clues (match date and time with those logged in SQL Server).

As last resort you may turn on 3689 and 4029 trace flags and wait for next error to come. Then get details from SQL Server error log and get back here pasting them. However it is unlikely that SQL Server closes those connections.

DBCC TRACEON (3689,4029,-1)
GO

Open in new window



Ps. You have quite old version of SQL Server, I suggest you to update to SQL Server 2008 R2 SP3.
Regards,
Daniel
0
 

Author Comment

by:GK GK
ID: 40542669
Thanks for update,

I verified DBCC TRACESTATUS in Server found below result.

TraceFlag      Status      Global      Session
2861      1      1      0

If I enabled "DBCC TRACEON (3689,4029,-1)" is there any impact in my server ?

Note: Soon I going to update to SP3.

Thanks in advance

Regards,
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 40542724
Hi,
Why did you enable 2861 trace flag? You can make serious performance problems by enabling this trace flag on a busy system. Therefore  I encourage you to disable it.

Trace flags 3689,4029 enables SQL Server to record detailed error message for login failures. However you shouldn't start from enabling those. First verify SQL Server error log for other messages as well as Windows eventlogs. You can start Profiler trace and track user error message events. If nothing is found then consider enabling flags.
0
 

Author Comment

by:GK GK
ID: 40542733
Thanks for suggestions and quick update.

I will disable 2861 trace flag.
mean while we verified all logs but we did not find any thing in logs  during the time of issue .

Regards,
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 40542751
Can you verify what changed recently that the error is happening?

Time spent during login: total 35904 ms, enqueued 6843 ms, network writes 202053 ms, network reads 6 ms, establishing SSL 31888 ms, negotiating SSPI 0 ms, validating login 1 ms, including user-defined login processing 0 ms.

In your error there is establishing SSL high time, in case of that please verify whether certificate being used is valid and is trusted on the client?

Did you verify also Windows Security event log? Did you verify logs at the client side?
0
 

Author Comment

by:GK GK
ID: 40542765
Thanks for Reply and great support  Daniel .

Here problem is we are facing Intermediate disconnect  issue with only one database among the 180 Databases in the SQL Server instance
This is the reason we are unable to find the root cause why that particular database disconnecting intermediately  connections.

when check trace logs     (2861) we found particular error in Trace logs.

Lot of thanks for support.

Regards,
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 40542779
Ok, that is some more information.
Can you verify whether anything is hanging up with THREADPOOL wait type?

SELECT * 
FROM sys.dm_os_wait_stats
WHERE wait_type = 'THREADPOOL'

Open in new window


It is more advanced but you can try to search for connectivity ring buffer:

SELECT CAST(record AS XML) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

Open in new window


Query will return xmls, just click on each to view details.

Additionally if you have connections to only one db facing issues it may be the specific clients/application failing. It may be wise to verify interconnect between those specific clients\application and you SQL Server instance.
0
 

Author Comment

by:GK GK
ID: 40542794
Hi Daniel,

Below is the result.

wait_type      waiting_tasks_count      wait_time_ms      max_wait_time_ms      signal_wait_time_ms
THREADPOOL      84621                     15280207              24940                               64

Regards,
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 40542831
Dear Snivas,

I suppose you are experiencing these because of long blocking chain, it is highly possible that your trace flag has to do with that. Before getting into deeper analysis I'd recommend you to remove trace flag and update SQL Server to SQL Server 2008 R2 SP3.
0
 

Author Comment

by:GK GK
ID: 40542963
Sure Daniel,

I turnoff trace flag by using
DBCC TRACEOFF (2861, -1);
GO
I will ask patching team to update to SP3.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 40542966
Snivas,
Please verify SQL Server startup parameters also, if that flag is set there it will be turned on after each SQL Server restart. You can modify startup parameters by using SQL Server configuration manager (start->run->sqlservermanager10.msc), reference:
How to: Configure Server Startup Options (SQL Server Configuration Manager)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now