Sql-server – Network issues with sql server

Networksql serversqlcmd

There are two servers on co-location. First is Windows Server Standard 2008 R2 SP1. Second is Windows Server 2012 + Sql Server Standard 2012 SP1.

First server has a task with a report against tables in Server B. Report runs via sqlcmd11. Normally, it takes 3-4 seconds. But sometimes it hangs in the middle of data receiving (even, If I redirect output to console) and around at 20 seconds after start connection is killed with these errors:

HResult 0x2746, Level 0, State 10
TCP Provider: An existing connection was forcibly closed by the remote host.

HResult 0x2746, Level 0, State 0
Communication link failure

Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total     21107  avg   21107.00 (0.05 xacts per sec.)

Sqlcmd runs script from file. Command line is:

"%ProgramFiles%\Microsoft SQL Server\110\Tools\binn\SQLCMD.EXE" -S "ip,port"
    -U username -P password -u -i script.sql -o output.html -m-1 -p

Query has header:

declare @today datetime = cast(cast(getdate() as date) as datetime)
declare @yesterday datetime = dateadd(day, -1, @today)
declare @days15 datetime = dateadd(day, -15, @today)
declare @days10 datetime = dateadd(day, -10, @today)

Then is contains 5 pairs:

print N'Report header'
select <report body>

Queries doesn't use table variables or any temp table. I read about tcp chimney and other tcp options. Disabling them on both sides didn't help.

Sql Server logs don't have messages about killing my process.

Best Answer

[Can't add comments yet so "commenting" here and a moderator can change it to a comment.]

I would recommend checking the network adapter properties on all systems involved and disabling auto-detect network speed/duplex. If it's set to auto-detect and you don't want to change it without knowing it's causing a problem, copy some large files and see if you're getting the expected throughput. If not, auto-detect has probably not negotiated the correct speed an duplex and this will cause poor throughput and flaky connections. I used to do tech support for a data replication co. and we fixed so many connection stability and throughput issues with this it's not even funny.

Related Question