Solved

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

Posted on 2015-01-09
13
640 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 24

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 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

12 Experts available now in Live!

Get 1:1 Help Now