General network error

Does anyone have an idea why some of the scheduled jobs
end with the following error message:
Executed as user: DATABASE\Administrator.
ConnectionCheckForData (CheckforData()). [SQLSTATE 01000]
(Message 10054) General network error. Check your
network documentation. [SQLSTATE 08S01] (Error 11). The
step failed.
I have also experienced this when running long queries
(usually > 30min) in Query Analyzer.
I set the timeout to 300 (default was 60 or 30) and
reduced the packet size to 1428 (default was 4096) and
these helped but only with some queries/jobs.
Any help would be greatly appreciated!
.mikaby reducing the packet size you reduced the amount of pipe that SQL Server u
ses to communicate over the network. Timeout is in seconds so 300 seconds t
ranslates to 5 minutes. If the reduction of your useable bandwith across th
e network causes your trans
fer of data to go over 5 minutes then you have a problem.|||Have had this problem recently, it seems a common error with quite a
few different symptoms. I will tell you our story, which may or may
not help.
For our case, we had this error executing dynamic SQL over ADO.NET,
but could also recreate the problem via Query Analyser using different
protocols.
We were using a complex query and got either your error or the
following:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
We only got this error under certain conditions but could reproduce it
faithfully under those conditions.
Sniffing around the newsgroups, the general opinion was that if you
changed the query slightly, then in some cases the problem just "went
away".
I found 2 solutions to our problem.
Solution 1
Our query was using a view and by putting a "TOP 100 PERCENT" clause
in the view the problem went away (for one instance only)
Solution 2
By using "OPTION(MERGE JOIN)" or "OPTION(LOOP JOIN)" in the SQL
statement, the problem went away. By forcing "OPTION(HASH JOIN)" the
problem re-appeared.
My theory is that merge joins were causing the problem (possibly in
conjunction with parallel query execution) but can't prove anything
because when you get this problem, you can't even get a query plan.
Looking at the query plan after using "TOP 100 PERCENT" I noticed that
there were no merge joins, although that proves nothing.
We have continued to get problems in other areas of our application
and find that "OPTION(LOOP JOIN)" with dynamic sql fixes the symptoms
but if anybody can find out the root causes and a fix I would
appreciate it if they let me know.
"Mika" <mika.ylinen@.corporateimage.se> wrote in message news:<126e501c3f65f$860582e0$a00
1280a@.phx.gbl>...
> Does anyone have an idea why some of the scheduled jobs
> end with the following error message:
> Executed as user: DATABASE\Administrator.
> ConnectionCheckForData (CheckforData()). [SQLSTATE 01000]
> (Message 10054) General network error. Check your
> network documentation. [SQLSTATE 08S01] (Error 11). The
> step failed.
> I have also experienced this when running long queries
> (usually > 30min) in Query Analyzer.
> I set the timeout to 300 (default was 60 or 30) and
> reduced the packet size to 1428 (default was 4096) and
> these helped but only with some queries/jobs.
> Any help would be greatly appreciated!
> .mika