Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
could you post what does sqlcmd do exactly? does it generate text output or staging tables? what is the "report"? –  Rohan Dec 31 '12 at 9:01
    
@Rohan, I updated post just now. –  Aen Sidhe Dec 31 '12 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) –  Rohan Dec 31 '12 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 Dec 31 '12 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/…) –  Rohan Dec 31 '12 at 10:21
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.