3

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.

Improve this question
8
  • could you post what does sqlcmd do exactly? does it generate text output or staging tables? what is the "report"?
    – WrinkleFree
    Commented Dec 31, 2012 at 9:01
  • @Rohan, I updated post just now.
    – Aen Sidhe
    Commented Dec 31, 2012 at 9:14
  • Just to debug, can you try the following, run the sqlcmd on the SQL Server itself (ServerB) and check for the error, also try using osql (msdn.microsoft.com/en-us/library/ms162806.aspx)
    – WrinkleFree
    Commented Dec 31, 2012 at 9:22
  • Results: sqlcmd on server B ran report for 3-4 seconds almost in all time, one time it hangs for 5 seconds and finished report at 10 seconds. osql results didn't differ from sqlcmd. Actually, we are moving from osql to sqlcmd/powershell, because osql is deprecated AFAIK.
    – Aen Sidhe
    Commented Dec 31, 2012 at 10:11
  • Ok, that leads to a lot more questions, what is the kind of load on the SQL Server? run "netstat -ano" in cmd, how many rows do you see? when the report takes a long time (10 seconds) is it being blocked (log sys.processes continuously)? There are also lot of other things that can cause that generic error (for example, social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/…)
    – WrinkleFree
    Commented Dec 31, 2012 at 10:21

1 Answer 1

Reset to default
1

[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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.