remote SQL query reset ("The semaphore timeout period has expired. [SQLSTATE 08S01] (Error 121) Communication link failure [SQLSTATE 08S01] (Error 121).")

That kb answer suggests the problem is network based rather than sql server based, but the symptoms could also be exaggerated by heavy io load.

Make sure you're up to date with service packs, firmware and drivers etc, and if the problem persists try to see if disabling Priority Boost helps.

If not try:

1. Set TcpMaxDataRetransmissions to 30 (decimal);
http://technet2.microsoft.com/WindowsServer/en/library/7dac9001-3e55-4e9c-b0fa-52841ece2fdd1033.mspx

2. Set KeepAliveInterval to 25000 (decimal).
http://technet2.microsoft.com/WindowsServer/en/library/734570a2-06d6-450e-b765-ccfa7530af491033.mspx 

Yeah, haven't seen the affinity mask options before, but 1 & 2 above resolved the issue here.

lorddef,

But in the internet , there are 2 x error message similiar but not the same, mine is about the SQL server, not server/netwrok.

DBA100.

This can be the other version:"

[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]TCP Provider: An existing connection was forcibly closed by the remote host.

[sqsrvres] printODBCError: sqlstate = 08S01; native error = 40; message = [Microsoft][SQL Native Client]Communication link failure

[sqsrvres] printODBCError: sqlstate = 08S01; native error = 40; message = [Microsoft][SQL Native Client]TCP Provider: The specified network name is no longer available.

Well you didn't post the full error message initially, however this still doesn't rule out a network issue as the cause of the problem.  I guess need to look at any other variables in your setup to figure out if it's a problem with sql server instance, or if the root cause is network related ; any connection pools? max connection limit? soes it happen under heavy load (network/io/cpu) or even when there is no load. Are there any firewalls between the client and server that could be closing the connection etc...

lorddef,

I did, let me requote, the message shown by our college even worse.

"The semaphore timeout period has expired. [SQLSTATE 08S01] (Error 121)  Communication link failure [SQLSTATE 08S01] (Error 121)."

This is the one, when you search from internet, it has differnet version.

I tested it copy coping many GB of files and it doesn't create problems at all, that;s why. Some articles even say change registry of the network!!

Do you think it is rare to change the default network confiugration, what if later on we have network problkme again and what to change then??

DBA100

You clearly posted a set of 3 differing error messages later after the initial post of 2.

I think you need to describe the symptoms and circumstances a little more clearly.  You seem to be adamant that the problem is not network based when the error messages seem to suggest this is a possibility, why so?   The problem could be due to a timeout or sql server issue, but copying large files isn't really a good test of network stability.

How often does the error occour, and under what circumstances?
Does it still occour if the client is run locally on the server rather than across the network?
Is the server io or cpu bound at the time of the issues?
Is there any deadlock, long running queries or excessive locks on the server at the time?
Is the client in question executing a long running query?
Does a running constant ping against the target server from the client that's having problems list any dropped packets, if so what percentage?
If there is an outage does it only happen when your client is running?
What does the server profiler show?
What does the windows event log show?

Also the link you posted above indicates an error within sql server that results in a thread getting stuck on a cpu that is saturated is the cause of that particular issue. Looking at cpu states, the profiler and sql server details in perfmon should allow you to see if this could be the case.

You could try the options suggested by the poster in that thread, or try disabling priority boost as that has had issues previously.

you know, I just copy and paste the whole error message and search it, bascially we will find only 2 groups of related answer.

one is SQL related and one is network and server related.

We did found that this is not occur regularily. we try to transfer file transfer inbound and outbound, if this is not a good test then what is a good test for network stabliity?

We did test on Windows log but nothing shows out. This is the only message.

DBA100.

I see there may be a language barrier here, but you still haven't clearly outlined the problem.

You won't get any closer to the soloution unless you investigate into the areas I posted above, and it's going to be hard for anyone else to see what the problem might be unless you can do so and relay the information back to us.

Sorry but without this information there's nothing more I can suggest.

lorddef,

By making it simple, I get this message, how to solve it?

"The semaphore timeout period has expired. [SQLSTATE 08S01] (Error 121)  Communication link failure [SQLSTATE 08S01] (Error 121)."

DBA100.

this support note should help for that error:
http://support.microsoft.com/kb/325487

angelIII,

This one is already known, this doen'st work,

We already know that this is not netwrok problem, my college send this to me also.

Some case even say this only appear on cluster nodes but not all nodes, how can we trust this?

I strongly belive that the server, after it is configured, it has no problme on network, please note that this error not happening all the time, right now we dont' have this any more.

The only thing we did is unclick the "boost SQL server proprity" and this can help on this can right now the system might not only listen to SQL server mostly, but also other request like Network request.

DBA100.